[ https://issues.apache.org/jira/browse/DRILL-4518?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Khurram Faraaz updated DRILL-4518: ---------------------------------- Attachment: f_20160316.json attached data file used in test. > Two or more columns present in <row value predicand> 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 > Attachments: f_20160316.json > > > Two or more columns present in <row value predicand> of IN predicate, query > returns wrong results. > Drill 1.7.0-SNAPSHOT git commit ID: 245da979 > {noformat} > 0: jdbc:drill:schema=dfs.tmp> alter system set > `store.json.all_text_mode`=true; > +-------+------------------------------------+ > | ok | summary | > +-------+------------------------------------+ > | true | store.json.all_text_mode updated. | > +-------+------------------------------------+ > 1 row selected (0.15 seconds) > 0: jdbc:drill:schema=dfs.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=dfs.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=dfs.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: Values > 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=,code=0) > 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-381a-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 line > 1, column 35 to line 1, column 68: Values passed to IN operator must have > compatible types > SQL Query null > [Error Id: 740e94a7-b61b-4dbf-96f3-8166c4f94164 ] > at > org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:543) > ~[drill-common-1.7.0-SNAPSHOT.jar:1.7.0-SNAPSHOT] > at > org.apache.drill.exec.planner.sql.SqlConverter.validate(SqlConverter.java:157) > [drill-java-exec-1.7.0-SNAPSHOT.jar:1.7.0-SNAPSHOT] > at > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:581) > [drill-java-exec-1.7.0-SNAPSHOT.jar:1.7.0-SNAPSHOT] > at > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:192) > [drill-java-exec-1.7.0-SNAPSHOT.jar:1.7.0-SNAPSHOT] > at > org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:164) > [drill-java-exec-1.7.0-SNAPSHOT.jar:1.7.0-SNAPSHOT] > at > org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.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:927) > [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(ThreadPoolExecutor.java:1145) > [na:1.7.0_45] > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.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 > compatible types > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) ~[na:1.7.0_45] > at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) > ~[na:1.7.0_45] > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.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(Resources.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.newValidationError(SqlValidatorImpl.java:3931) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] > at > org.apache.calcite.sql.fun.SqlInOperator.deriveType(SqlInOperator.java:154) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4268) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] > at > org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.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) > ~[calcite-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(SqlValidatorImpl.java:1478) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereOrOn(SqlValidatorImpl.java:3375) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause(SqlValidatorImpl.java:3362) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-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(SelectNamespace.java:60) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] > at > org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:877) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:863) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] > 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.validateScopedExpression(SqlValidatorImpl.java:837) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] > at > org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:551) > ~[calcite-core-1.4.0-drill-r10.jar:1.4.0-drill-r10] > at > org.apache.drill.exec.planner.sql.SqlConverter.validate(SqlConverter.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 > passed to IN operator must have compatible types > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) ~[na:1.7.0_45] > at > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) > ~[na:1.7.0_45] > at > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.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(Resources.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:514) > ~[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=dfs.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=dfs.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=dfs.tmp> explain plan for SELECT * FROM > `f_20160316.json` t WHERE (t.c2) IN ('1234','345643'); > +------+------+ > | text | json | > +------+------+ > | 00-00 Screen > 00-01 Project(*=[$0]) > 00-02 Project(T1¦¦*=[$0]) > 00-03 SelectionVectorRemover > 00-04 Filter(condition=[OR(=($1, '1234'), =($1, '345643'))]) > 00-05 Project(T1¦¦*=[$0], c2=[$1]) > 00-06 Scan(groupscan=[EasyGroupScan > [selectionRoot=maprfs:/tmp/f_20160316.json, numFiles=1, columns=[`*`], > files=[maprfs:///tmp/f_20160316.json]]]) > 0: jdbc:drill:schema=dfs.tmp> explain plan for SELECT * FROM > `f_20160316.json` t WHERE (t.c1) IN ('1234','345643'); > +------+------+ > | text | json | > +------+------+ > | 00-00 Screen > 00-01 Project(*=[$0]) > 00-02 Project(T2¦¦*=[$0]) > 00-03 SelectionVectorRemover > 00-04 Filter(condition=[OR(=($1, '1234'), =($1, '345643'))]) > 00-05 Project(T2¦¦*=[$0], c1=[$1]) > 00-06 Scan(groupscan=[EasyGroupScan > [selectionRoot=maprfs:/tmp/f_20160316.json, numFiles=1, columns=[`*`], > files=[maprfs:///tmp/f_20160316.json]]]) > {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)