drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sean Hsuan-Yi Chu (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-2724) Implicit cast test fails in Union All query (reports type mismatch)
Date Thu, 02 Jul 2015 21:02:04 GMT

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

Mime
View raw message