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>
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>
[image: Cloudera] <https://www.cloudera.com/>
[image: Cloudera on Twitter] <https://twitter.com/cloudera> [image:
Cloudera on Facebook] <https://www.facebook.com/cloudera> [image: Cloudera
on LinkedIn] <https://www.linkedin.com/company/cloudera>
<https://www.cloudera.com/>
------------------------------
