Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 2F8C9200C5B for ; Wed, 15 Mar 2017 01:03:50 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id 2E3A3160B7E; Wed, 15 Mar 2017 00:03:50 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 80593160B89 for ; Wed, 15 Mar 2017 01:03:49 +0100 (CET) Received: (qmail 56604 invoked by uid 500); 15 Mar 2017 00:03:48 -0000 Mailing-List: contact issues-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@drill.apache.org Delivered-To: mailing list issues@drill.apache.org Received: (qmail 56409 invoked by uid 99); 15 Mar 2017 00:03:47 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 15 Mar 2017 00:03:47 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id B14D7C0B07 for ; Wed, 15 Mar 2017 00:03:46 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.451 X-Spam-Level: * X-Spam-Status: No, score=1.451 tagged_above=-999 required=6.31 tests=[KAM_ASCII_DIVIDERS=0.8, RP_MATCHES_RCVD=-0.001, SPF_NEUTRAL=0.652] autolearn=disabled Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id 59ax_b7m6zE0 for ; Wed, 15 Mar 2017 00:03:45 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTP id 1FFB85F343 for ; Wed, 15 Mar 2017 00:03:45 +0000 (UTC) Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id F12E9E0984 for ; Wed, 15 Mar 2017 00:03:42 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id E451E243B6 for ; Wed, 15 Mar 2017 00:03:41 +0000 (UTC) Date: Wed, 15 Mar 2017 00:03:41 +0000 (UTC) From: "Gautam Kumar Parai (JIRA)" To: issues@drill.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (DRILL-5049) wrong results - correlated subquery interacting with null equality join MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 archived-at: Wed, 15 Mar 2017 00:03:50 -0000 [ https://issues.apache.org/jira/browse/DRILL-5049?page=3Dcom.atlassian= .jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1592= 5299#comment-15925299 ]=20 Gautam Kumar Parai commented on DRILL-5049: ------------------------------------------- Please refer CALCITE-714, CALCITE-1200 for a more complete reference. > wrong results - correlated subquery interacting with null equality join > ----------------------------------------------------------------------- > > Key: DRILL-5049 > URL: https://issues.apache.org/jira/browse/DRILL-5049 > Project: Apache Drill > Issue Type: Bug > Components: Execution - Flow > Affects Versions: 1.9.0 > Reporter: Khurram Faraaz > Assignee: Gautam Kumar Parai > Priority: Critical > Attachments: nullEqJoin_17.drill_res, nullEqJoin_17.postgres, t_a= lltype.parquet > > > Here is a query that uses null equality join. Drill 1.9.0 returns 124 rec= ords, whereas Postgres 9.3 returns 145 records. I am on Drill 1.9.0 git com= mit id: db308549 > I have attached the results from Drill 1.9.0 and Postgres, please review. > {noformat} > 0: jdbc:drill:schema=3Ddfs.tmp> explain plan for > . . . . . . . . . . . . . . > SELECT * > . . . . . . . . . . . . . . > FROM `t_alltype.parquet` t1 > . . . . . . . . . . . . . . > WHERE EXISTS > . . . . . . . . . . . . . . > ( > . . . . . . . . . . . . . . > SELECT * > . . . . . . . . . . . . . . > FROM `t_alltype.parquet` t2 > . . . . . . . . . . . . . . > WHERE t1.c4 =3D t2.c4 OR (t= 1.c4 IS NULL AND t2.c4 IS NULL) > . . . . . . . . . . . . . . > ); > +------+------+ > | text | json | > +------+------+ > | 00-00 Screen > 00-01 Project(*=3D[$0]) > 00-02 Project(T30=C2=A6=C2=A6*=3D[$0]) > 00-03 HashJoin(condition=3D[AND(=3D($1, $2), =3D($1, $3))], join= Type=3D[inner]) > 00-05 Project(T30=C2=A6=C2=A6*=3D[$0], c4=3D[$1]) > 00-07 Scan(groupscan=3D[ParquetGroupScan [entries=3D[ReadEnt= ryWithPath [path=3Dmaprfs:///tmp/t_alltype.parquet]], selectionRoot=3Dmaprf= s:/tmp/t_alltype.parquet, numFiles=3D1, usedMetadataFile=3Dfalse, columns= =3D[`*`]]]) > 00-04 HashAgg(group=3D[{0, 1}], agg#0=3D[MIN($2)]) > 00-06 Project(c40=3D[$1], c400=3D[$1], $f0=3D[true]) > 00-08 HashJoin(condition=3D[IS NOT DISTINCT FROM($0, $1)],= joinType=3D[inner]) > 00-10 Scan(groupscan=3D[ParquetGroupScan [entries=3D[Rea= dEntryWithPath [path=3Dmaprfs:///tmp/t_alltype.parquet]], selectionRoot=3Dm= aprfs:/tmp/t_alltype.parquet, numFiles=3D1, usedMetadataFile=3Dfalse, colum= ns=3D[`c4`]]]) > 00-09 Project(c40=3D[$0]) > 00-11 HashAgg(group=3D[{0}]) > 00-12 Scan(groupscan=3D[ParquetGroupScan [entries=3D= [ReadEntryWithPath [path=3Dmaprfs:///tmp/t_alltype.parquet]], selectionRoot= =3Dmaprfs:/tmp/t_alltype.parquet, numFiles=3D1, usedMetadataFile=3Dfalse, c= olumns=3D[`c4`]]]) > {noformat} -- This message was sent by Atlassian JIRA (v6.3.15#6346)