Hi
Thanks for the quick response, we can ignore Dbeaver from now on
I was using the PQS version that was included in the latest Phoenix HEAD
(5.2.0.SNAPSHOT)
I've built the latest queryserver
<version>6.0.1-SNAPSHOT</version>
and have updated the POM to user the phoenix 5.2.0-SNAPSHOT and it built
correctly. So I now have
I'm stuck knowing how to proceed getting the sandbox.py to use the new query
server.
"Also please use the sqlline-thin client from queryserver-6.0.0 or HEAD, not
5.0.0 for repro."
I"m afraid baby steps required.
1. The sqline thin.py is presumably a replacement for the sqlline.py in the
phoenix 520 project. So just run that?
2. it's not obvious to me where to put the new query server jars, the
server jars have changed name?
In the phoenix project there is phoenix-server-hbase-2.4-5.2.0-SNAPSHOT , but
no file of a similar name in the queryserver project. I can find
./phoenix-queryserver/target/phoenix-queryserver-6.0.1-SNAPSHOT.jar
* what files
* where from
* where to.
This is the folder phoenix/phoenix-server/target.
simbosan@DESKTOP-GLPB751:/mnt/c/work/phoenix/phoenix/phoenix-server/target$ ls
classes maven-shared-archive-resources
phoenix-server-hbase-2.4-5.2.0-SNAPSHOT-sources.jar
phoenix-server-hbase-2.4.jar test-classes
maven-archiver original-phoenix-server-hbase-2.4-5.2.0-SNAPSHOT.jar
phoenix-server-hbase-2.4-5.2.0-SNAPSHOT.jar pom.xml
Does sandbox load from this folder, presumably I need to recreate the symlink?
Bit lost!
Cheers
S
________________________________
From: Istvan Toth <st...@cloudera.com>
Sent: 16 March 2022 11:08 PM
To: user@phoenix.apache.org <user@phoenix.apache.org>
Subject: Re: Bugs with multiple LAST_VALUE aggregation on multiple columns
Thanks for the reports.
The preferred way for repros is sandbox + sqlline.py.
You say that you are also using dbeaver + sqlline-thin client jar.
What version of queryserver are you using ?
Please make sure that use for queryserver-6.0.0, or the latest HEAD,
with the phoenix-client JAR built from the same commit that you use for
the sandbox.
If you use the test mode described in the PQS README.md, then make sure that
you mvn install phoenix-5.2-SNAPSHOT first, and set that version in the PQS
pom.xml
before starting the test PQS instance
Also please use the sqlline-thin client from queryserver-6.0.0 or HEAD, not
5.0.0 for repro.
If you can repro the problem with sandbox+sqlline, then please open the JIRA.
If the behaviour is different with the latest PQS + thin client, then please
also note that.
This applied to all three problems.
On Wed, Mar 16, 2022 at 1:24 AM Simon Mottram
<simon.mott...@cucumber.co.nz<mailto:simon.mott...@cucumber.co.nz>> wrote:
Before I submit a JIRA, can someone point out if I made a mistake. Pretty sure
the exceptions are a bug, but...
-- Tested with both DBeaver and local new build of Phoenix
-- Phoenix sandbox. HBase 2.4.9 Phoenix <version>5.2.0-SNAPSHOT</version>
pulled from master 13/3/2022
-- DBeaver driver version - phoenix-5.0.0-HBase-2.0-thin-client.jar
-- Dbeaver sometimes gives different values, documented below
--- ==================
-- SETUP
--- ==================
DROP TABLE test.TEST_LAST_VALUE ;
create table if not exists test.test_last_value (pk1 varchar not null, bi
bigint,i integer,d decimal, constraint test_last_value_pk primary key (pk1));
upsert into test.test_last_value(pk1,bi,i) values('nulldecimal1',10,20);
upsert into test.test_last_value(pk1,bi,i) values('nulldecimal2',11,21);
upsert into test.test_last_value(pk1,i,d) values('nullbi1',10,20.1);
upsert into test.test_last_value(pk1,i,d) values('nullbi2',11,21.1);
upsert into test.test_last_value(pk1,bi,d) values('nulli1',10,20.1);
upsert into test.test_last_value(pk1,bi,d) values('nulli2',11,21.1);
upsert into test.test_last_value(pk1) values('allnuls');
upsert into test.test_last_value(pk1, bi, i, d) values('nonuls1', 10, 20, 20.1);
upsert into test.test_last_value(pk1, bi, i, d) values('nonuls2', 11, 21, 20.2);
-- yeah there's something there
SELECT * FROM TEST.TEST_LAST_VALUE ;
PK1 |BI|I |D |
------------+--+--+----+
allnuls | | | |
nonuls1 |10|20|20.1|
nonuls2 |11|21|20.2|
nullbi1 | |10|20.1|
nullbi2 | |11|21.1|
nulldecimal1|10|20| |
nulldecimal2|11|21| |
nulli1 |10| |20.1|
nulli2 |11| |21.1|
-- ==============================
-- TESTING LAST_VALUE
-- ==============================
-- Last BIGINT = CORRECT
SELECT pk1,
last_value(bi) WITHIN GROUP (ORDER BY pk1 ASC)
FROM TEST.TEST_LAST_VALUE
GROUP BY PK1;
PK1 |LAST_VALUE(PK1, true, BI)|
------------+-------------------------+
allnuls | |
nonuls1 | 10|
nonuls2 | 11|
nullbi1 | |
nullbi2 | |
nulldecimal1| 10|
nulldecimal2| 11|
nulli1 | 10|
nulli2 | 11|
-- LAST INTEGER = CORRECT
SELECT pk1,
last_value(i) WITHIN GROUP (ORDER BY pk1 ASC)
FROM TEST.TEST_LAST_VALUE
GROUP BY PK1;
PK1 |LAST_VALUE(PK1, true, I)|
------------+------------------------+
allnuls | |
nonuls1 | 20|
nonuls2 | 21|
nullbi1 | 10|
nullbi2 | 11|
nulldecimal1| 20|
nulldecimal2| 21|
nulli1 | |
nulli2 | |
-- LAST DECIMAL = CORRECT
SELECT pk1,
last_value(d) WITHIN GROUP (ORDER BY pk1 ASC)
FROM TEST.TEST_LAST_VALUE
GROUP BY PK1;
PK1 |LAST_VALUE(PK1, true, I)|
------------+------------------------+
allnuls | |
nonuls1 | 20|
nonuls2 | 21|
nullbi1 | 10|
nullbi2 | 11|
nulldecimal1| 20|
nulldecimal2| 21|
nulli1 | |
nulli2 | |
-- ================
-- COMBINATIONS
-- ================
-- LAST BIGINT + LAST INTEGER
SELECT PK1,
last_value(bi) WITHIN GROUP (ORDER BY pk1 ASC),
last_value(i) WITHIN GROUP (ORDER BY pk1 ASC)
FROM TEST.TEST_LAST_VALUE
GROUP BY PK1;
-- Same exception with sandbox and dbeaver
Error: ERROR 201 (22000): Illegal data. Expected length of at least 8 bytes,
but had 4 (state=22000,code=201)
java.sql.SQLException: ERROR 201 (22000): Illegal data. Expected length of at
least 8 bytes, but had 4
at
org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:620)
at
org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:217)
at
org.apache.phoenix.schema.types.PDataType.checkForSufficientLength(PDataType.java:284)
at
org.apache.phoenix.schema.types.PLong$LongCodec.decodeLong(PLong.java:256)
at org.apache.phoenix.schema.types.PLong.toObject(PLong.java:115)
at org.apache.phoenix.schema.types.PLong.toObject(PLong.java:31)
at
org.apache.phoenix.schema.types.PDataType.toObject(PDataType.java:1005)
at
org.apache.phoenix.compile.ExpressionProjector.getValue(ExpressionProjector.java:75)
at
org.apache.phoenix.jdbc.PhoenixResultSet.getObject(PhoenixResultSet.java:597)
at sqlline.Rows$Row.<init>(Rows.java:260)
at sqlline.BufferedRows.nextList(BufferedRows.java:110)
at sqlline.BufferedRows.<init>(BufferedRows.java:52)
at sqlline.SqlLine.print(SqlLine.java:1672)
at sqlline.Commands.executeSingleQuery(Commands.java:1063)
at sqlline.Commands.execute(Commands.java:1003)
at sqlline.Commands.sql(Commands.java:967)
at sqlline.SqlLine.dispatch(SqlLine.java:734)
at sqlline.SqlLine.begin(SqlLine.java:541)
at sqlline.SqlLine.start(SqlLine.java:267)
at sqlline.SqlLine.main(SqlLine.java:206)
-- LAST BIGINT + LAST DECIMAL - Exception with sandbox, table below with DBeaver
SELECT pk1,
last_value(bi) WITHIN GROUP (ORDER BY pk1 ASC),
last_value(d) WITHIN GROUP (ORDER BY pk1 ASC)
FROM TEST.TEST_LAST_VALUE
GROUP BY PK1;
-- Sandbox result
Error: ERROR 201 (22000): Illegal data. Expected length of at least 8 bytes,
but had 3 (state=22000,code=201)
java.sql.SQLException: ERROR 201 (22000): Illegal data. Expected length of at
least 8 bytes, but had 3
at
org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:620)
at
org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:217)
at
org.apache.phoenix.schema.types.PDataType.checkForSufficientLength(PDataType.java:284)
at
org.apache.phoenix.schema.types.PLong$LongCodec.decodeLong(PLong.java:256)
at org.apache.phoenix.schema.types.PLong.toObject(PLong.java:115)
at org.apache.phoenix.schema.types.PLong.toObject(PLong.java:31)
at
org.apache.phoenix.schema.types.PDataType.toObject(PDataType.java:1005)
at
org.apache.phoenix.compile.ExpressionProjector.getValue(ExpressionProjector.java:75)
at
org.apache.phoenix.jdbc.PhoenixResultSet.getObject(PhoenixResultSet.java:597)
at sqlline.Rows$Row.<init>(Rows.java:260)
at sqlline.BufferedRows.nextList(BufferedRows.java:110)
at sqlline.BufferedRows.<init>(BufferedRows.java:52)
at sqlline.SqlLine.print(SqlLine.java:1672)
at sqlline.Commands.executeSingleQuery(Commands.java:1063)
at sqlline.Commands.execute(Commands.java:1003)
at sqlline.Commands.sql(Commands.java:967)
at sqlline.SqlLine.dispatch(SqlLine.java:734)
at sqlline.SqlLine.begin(SqlLine.java:541)
at sqlline.SqlLine.start(SqlLine.java:267)
at sqlline.SqlLine.main(SqlLine.java:206)
-- DBeaver result. Note the odd values
PK1 |LAST_VALUE(PK1, true, BI)|LAST_VALUE(PK1, true, D)
|
------------+-------------------------+--------------------------------------------------------------------------------------------------------------------------------+
allnuls | |
|
nullbi1 | |
20.1|
nullbi2 | |
21.1|
nulldecimal1|
|-1010101010091000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
nulldecimal2|
|-1010101010090000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
nulli1 | 10|
20.1|
nulli2 | 11|
21.1|
--- LAST INTEGER + LAST DECIMAL - Obscure decimal values. NOTE: different
values for null decimal with dbeaver
SELECT pk1,
last_value(i) WITHIN GROUP (ORDER BY pk1 ASC),
last_value(d) WITHIN GROUP (ORDER BY pk1 ASC)
FROM TEST.TEST_LAST_VALUE
GROUP BY PK1;
PK1 |LAST_VALUE(PK1, true, I)|LAST_VALUE(PK1, true, D)
|
------------+------------------------+--------------------------------------------------------------------------------------------------------------------------------+
allnuls | |
|
nullbi1 | 10|
20.1|
nullbi2 | 11|
21.1|
nulldecimal1|
|-1008100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
nulldecimal2|
|-1008000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
nulli1 | |
20.1|
nulli2 | |
21.1|
--
István Tóth | Staff Software Engineer
st...@cloudera.com<https://www.cloudera.com>
[Cloudera]<https://www.cloudera.com/>
[Cloudera on Twitter]<https://twitter.com/cloudera> [Cloudera on Facebook]
<https://www.facebook.com/cloudera> [Cloudera on LinkedIn]
<https://www.linkedin.com/company/cloudera>
[https://www.cloudera.com/content/dam/www/marketing/brand/email-signature/ent-cloud-co-emailsig-gray.png]<https://www.cloudera.com/>
________________________________
