Return-Path: X-Original-To: apmail-drill-issues-archive@minotaur.apache.org Delivered-To: apmail-drill-issues-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id DBFAF18390 for ; Thu, 17 Mar 2016 09:09:33 +0000 (UTC) Received: (qmail 581 invoked by uid 500); 17 Mar 2016 09:09:33 -0000 Delivered-To: apmail-drill-issues-archive@drill.apache.org Received: (qmail 519 invoked by uid 500); 17 Mar 2016 09:09:33 -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 508 invoked by uid 99); 17 Mar 2016 09:09:33 -0000 Received: from arcas.apache.org (HELO arcas) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 17 Mar 2016 09:09:33 +0000 Received: from arcas.apache.org (localhost [127.0.0.1]) by arcas (Postfix) with ESMTP id 860812C1F54 for ; Thu, 17 Mar 2016 09:09:33 +0000 (UTC) Date: Thu, 17 Mar 2016 09:09:33 +0000 (UTC) From: "Khurram Faraaz (JIRA)" To: issues@drill.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Created] (DRILL-4518) Two or more columns present in of IN predicate, query returns wrong results. MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 Khurram Faraaz created DRILL-4518: ------------------------------------- Summary: Two or more columns present in = of IN predicate, query returns wrong results. Key: DRILL-4518 URL: https://issues.apache.org/jira/browse/DRILL-4518 Project: Apache Drill Issue Type: Bug Components: Query Planning & Optimization Affects Versions: 1.7.0 Environment: 4 node cluster CentOS Reporter: Khurram Faraaz Two or more columns present in of IN predicate, query= returns wrong results. Drill 1.7.0-SNAPSHOT git commit ID: 245da979 {noformat} 0: jdbc:drill:schema=3Ddfs.tmp> alter system set `store.json.all_text_mode`= =3Dtrue; +-------+------------------------------------+ | ok | summary | +-------+------------------------------------+ | true | store.json.all_text_mode updated. | +-------+------------------------------------+ 1 row selected (0.15 seconds) 0: jdbc:drill:schema=3Ddfs.tmp> SELECT * FROM `f_20160316.json` t WHERE (t.= c1) IN (1234,345643); +-------+ | c1 | +-------+ | 1234 | +-------+ 1 row selected (0.292 seconds) 0: jdbc:drill:schema=3Ddfs.tmp> SELECT * FROM `f_20160316.json` t WHERE (t.= c2) IN (1234,345643); +-------+ | c1 | +-------+ | null | +-------+ 1 row selected (0.224 seconds) 0: jdbc:drill:schema=3Ddfs.tmp> SELECT * FROM `f_20160316.json` t WHERE (t.= c1,t.c2) IN (1234,345643); Error: VALIDATION ERROR: From line 1, column 35 to line 1, column 68: Value= s passed to IN operator must have compatible types SQL Query null [Error Id: 740e94a7-b61b-4dbf-96f3-8166c4f94164 on centos-04.qa.lab:31010] = (state=3D,code=3D0) Stack trace from drillbit.log for above failure. 2016-03-17 06:57:40,227 [2915aa9b-381a-119d-2814-711fea9dd07c:foreman] INFO= o.a.drill.exec.work.foreman.Foreman - Query text for query id 2915aa9b-38= 1a-119d-2814-711fea9dd07c: SELECT * FROM `f_20160316.json` t WHERE (t.c1,t.= c2) IN (1234,345643) 2016-03-17 06:57:40,286 [2915aa9b-381a-119d-2814-711fea9dd07c:foreman] INFO= o.a.d.exec.planner.sql.SqlConverter - User Error Occurred org.apache.drill.common.exceptions.UserException: VALIDATION ERROR: From li= ne 1, column 35 to line 1, column 68: Values passed to IN operator must hav= e compatible types SQL Query null [Error Id: 740e94a7-b61b-4dbf-96f3-8166c4f94164 ] at org.apache.drill.common.exceptions.UserException$Builder.build(U= serException.java:543) ~[drill-common-1.7.0-SNAPSHOT.jar:1.7.0-SNAPSHOT] at org.apache.drill.exec.planner.sql.SqlConverter.validate(SqlConve= rter.java:157) [drill-java-exec-1.7.0-SNAPSHOT.jar:1.7.0-SNAPSHOT] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.val= idateNode(DefaultSqlHandler.java:581) [drill-java-exec-1.7.0-SNAPSHOT.jar:1= .7.0-SNAPSHOT] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.val= idateAndConvert(DefaultSqlHandler.java:192) [drill-java-exec-1.7.0-SNAPSHOT= .jar:1.7.0-SNAPSHOT] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.get= Plan(DefaultSqlHandler.java:164) [drill-java-exec-1.7.0-SNAPSHOT.jar:1.7.0-= SNAPSHOT] at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSq= lWorker.java:94) [drill-java-exec-1.7.0-SNAPSHOT.jar:1.7.0-SNAPSHOT] at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:9= 27) [drill-java-exec-1.7.0-SNAPSHOT.jar:1.7.0-SNAPSHOT] at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:251)= [drill-java-exec-1.7.0-SNAPSHOT.jar:1.7.0-SNAPSHOT] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExec= utor.java:1145) [na:1.7.0_45] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExe= cutor.java:615) [na:1.7.0_45] at java.lang.Thread.run(Thread.java:744) [na:1.7.0_45] Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1,= column 35 to line 1, column 68: Values passed to IN operator must have com= patible types at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Me= thod) ~[na:1.7.0_45] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeCons= tructorAccessorImpl.java:57) ~[na:1.7.0_45] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Delega= tingConstructorAccessorImpl.java:45) ~[na:1.7.0_45] at java.lang.reflect.Constructor.newInstance(Constructor.java:526) = ~[na:1.7.0_45] at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resource= s.java:405) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:= 714) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:= 702) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationEr= ror(SqlValidatorImpl.java:3931) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-dr= ill-r10] at org.apache.calcite.sql.fun.SqlInOperator.deriveType(SqlInOperato= r.java:154) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisit= or.visit(SqlValidatorImpl.java:4268) ~[calcite-core-1.4.0-drill-r10.jar:1.4= .0-drill-r10] at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisit= or.visit(SqlValidatorImpl.java:4255) ~[calcite-core-1.4.0-drill-r10.jar:1.4= .0-drill-r10] at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:130) ~[calcit= e-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(= SqlValidatorImpl.java:1495) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-= r10] at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlV= alidatorImpl.java:1478) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOr= On(SqlValidatorImpl.java:3375) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-dri= ll-r10] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereCl= ause(SqlValidatorImpl.java:3362) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-d= rill-r10] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(= SqlValidatorImpl.java:2987) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-= r10] at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(Sel= ectNamespace.java:60) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] at org.apache.calcite.sql.validate.AbstractNamespace.validate(Abstr= actNamespace.java:86) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespa= ce(SqlValidatorImpl.java:877) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-dril= l-r10] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(S= qlValidatorImpl.java:863) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r1= 0] at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:210) ~[= calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedE= xpression(SqlValidatorImpl.java:837) ~[calcite-core-1.4.0-drill-r10.jar:1.4= .0-drill-r10] at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlVal= idatorImpl.java:551) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] at org.apache.drill.exec.planner.sql.SqlConverter.validate(SqlConve= rter.java:148) [drill-java-exec-1.7.0-SNAPSHOT.jar:1.7.0-SNAPSHOT] ... 9 common frames omitted Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Values pa= ssed to IN operator must have compatible types at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Me= thod) ~[na:1.7.0_45] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeCons= tructorAccessorImpl.java:57) ~[na:1.7.0_45] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Delega= tingConstructorAccessorImpl.java:45) ~[na:1.7.0_45] at java.lang.reflect.Constructor.newInstance(Constructor.java:526) = ~[na:1.7.0_45] at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resource= s.java:405) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:51= 4) ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] ... 29 common frames omitted {noformat} -- when values enclosed within quotes inside IN predicate, in this case too= only second query below returns correct results. -- store.json.all_text_mode was set to true {noformat} 0: jdbc:drill:schema=3Ddfs.tmp> SELECT * FROM `f_20160316.json` t WHERE (t.= c2) IN ('1234','345643'); +-------+ | c1 | +-------+ | null | +-------+ 1 row selected (0.31 seconds) 0: jdbc:drill:schema=3Ddfs.tmp> SELECT * FROM `f_20160316.json` t WHERE (t.= c1) IN ('1234','345643'); +-------+ | c1 | +-------+ | 1234 | +-------+ 1 row selected (0.235 seconds) Query plan for the above two queries. 0: jdbc:drill:schema=3Ddfs.tmp> explain plan for SELECT * FROM `f_20160316.= json` t WHERE (t.c2) IN ('1234','345643'); +------+------+ | text | json | +------+------+ | 00-00 Screen 00-01 Project(*=3D[$0]) 00-02 Project(T1=C2=A6=C2=A6*=3D[$0]) 00-03 SelectionVectorRemover 00-04 Filter(condition=3D[OR(=3D($1, '1234'), =3D($1, '345643'))= ]) 00-05 Project(T1=C2=A6=C2=A6*=3D[$0], c2=3D[$1]) 00-06 Scan(groupscan=3D[EasyGroupScan [selectionRoot=3Dmaprf= s:/tmp/f_20160316.json, numFiles=3D1, columns=3D[`*`], files=3D[maprfs:///t= mp/f_20160316.json]]]) 0: jdbc:drill:schema=3Ddfs.tmp> explain plan for SELECT * FROM `f_20160316.= json` t WHERE (t.c1) IN ('1234','345643'); +------+------+ | text | json | +------+------+ | 00-00 Screen 00-01 Project(*=3D[$0]) 00-02 Project(T2=C2=A6=C2=A6*=3D[$0]) 00-03 SelectionVectorRemover 00-04 Filter(condition=3D[OR(=3D($1, '1234'), =3D($1, '345643'))= ]) 00-05 Project(T2=C2=A6=C2=A6*=3D[$0], c1=3D[$1]) 00-06 Scan(groupscan=3D[EasyGroupScan [selectionRoot=3Dmaprf= s:/tmp/f_20160316.json, numFiles=3D1, columns=3D[`*`], files=3D[maprfs:///t= mp/f_20160316.json]]]) {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)