Details
Major Description
Returning elements of an array from a table that is part of a join causes array elements to be nullified.
create table array_test_1 (id integer not null primary key, arr tinyint[5]); upsert into array_test_1 values (1001, array[0, 0, 0, 0, 0]); upsert into array_test_1 values (1002, array[0, 0, 0, 0, 1]); upsert into array_test_1 values (1003, array[0, 0, 0, 1, 1]); upsert into array_test_1 values (1004, array[0, 0, 1, 1, 1]); upsert into array_test_1 values (1005, array[1, 1, 1, 1, 1]);
create table test_table_1 (id integer not null primary key, val varchar); upsert into test_table_1 values (1001, 'abc'); upsert into test_table_1 values (1002, 'def'); upsert into test_table_1 values (1003, 'ghi');
0: jdbc:phoenix:localhost> select t1.id, t2.val, t1.arr[1], t1.arr[2], t1.arr[3] from array_test_1 as t1 join test_table_1 as t2 on t1.id = t2.id; +--------+---------+---------+------------------------+---------------+ | T1.ID | T2.VAL | ARRAY_ELEM(T1.ARR, 1) | ARRAY_ELEM(T1.ARR, 2) | ARRAY_ELEM(T1.ARR, 3) | +--------+---------+---------+-----------------+------------------------+ | 1001 | abc | null | null | null | | 1002 | def | null | null | null | | 1003 | ghi | null | null | null | +--------+---------+--------+------------------------+-----------------+ 3 rows selected (0.056 seconds)
However, directly selecting array elements from the array returns data correctly.
0: jdbc:phoenix:localhost> select [t1.id, t1.arr[1], t1.arr[2], t1.arr[3] from array_test_1 as t1; +-------+--------+-------------+-------------+ | ID | ARRAY_ELEM(ARR, 1) | ARRAY_ELEM(ARR, 2) | ARRAY_ELEM(ARR, 3) | +-------+--------+-------------+-------------+ | 1001 | 0 | 0 | 0 | | 1002 | 0 | 0 | 0 | | 1003 | 0 | 0 | 0 | | 1004 | 0 | 0 | 1 | | 1005 | 1 | 1 | 1 | +-------+-------+--------------+--------------+ 5 rows selected (0.044 seconds)
Attachments
Attachments
Issue Links
- relates to
-
PHOENIX-4917 ClassCastException when projecting array elements in hash join
-
- Closed
-
- links to
