phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tulasi Paradarami <tulasi.krishn...@gmail.com>
Subject Null array elements with joins
Date Tue, 19 Jun 2018 17:02:17 GMT
Hi,

I'm running few tests against Phoenix array and running into this bug where
array elements return null values when a join is involved. Is this a known
issue/limitation of arrays?

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)

Mime
View raw message