[
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)
