phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gerald Sangudi <gsang...@23andme.com>
Subject Re: Null array elements with joins
Date Mon, 13 Aug 2018 18:08:21 GMT
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)
>
>
>

Mime
View raw message