Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 0A12A109D8 for ; Wed, 12 Mar 2014 13:47:52 +0000 (UTC) Received: (qmail 17933 invoked by uid 500); 12 Mar 2014 13:47:49 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 16895 invoked by uid 500); 12 Mar 2014 13:47:41 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 16871 invoked by uid 99); 12 Mar 2014 13:47:37 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 12 Mar 2014 13:47:37 +0000 X-ASF-Spam-Status: No, hits=-5.0 required=5.0 tests=RCVD_IN_DNSWL_HI,SPF_HELO_PASS,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of mkudlej@redhat.com designates 209.132.183.28 as permitted sender) Received: from [209.132.183.28] (HELO mx1.redhat.com) (209.132.183.28) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 12 Mar 2014 13:47:32 +0000 Received: from int-mx12.intmail.prod.int.phx2.redhat.com (int-mx12.intmail.prod.int.phx2.redhat.com [10.5.11.25]) by mx1.redhat.com (8.14.4/8.14.4) with ESMTP id s2CDl8ct021957 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Wed, 12 Mar 2014 09:47:09 -0400 Received: from dhcp-27-250.brq.redhat.com (dhcp-27-250.brq.redhat.com [10.34.27.250]) by int-mx12.intmail.prod.int.phx2.redhat.com (8.14.4/8.14.4) with ESMTP id s2CDl71t022020 for ; Wed, 12 Mar 2014 09:47:07 -0400 Message-ID: <53206560.3090300@redhat.com> Date: Wed, 12 Mar 2014 14:47:12 +0100 From: Martin Kudlej User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.2.0 MIME-Version: 1.0 To: user@hive.apache.org Subject: full outer join result Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Scanned-By: MIMEDefang 2.68 on 10.5.11.25 X-Virus-Checked: Checked by ClamAV on apache.org Hi all, I've tried BigTop test for join_filters: CREATE TABLE myinput1(key int, value int); LOAD DATA LOCAL INPATH 'seed_data_files/in3.txt' INTO TABLE myinput1; where seed_data_files/in3.txt: 12 35 NULL 40 48 NULL 100 100 I've tried: SELECT * FROM myinput1 a FULL OUTER JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value ORDER BY a.key, a.value, b.key, b.value; and expected result in test is: NULL NULL NULL 40 NULL NULL NULL 40 NULL NULL NULL 40 NULL NULL NULL 40 NULL NULL 12 35 NULL NULL 12 35 NULL NULL 12 35 NULL NULL 12 35 NULL NULL 48 NULL NULL NULL 48 NULL NULL NULL 48 NULL NULL NULL 48 NULL NULL 40 NULL NULL NULL 40 NULL NULL NULL 40 NULL NULL NULL 40 NULL NULL 12 35 NULL NULL 12 35 NULL NULL 12 35 NULL NULL 12 35 NULL NULL 48 NULL NULL NULL 48 NULL NULL NULL 48 NULL NULL NULL 48 NULL NULL NULL 100 100 NULL NULL 100 100 NULL NULL 100 100 NULL NULL 100 100 100 100 but real hive result is: NULL NULL NULL 40 NULL NULL 12 35 NULL NULL 48 NULL NULL 40 NULL NULL 12 35 NULL NULL 48 NULL NULL NULL 100 100 100 100 btw. result from postgresql is: (SELECT * FROM myinput1 a LEFT JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value ORDER BY a.key, a.value, b.key, b.value) UNION (SELECT * FROM myinput1 a RIGHT JOIN myinput1 b on a.key > 40 AND a.value > 50 AND a.key = a.value AND b.key > 40 AND b.value > 50 AND b.key = b.value ORDER BY a.key, a.value, b.key, b.value); | | 12 | 35 12 | 35 | | | | 48 | 48 | | | | 40 | | | | | 40 100 | 100 | 100 | 100 so it's the same like in hive. What is the right result for this full outer join in HiveQL, please? -- Best Regards, Martin Kudlej. MRG/Grid & RHS-Hadoop Senior Quality Assurance Engineer Red Hat Czech s.r.o. Phone: +420 532 294 155 E-mail:mkudlej at redhat.com IRC: mkudlej at #brno, #messaging, #grid, #rhs, #distcomp