[
https://issues.apache.org/jira/browse/DRILL-2724?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14612512#comment-14612512
]
Sean Hsuan-Yi Chu commented on DRILL-2724:
------------------------------------------
Postgres won't allow you to do this. Implicit casting kicks in when we are in
the schema-less context.
However, since explicit casting was given by user, schema is "known". And it
would not make too much sense if implicit casting is allowed to override the
types specified by users
> 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
> Fix For: 1.2.0
>
>
> 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)
