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|
