[
https://issues.apache.org/jira/browse/DRILL-2724?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14486140#comment-14486140
]
Sean Hsuan-Yi Chu commented on DRILL-2724:
------------------------------------------
First, if you do not do casting from the CSV, those "numbers" will be treated
as VARCHAR. So the sorted result would look wrong.
Second, if you do cast, Calcite will know the types for each table. Then
Calcite uses its own definition of Type Compatibility to block the query from
proceeding.
One solution might be making Calcite less restrictive on Union-All's Type
Compatibility.
> Implicit cast test fails in Union All query (reports type mismatch)
> -------------------------------------------------------------------
>
> Key: DRILL-2724
> URL: https://issues.apache.org/jira/browse/DRILL-2724
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 0.9.0
> Environment: 4 node cluster on CentOS
> | 9d92b8e319f2d46e8659d903d355450e15946533 | DRILL-2580: Exit early from
> HashJoinBatch if build side is empty | 26.03.2015 @ 16:13:53 EDT
> Reporter: Khurram Faraaz
> Assignee: Sean Hsuan-Yi Chu
>
> Test that performs implicit cast in a Union All query fails. Each CSV file
> has only numeric data. Test was performed on 4 node cluster.
> {code}
> With casting, assuming implicit casting would work. Data in each of the csv
> files is numeric.
> 0: jdbc:drill:> select * from (select cast(columns[0] as bigint) from
> `bgint_f.csv` union all select cast(columns[0] as char(2)) from `char_f.csv`
> union all select cast(columns[0] as double) from `dbl_f.csv` union all select
> cast(columns[0] as float) from `float_f.csv` union all select cast(columns[0]
> as int) from `int_f.csv` union all select cast(columns[0] as varchar(100))
> from `vchar_f.csv`) order by EXPR$0;
> Query failed: SqlValidatorException: Type mismatch in column 1 of UNION ALL
> Error: exception while executing query: Failure while executing query.
> (state=,code=0)
> explain plan did not return the physical plan
> 0: jdbc:drill:> explain plan for select * from (select cast(columns[0] as
> bigint) from `bgint_f.csv` union all select cast(columns[0] as char(2)) from
> `char_f.csv` union all select cast(columns[0] as double) from `dbl_f.csv`
> union all select cast(columns[0] as float) from `float_f.csv` union all
> select cast(columns[0] as int) from `int_f.csv` union all select
> cast(columns[0] as varchar(100)) from `vchar_f.csv`) order by EXPR$0;
> Query failed: SqlValidatorException: Type mismatch in column 1 of UNION ALL
> Error: exception while executing query: Failure while executing query.
> (state=,code=0)
> Stack trace from drillbit.log
> 2015-04-08 21:30:44,553 [2ada61fa-8207-279a-97fd-b40631cdb151:foreman] ERROR
> o.a.drill.exec.work.foreman.Foreman - Error
> 00ab460b-d2be-441e-8baa-0a8ff474769e: SqlValidatorException: Type mismatch in
> column 1 of UNION ALL
> org.apache.drill.exec.planner.sql.QueryInputException: Failure validating
> SQL. org.eigenbase.util.EigenbaseContextException: From line 1, column 23 to
> line 1, column 48: Type mismatch in column 1 of UNION ALL
> at
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:147)
> ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT]
> at
> org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:773)
> ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT]
> at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:204)
> ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT]
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> [na:1.7.0_75]
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> [na:1.7.0_75]
> at java.lang.Thread.run(Thread.java:745) [na:1.7.0_75]
> Caused by: net.hydromatic.optiq.tools.ValidationException:
> org.eigenbase.util.EigenbaseContextException: From line 1, column 23 to line
> 1, column 48: Type mismatch in column 1 of UNION ALL
> at
> net.hydromatic.optiq.prepare.PlannerImpl.validate(PlannerImpl.java:176)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:157)
> ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT]
> at
> org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:133)
> ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT]
> at
> org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:145)
> ~[drill-java-exec-0.9.0-SNAPSHOT-rebuffed.jar:0.9.0-SNAPSHOT]
> ... 5 common frames omitted
> Caused by: org.eigenbase.util.EigenbaseContextException: From line 1, column
> 23 to line 1, column 48: Type mismatch in column 1 of UNION ALL
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method) ~[na:1.7.0_75]
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
> ~[na:1.7.0_75]
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> ~[na:1.7.0_75]
> at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
> ~[na:1.7.0_75]
> at
> org.eigenbase.resource.Resources$ExInstWithCause.ex(Resources.java:348)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:673)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:661)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3588)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.type.SetopOperandTypeChecker.checkOperandTypes(SetopOperandTypeChecker.java:100)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.SqlOperator.checkOperandTypes(SqlOperator.java:533)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.SqlOperator.validateOperands(SqlOperator.java:412)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SetopNamespace.validateImpl(SetopNamespace.java:70)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:85)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:785)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:774)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SetopNamespace.validateImpl(SetopNamespace.java:68)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:85)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:785)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:774)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SetopNamespace.validateImpl(SetopNamespace.java:68)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:85)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:785)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:774)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SetopNamespace.validateImpl(SetopNamespace.java:68)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:85)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:785)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:774)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SetopNamespace.validateImpl(SetopNamespace.java:68)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:85)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:785)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:774)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2605)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2590)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2813)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:85)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:785)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:774)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:211)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:748)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:464)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> net.hydromatic.optiq.prepare.PlannerImpl.validate(PlannerImpl.java:174)
> ~[optiq-core-0.9-drill-r20.jar:na]
> ... 8 common frames omitted
> Caused by: org.eigenbase.sql.validate.SqlValidatorException: Type mismatch in
> column 1 of UNION ALL
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method) ~[na:1.7.0_75]
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
> ~[na:1.7.0_75]
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> ~[na:1.7.0_75]
> at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
> ~[na:1.7.0_75]
> at
> org.eigenbase.resource.Resources$ExInstWithCause.ex(Resources.java:348)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at org.eigenbase.resource.Resources$ExInst.ex(Resources.java:457)
> ~[optiq-core-0.9-drill-r20.jar:na]
> ... 45 common frames omitted
> This is how my data looks like in each of the CSV files
> [root@centos-01 csv_dir]# cat bgint_f.csv
> 1
> 2
> 0
> -1
> 1000000
> 65535
> 100
> 13
> 19
> 17
> 111111
> 1010101
> 9999999
> [root@centos-01 csv_dir]# cat int_f.csv
> 1
> 0
> -1
> 65535
> 1234567
> 1000000
> 101010
> 11111
> 100
> 13
> 19
> 17
> [root@centos-01 csv_dir]# cat dbl_f.csv
> 123.45
> 11.98
> 12345.39
> 1.1
> 1.0
> 0.0
> -1.0
> 11111.99
> 99999.99
> [root@centos-01 csv_dir]# cat float_f.csv
> 1.1
> 1.234
> 1234.19
> 13.19
> 1.0
> -1.0
> 0.0
> 1111.98
> 9999.99
> [root@centos-01 csv_dir]# cat char_f.csv
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 0
> [root@centos-01 csv_dir]# cat vchar_f.csv
> 12345
> 1
> 0
> -1
> 200000
> 1000000
> 65535
> 13
> 19
> 17
> 11111
> 10101
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
