Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-2724

Implicit cast test fails in Union All query (reports type mismatch)

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Won't Fix
    • 0.9.0
    • 1.2.0
    • None

    Description

      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.

      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
      

      Attachments

        Issue Links

          Activity

            People

              seanhychu Sean Hsuan-Yi Chu
              khfaraaz Khurram Faraaz
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: