Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
4.7.0
-
None
-
None
-
HBase version: 1.1.2
Phoenix version: 4.7.0
Description
When I use last_value aggregation on a column that is sparse with NULLs it fails with the exception
Error: Error -1 (00000) : Error while executing SQL "select TO_CHAR(TO_DATE(pk1,'yyyyMMddHHmm'),'yyyyMMddHH') as t, last_value(m3) within group (order by pk1 asc) as m3_last, sum(m2) as m2_sum from test_last_value group by t": Remote driver error: ArrayIndexOutOfBoundsException: (null exception message) (state=00000,code=-1) org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : Error while executing SQL "select TO_CHAR(TO_DATE(pk1,'yyyyMMddHHmm'),'yyyyMMddHH') as t, last_value(m3) within group (order by pk1 asc) as m3_last, sum(m2) as m2_sum from test_last_value group by t": Remote driver error: ArrayIndexOutOfBoundsException: (null exception message) at org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:54) at org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:41) at org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156) at org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:208) at sqlline.Commands.execute(Commands.java:822) at sqlline.Commands.sql(Commands.java:732) at sqlline.SqlLine.dispatch(SqlLine.java:808) at sqlline.SqlLine.begin(SqlLine.java:681) at sqlline.SqlLine.start(SqlLine.java:398) at sqlline.SqlLine.main(SqlLine.java:292) at org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93) java.lang.ArrayIndexOutOfBoundsException
Please find below the DDL and SQL queries to reproduce the issue
create table if not exists test_last_value (pk1 varchar not null, m1 bigint,m2 bigint,m3 bigint, constraint test_last_value_pk primary key (pk1)); upsert into test_last_value(pk1,m1,m2) values('201809010000',10,20); upsert into test_last_value(pk1,m1,m2) values('201809010030',10,20); upsert into test_last_value(pk1,m1,m2,m3) values('201809010100',10,20,11); upsert into test_last_value(pk1,m1,m2) values('201809010130',10,20); upsert into test_last_value(pk1,m1,m2) values('201809010200',10,20); upsert into test_last_value(pk1,m1,m2) values('201809010230',10,20); upsert into test_last_value(pk1,m1,m3) values('201809010300',10,22);
select * from test_last_value; +---------------+-----+-------+-------+ | PK1 | M1 | M2 | M3 | +---------------+-----+-------+-------+ | 201809010000 | 10 | 20 | null | | 201809010030 | 10 | 20 | null | | 201809010100 | 10 | 20 | 11 | | 201809010130 | 10 | 20 | null | | 201809010200 | 10 | 20 | null | | 201809010230 | 10 | 20 | null | | 201809010300 | 10 | null | 22 | +---------------+-----+-------+-------+ Last value aggregation succeeds when column has no nulls ======================================================== select TO_CHAR(TO_DATE(pk1,'yyyyMMddHHmm'),'yyyyMMddHH') as t, last_value(m1) within group (order by pk1 asc) as m1_last, sum(m2) as m2_sum from test_last_value group by t; +-------------+----------+---------+ | T | M1_LAST | M2_SUM | +-------------+----------+---------+ | 2018090100 | 10 | 40 | | 2018090101 | 10 | 40 | | 2018090102 | 10 | 40 | | 2018090103 | 10 | null | +-------------+----------+---------+ Last value aggregation fails with java.lang.ArrayIndexOutOfBoundsException when column has nulls ============================================================================== select TO_CHAR(TO_DATE(pk1,'yyyyMMddHHmm'),'yyyyMMddHH') as t, last_value(m3) within group (order by pk1 asc) as m3_last, sum(m2) as m2_sum from test_last_value group by t; java.lang.ArrayIndexOutOfBoundsException