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, 27 Aug 2018 16:38:53 GMT
Hi folks,

I submitted PR https://github.com/apache/phoenix/pull/335

Appreciate your review.

Thanks,
Gerald

On Mon, Aug 13, 2018 at 1:10 PM, James Taylor <jamestaylor@apache.org>
wrote:

> 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)
>>>
>>>
>>>
>>
>

Mime
View raw message