From dev-return-53639-archive-asf-public=cust-asf.ponee.io@phoenix.apache.org Mon Aug 13 22:10:58 2018 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id 533DE18067A for ; Mon, 13 Aug 2018 22:10:58 +0200 (CEST) Received: (qmail 13773 invoked by uid 500); 13 Aug 2018 20:10:57 -0000 Mailing-List: contact dev-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list dev@phoenix.apache.org Received: (qmail 13753 invoked by uid 99); 13 Aug 2018 20:10:57 -0000 Received: from mail-relay.apache.org (HELO mailrelay1-lw-us.apache.org) (207.244.88.152) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 13 Aug 2018 20:10:57 +0000 Received: from mail-qt0-f172.google.com (mail-qt0-f172.google.com [209.85.216.172]) by mailrelay1-lw-us.apache.org (ASF Mail Server at mailrelay1-lw-us.apache.org) with ESMTPSA id 2C4D321BD; Mon, 13 Aug 2018 20:10:56 +0000 (UTC) Received: by mail-qt0-f172.google.com with SMTP id n6-v6so18844865qtl.4; Mon, 13 Aug 2018 13:10:56 -0700 (PDT) X-Gm-Message-State: AOUpUlFdMKmu5L2TyyxcThCN/TVNiyzpArObCNwCEsQm1Wf2WO573YT/ GVtURC2qg1631c5nhbk0HWa/nTQEKEUJkCAIV9Y= X-Google-Smtp-Source: AA+uWPw3dZbEFl24xWCoI8wLdlBJiTMqoD1Bp54HwPyE2IbA6Cu1RH352CY48yaalalcE+6fS8dS66F1215EbHudHEY= X-Received: by 2002:aed:3bc1:: with SMTP id s1-v6mr18434419qte.343.1534191055915; Mon, 13 Aug 2018 13:10:55 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a0c:f988:0:0:0:0:0 with HTTP; Mon, 13 Aug 2018 13:10:55 -0700 (PDT) In-Reply-To: References: From: James Taylor Date: Mon, 13 Aug 2018 13:10:55 -0700 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Null array elements with joins To: user Cc: dev@phoenix.apache.org Content-Type: multipart/alternative; boundary="000000000000e7d0c4057356b0f0" --000000000000e7d0c4057356b0f0 Content-Type: text/plain; charset="UTF-8" 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 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) >> >> >> > --000000000000e7d0c4057356b0f0--