Sorry for bug spam, just want to get feedback (if any) before I JIRA
If you just create the first 10 rows in the scripts below, most functions work
fine. If you create records that are in just one group, that contain any
nulls, then exceptions are thrown:
So try with first 10, confirm stuff works
Then add last 5 rows, confirm stuff breaks
NOTE: PERCENTILE_CONT shows none of these errors, so whatever that function
does is maybe of interest!
Cheers
Simon
-- If a GROUP BY clause results in a single row with a NULL value then
Exceptions are thrown
-- If the column is INTEGER then the exception is: NumberFormatException:
Infinite or NaN
-- If the column is DECIMAL then the exception is: ArithmeticException:
Division undefined
-- 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
DROP TABLE TEST.TESTEXCEPTIONS ;
CREATE TABLE IF NOT EXISTS TEST.TESTEXCEPTIONS ( KEYCOL VARCHAR NOT NULL
PRIMARY KEY, GROUPNAME VARCHAR, INTEGERCOLUMN INTEGER , INTEGERCOLUMN2 INTEGER,
DECIMALCOLUMN DECIMAL(20,5), DECIMALCOLUMN2 DECIMAL(20,5));
-- 5 in group A
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('A', 'A', 1, 1, 1.1, 1.1);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('B', 'A', null, 2, 2.2,
2.2);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('C', 'A', 2, null, 2.2,
2.2);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('D', 'A', 3, 3, null,
3.3);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('E', 'A', 4, 4, 4.4,
null);
-- 5 in group B
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('F', 'B', 1, 1, 1.1, 1.1);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('G', 'B', null, 2, 2.2,
2.2);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('H', 'B', 2, null, 2.2,
2.2);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('I', 'B', 3, 3, null,
3.3);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('J', 'B', 4, 4, 4.4,
null);
-- 5 Unique groups - These cause the issue. Try running all the queries below
BEFORE creating these records to see that some bugs only occur if a grouping
results in a single row with a null column value
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('K', 'C', 1, 1, 1.1, 1.1);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('L', 'D', null, 2, 2.2,
2.2);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('M', 'E', 2, null, 2.2,
2.2);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('N', 'F', 3, 3, null,
3.3);
UPSERT INTO TEST.TESTEXCEPTIONS(KEYCOL, GROUPNAME, INTEGERCOLUMN,
INTEGERCOLUMN2, DECIMALCOLUMN, DECIMALCOLUMN2) VALUES('O', 'G', 4, 4, 4.4,
null);
-- to remove these problem rows
DELETE FROM TEST.TESTEXCEPTIONS WHERE KEYCOL = 'K' OR KEYCOL = 'L' OR KEYCOL =
'M' OR KEYCOL = 'N' OR KEYCOL = 'O';
-- rows exist
SELECT count(*) FROM TEST.TESTEXCEPTIONS ;
--
======================================================================================================================================
-- PERCENTILE_DISC
-- ======================
-- Integer single columns works
SELECT PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC) FROM
TEST.TESTEXCEPTIONS;
-- Integer two columns works
SELECT PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC),
PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN2 ASC) FROM
TEST.TESTEXCEPTIONS;
-- BUG: Single Decimal columns throws exception (already reported separately)
SELECT PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY DECIMALCOLUMN ASC) FROM
TEST.TESTEXCEPTIONS;
java.lang.ArrayIndexOutOfBoundsException: arraycopy: last source index 21 out
of bounds for byte[3]
at java.base/java.lang.System.arraycopy(Native Method)
at
org.apache.phoenix.expression.aggregator.PercentileDiscClientAggregator.evaluate(PercentileDiscClientAggregator.java:85)
at
org.apache.phoenix.schema.KeyValueSchema.toBytes(KeyValueSchema.java:113)
at
org.apache.phoenix.schema.KeyValueSchema.toBytes(KeyValueSchema.java:94)
at
org.apache.phoenix.expression.aggregator.Aggregators.toBytes(Aggregators.java:112)
at
org.apache.phoenix.iterate.UngroupedAggregatingResultIterator.next(UngroupedAggregatingResultIterator.java:65)
at
org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:853)
at sqlline.BufferedRows.nextList(BufferedRows.java:109)
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)
-- ==================
-- With Grouping
-- shows various exceptions when last 5 rows are added to mix
-- ==================
SELECT GROUPNAME, PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN
ASC) FROM TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- Integer two columns works
SELECT GROUPNAME, PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN
ASC), PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN2 ASC) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- Decimal - Same exception as above
SELECT GROUPNAME, PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY DECIMALCOLUMN
ASC) FROM TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
--
======================================================================================================================================
-- PERCENT_RANK
-- Grouping Issues. Create the 5 unique grouped records to show error
-- single integer
SELECT PERCENT_RANK (100) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC) FROM
TEST.TESTEXCEPTIONS;
-- two integers
SELECT PERCENT_RANK (100) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC),
PERCENT_RANK (100) WITHIN GROUP (ORDER BY INTEGERCOLUMN2 ASC) FROM
TEST.TESTEXCEPTIONS;
-- single decimal
SELECT PERCENT_RANK (100) WITHIN GROUP (ORDER BY DECIMALCOLUMN ASC) FROM
TEST.TESTEXCEPTIONS;
-- two decmals
SELECT PERCENT_RANK (100) WITHIN GROUP (ORDER BY DECIMALCOLUMN ASC),
PERCENT_RANK (100) WITHIN GROUP (ORDER BY DECIMALCOLUMN2 ASC) FROM
TEST.TESTEXCEPTIONS;
-- combination
SELECT PERCENT_RANK (100) WITHIN GROUP (ORDER BY DECIMALCOLUMN ASC),
PERCENT_RANK (100) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC) FROM
TEST.TESTEXCEPTIONS;
-- ==================
-- With Grouping
-- Exceptions thrown if an grouping contains a single row with a null value
-- Use the unique group rows at the top of this file to cause this bug.
-- ==================
SELECT GROUPNAME, PERCENT_RANK (100) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC)
FROM TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- two integers
SELECT GROUPNAME, PERCENT_RANK (100) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC),
PERCENT_RANK (100) WITHIN GROUP (ORDER BY INTEGERCOLUMN2 ASC) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- single decimal
SELECT GROUPNAME, PERCENT_RANK (100) WITHIN GROUP (ORDER BY DECIMALCOLUMN ASC)
FROM TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- two decmals
SELECT GROUPNAME, PERCENT_RANK (100) WITHIN GROUP (ORDER BY DECIMALCOLUMN ASC),
PERCENT_RANK (100) WITHIN GROUP (ORDER BY DECIMALCOLUMN2 ASC) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- combination
SELECT GROUPNAME, PERCENT_RANK (100) WITHIN GROUP (ORDER BY DECIMALCOLUMN ASC),
PERCENT_RANK (100) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
--
======================================================================================================================================
-- STDDEV_SAMP
-- Grouping Issues. Create the 5 unique grouped records to show error
-- single integer
SELECT STDDEV_SAMP(INTEGERCOLUMN) FROM TEST.TESTEXCEPTIONS;
-- two integers
SELECT STDDEV_SAMP(INTEGERCOLUMN), STDDEV_SAMP(INTEGERCOLUMN2) FROM
TEST.TESTEXCEPTIONS;
-- single decimal
SELECT STDDEV_SAMP(DECIMALCOLUMN) FROM TEST.TESTEXCEPTIONS;
-- two decimals
SELECT STDDEV_SAMP(DECIMALCOLUMN), STDDEV_SAMP(DECIMALCOLUMN2) FROM
TEST.TESTEXCEPTIONS;
-- integer and decimal
SELECT STDDEV_SAMP(INTEGERCOLUMN), STDDEV_SAMP(DECIMALCOLUMN2) FROM
TEST.TESTEXCEPTIONS;
-- ==================
-- With Grouping
-- Exceptions thrown if an grouping contains a single row with a null value
-- Use the unique group rows at the top of this file to cause this bug.
-- ==================
-- single integer
SELECT GROUPNAME, STDDEV_SAMP(INTEGERCOLUMN) FROM TEST.TESTEXCEPTIONS GROUP BY
GROUPNAME ;
-- two integers
SELECT GROUPNAME, STDDEV_SAMP(INTEGERCOLUMN), STDDEV_SAMP(INTEGERCOLUMN2) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- single decimal
SELECT GROUPNAME, STDDEV_SAMP(DECIMALCOLUMN) FROM TEST.TESTEXCEPTIONS GROUP BY
GROUPNAME ;
-- two decimals
SELECT GROUPNAME, STDDEV_SAMP(DECIMALCOLUMN), STDDEV_SAMP(DECIMALCOLUMN2) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- integer and decimal
SELECT GROUPNAME, STDDEV_SAMP(INTEGERCOLUMN), STDDEV_SAMP(DECIMALCOLUMN2) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
--
======================================================================================================================================
-- STDDEV_POP
-- Grouping Issues. Create the 5 unique grouped records to show error
-- single integer
SELECT STDDEV_POP(INTEGERCOLUMN) FROM TEST.TESTEXCEPTIONS;
-- two integers
SELECT STDDEV_POP(INTEGERCOLUMN), STDDEV_POP(INTEGERCOLUMN2) FROM
TEST.TESTEXCEPTIONS;
-- Single decimal column works
SELECT STDDEV_POP(DECIMALCOLUMN) FROM TEST.TESTEXCEPTIONS;
-- two decimal columns works
SELECT STDDEV_POP(DECIMALCOLUMN), STDDEV_POP(DECIMALCOLUMN2) FROM
TEST.TESTEXCEPTIONS;
-- integer and decimal work
SELECT STDDEV_POP(INTEGERCOLUMN), STDDEV_POP(DECIMALCOLUMN2) FROM
TEST.TESTEXCEPTIONS;
-- ==================
-- With Grouping
-- Exceptions thrown if an grouping contains a single row with a null value
-- Use the unique group rows at the top of this file to cause this bug.
-- ==================
-- single integer
SELECT GROUPNAME, STDDEV_POP(INTEGERCOLUMN) FROM TEST.TESTEXCEPTIONS GROUP BY
GROUPNAME ;
-- two integers
SELECT GROUPNAME, STDDEV_POP(INTEGERCOLUMN), STDDEV_POP(INTEGERCOLUMN2) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- Single decimal column
SELECT GROUPNAME, STDDEV_POP(DECIMALCOLUMN) FROM TEST.TESTEXCEPTIONS GROUP BY
GROUPNAME ;
-- two decimal columns
SELECT GROUPNAME, STDDEV_POP(DECIMALCOLUMN), STDDEV_POP(DECIMALCOLUMN2) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- integer and decimal
SELECT GROUPNAME, STDDEV_POP(INTEGERCOLUMN), STDDEV_POP(DECIMALCOLUMN2) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
--
======================================================================================================================================
-- PERCENTILE_CONT just including for completeness, no issues here
-- No issues
-- single integer
SELECT PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC) FROM
TEST.TESTEXCEPTIONS;
-- two integers
SELECT PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC),
PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN2 ASC) FROM
TEST.TESTEXCEPTIONS;
-- single decimal
SELECT PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY DECIMALCOLUMN ASC) FROM
TEST.TESTEXCEPTIONS;
-- two decmals
SELECT PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY DECIMALCOLUMN ASC),
PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY DECIMALCOLUMN2 ASC) FROM
TEST.TESTEXCEPTIONS;
-- combination
SELECT PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY DECIMALCOLUMN ASC),
PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC) FROM
TEST.TESTEXCEPTIONS;
-- ==================
-- With Grouping
-- No issues, whatever this function does, the others should copy!
-- ==================
-- single integer
SELECT GROUPNAME, PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN
ASC) FROM TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- two integers
SELECT GROUPNAME, PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN
ASC), PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN2 ASC) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME;
-- single decimal
SELECT GROUPNAME, PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY DECIMALCOLUMN
ASC) FROM TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- two decmals
SELECT GROUPNAME, PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY DECIMALCOLUMN
ASC), PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY DECIMALCOLUMN2 ASC) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
-- combination
SELECT GROUPNAME, PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY DECIMALCOLUMN
ASC), PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY INTEGERCOLUMN ASC) FROM
TEST.TESTEXCEPTIONS GROUP BY GROUPNAME ;
