You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Simon Mottram <Si...@cucumber.co.nz> on 2022/03/16 00:23:55 UTC
Bugs with multiple LAST_VALUE aggregation on multiple columns
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|
Re: Bugs with multiple LAST_VALUE aggregation on multiple columns
Posted by Istvan Toth <st...@cloudera.com>.
My bad, it's in the phoenixDB readme:
https://github.com/apache/phoenix-queryserver/blob/master/python-phoenixdb/README.rst
On Thu, Mar 17, 2022 at 9:38 PM Simon Mottram <Si...@cucumber.co.nz>
wrote:
> Hi
>
> There's no README.md with testing instructions that I can find.
>
> This is the entire README.md for queryserver 6.0.1 . I've checked all .md
> files in hbase/phoenix/phoenix-queryserver
>
> Cheers
>
> S
>
>
> <!--
> Licensed to the Apache Software Foundation (ASF) under one or more
> contributor license agreements. See the NOTICE file distributed with
> this work for additional information regarding copyright ownership.
> The ASF licenses this file to You under the Apache License, Version 2.0
> (the "License"); you may not use this file except in compliance with
> the License. You may obtain a copy of the License at
>
> http://www.apache.org/licenses/LICENSE-2.0
>
> Unless required by applicable law or agreed to in writing, software
> distributed under the License is distributed on an "AS IS" BASIS,
> WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
> See the License for the specific language governing permissions and
> limitations under the License.
> -->
>
> 
>
> <b>[Apache Phoenix](http://phoenix.apache.org/)</b> enables OLTP and
> operational analytics in Hadoop for low latency applications. Visit the
> Apache Phoenix website <b>[here](http://phoenix.apache.org/)</b>. This is
> the repo for the Phoenix Query Server (PQS).
>
> Copyright ©2020 [Apache Software Foundation](http://www.apache.org/). All
> Rights Reserved.
>
> ## Introduction
>
> The Phoenix Query Server is an JDBC over HTTP abstraction. The Phoenix
> Query Server proxies the standard
> Phoenix JDBC driver and provides a backwards-compatible wire protocol to
> invoke that JDBC driver. This is
> all done via the Apache Avatica project (sub-project of Apache Calcite).
>
> The reference client implementation for PQS is a "thin" JDBC driver which
> can communicate with PQS. There
> are drivers in other languages which exist in varying levels of maturity
> including Python, Golang, and .NET.
>
> The Python driver is maintained by the Phoenix project, and is available
> in the python-phoenixdb
> directory of the phoenix-queryserver repository.
> ------------------------------
> *From:* Istvan Toth <st...@cloudera.com>
> *Sent:* 17 March 2022 5:40 PM
> *To:* user@phoenix.apache.org <us...@phoenix.apache.org>
> *Subject:* Re: Bugs with multiple LAST_VALUE aggregation on multiple
> columns
>
>
>
> 1. The sqline thin.py is presumably a replacement for the sqlline.py
> in the phoenix 520 project. So just run that?
>
> Yes, exactly (but don't use the one from phoenix 5.0)
>
> phoenix-server-hbase-2.4.jar is NOT the PQS jar, it is the JAR with the
> Phoenix Hbase server side components that must be copied to the HBase lib
> dir.
>
> The phoenix_sandbox.py script has nothing to do with PQS, it just starts a
> local Hadoop+HBase+Phoenix minicluster for testing.
>
> PQS has been removed from the main Phoenix repo, and now lives in
> https://github.com/apache/phoenix-queryserver
> Testing it is discussed in detail in the README.md, please check that
> first.
> I'll be happy to answer any questions you still may have. (PQS docs are
> not great at the moment)
>
> For PQS production use, just untar the assembly, and copy the
> phoenix-client-embedded (if you use HEAD) or phoenix-client (for 6.0.0)
> into the root directory.
>
>
> On Thu, Mar 17, 2022 at 12:54 AM Simon Mottram <
> Simon.Mottram@cucumber.co.nz> wrote:
>
> 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
> 1. what files
> 2. where from
> 3. 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 <us...@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.Mottram@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
> stoty@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/>
> ------------------------------
>
>
>
> --
> *István Tóth* | Staff Software Engineer
> stoty@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/>
> ------------------------------
>
--
*István Tóth* | Staff Software Engineer
stoty@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/>
------------------------------
Re: Bugs with multiple LAST_VALUE aggregation on multiple columns
Posted by Simon Mottram <Si...@cucumber.co.nz>.
Hi
There's no README.md with testing instructions that I can find.
This is the entire README.md for queryserver 6.0.1 . I've checked all .md files in hbase/phoenix/phoenix-queryserver
Cheers
S
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<b>[Apache Phoenix](http://phoenix.apache.org/)</b> enables OLTP and operational analytics in Hadoop for low latency applications. Visit the Apache Phoenix website <b>[here](http://phoenix.apache.org/)</b>. This is the repo for the Phoenix Query Server (PQS).
Copyright ©2020 [Apache Software Foundation](http://www.apache.org/). All Rights Reserved.
## Introduction
The Phoenix Query Server is an JDBC over HTTP abstraction. The Phoenix Query Server proxies the standard
Phoenix JDBC driver and provides a backwards-compatible wire protocol to invoke that JDBC driver. This is
all done via the Apache Avatica project (sub-project of Apache Calcite).
The reference client implementation for PQS is a "thin" JDBC driver which can communicate with PQS. There
are drivers in other languages which exist in varying levels of maturity including Python, Golang, and .NET.
The Python driver is maintained by the Phoenix project, and is available in the python-phoenixdb
directory of the phoenix-queryserver repository.
________________________________
From: Istvan Toth <st...@cloudera.com>
Sent: 17 March 2022 5:40 PM
To: user@phoenix.apache.org <us...@phoenix.apache.org>
Subject: Re: Bugs with multiple LAST_VALUE aggregation on multiple columns
1. The sqline thin.py is presumably a replacement for the sqlline.py in the phoenix 520 project. So just run that?
Yes, exactly (but don't use the one from phoenix 5.0)
phoenix-server-hbase-2.4.jar is NOT the PQS jar, it is the JAR with the Phoenix Hbase server side components that must be copied to the HBase lib dir.
The phoenix_sandbox.py script has nothing to do with PQS, it just starts a local Hadoop+HBase+Phoenix minicluster for testing.
PQS has been removed from the main Phoenix repo, and now lives in https://github.com/apache/phoenix-queryserver
Testing it is discussed in detail in the README.md, please check that first.
I'll be happy to answer any questions you still may have. (PQS docs are not great at the moment)
For PQS production use, just untar the assembly, and copy the phoenix-client-embedded (if you use HEAD) or phoenix-client (for 6.0.0) into the root directory.
On Thu, Mar 17, 2022 at 12:54 AM Simon Mottram <Si...@cucumber.co.nz>> wrote:
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<ma...@phoenix.apache.org> <us...@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 <Si...@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
stoty@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/>
________________________________
--
István Tóth | Staff Software Engineer
stoty@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/>
________________________________
Re: Bugs with multiple LAST_VALUE aggregation on multiple columns
Posted by Istvan Toth <st...@cloudera.com>.
>
>
> 1. The sqline thin.py is presumably a replacement for the sqlline.py
> in the phoenix 520 project. So just run that?
>
> Yes, exactly (but don't use the one from phoenix 5.0)
phoenix-server-hbase-2.4.jar is NOT the PQS jar, it is the JAR with the
Phoenix Hbase server side components that must be copied to the HBase lib
dir.
The phoenix_sandbox.py script has nothing to do with PQS, it just starts a
local Hadoop+HBase+Phoenix minicluster for testing.
PQS has been removed from the main Phoenix repo, and now lives in
https://github.com/apache/phoenix-queryserver
Testing it is discussed in detail in the README.md, please check that first.
I'll be happy to answer any questions you still may have. (PQS docs are not
great at the moment)
For PQS production use, just untar the assembly, and copy the
phoenix-client-embedded (if you use HEAD) or phoenix-client (for 6.0.0)
into the root directory.
On Thu, Mar 17, 2022 at 12:54 AM Simon Mottram <Si...@cucumber.co.nz>
wrote:
> 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
> 1. what files
> 2. where from
> 3. 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 <us...@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.Mottram@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
> stoty@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/>
> ------------------------------
>
--
*István Tóth* | Staff Software Engineer
stoty@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/>
------------------------------
Re: Bugs with multiple LAST_VALUE aggregation on multiple columns
Posted by Simon Mottram <Si...@cucumber.co.nz>.
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 <us...@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 <Si...@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
stoty@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/>
________________________________
Re: Bugs with multiple LAST_VALUE aggregation on multiple columns
Posted by Istvan Toth <st...@cloudera.com>.
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 <Si...@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
stoty@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/>
------------------------------