[ https://issues.apache.org/jira/browse/DRILL-2724?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14745588#comment-14745588 ]
Victoria Markman commented on DRILL-2724: ----------------------------------------- Closing, as this is not a bug, but expected behavior. > 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)