[
https://issues.apache.org/jira/browse/PHOENIX-4898?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Abhishek Gupta updated PHOENIX-4898:
------------------------------------
Labels: Aggregation UDF udfs (was: UDF udfs)
> LAST_VALUE aggregation fails when column contain NULLs
> ------------------------------------------------------
>
> Key: PHOENIX-4898
> URL: https://issues.apache.org/jira/browse/PHOENIX-4898
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.7.0
> Environment: HBase version: 1.1.2
> Phoenix version: 4.7.0
>
> Reporter: Abhishek Gupta
> Priority: Major
> Labels: Aggregation, UDF, udfs
>
> When I use last_value aggregation on a column that is sparse with NULLs it
> fails with the exception
> {code:java}
> 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
> {code}
> Please find below the DDL and SQL queries to reproduce the issue
>
> {code:java}
> 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);
> {code}
>
> {noformat}
> 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
> {noformat}
>
>
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
