I commented on the JIRA you filed here: PHOENIX-4791. Best to keep discussion there.
Thanks,
James

On Mon, Aug 13, 2018 at 11:08 AM, Gerald Sangudi <gsangudi@23andme.com> wrote:
Hello all,

Any suggestions or pointers on the issue below?

Projecting array elements works when not using joins, and does not work when we use hash joins. Is there an issue with the ProjectionCompiler for joins? I have not been able to isolate the specific cause, and would appreciate any pointers or suggestions.

Thanks,
Gerald

On Tue, Jun 19, 2018 at 10:02 AM, Tulasi Paradarami <tulasi.krishna.p@gmail.com> wrote:
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)