drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khurram Faraaz (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-4518) Two or more columns present in <row value predicand> of IN predicate, query returns wrong results.
Date Thu, 17 Mar 2016 09:09:33 GMT
Khurram Faraaz created DRILL-4518:
-------------------------------------

             Summary: 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


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)

Mime
View raw message