Return-Path: X-Original-To: apmail-drill-issues-archive@minotaur.apache.org Delivered-To: apmail-drill-issues-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id D89AA1739F for ; Thu, 2 Jul 2015 21:02:04 +0000 (UTC) Received: (qmail 49365 invoked by uid 500); 2 Jul 2015 21:02:04 -0000 Delivered-To: apmail-drill-issues-archive@drill.apache.org Received: (qmail 49338 invoked by uid 500); 2 Jul 2015 21:02:04 -0000 Mailing-List: contact issues-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@drill.apache.org Delivered-To: mailing list issues@drill.apache.org Received: (qmail 49324 invoked by uid 99); 2 Jul 2015 21:02:04 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 02 Jul 2015 21:02:04 +0000 Date: Thu, 2 Jul 2015 21:02:04 +0000 (UTC) From: "Sean Hsuan-Yi Chu (JIRA)" To: issues@drill.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (DRILL-2724) Implicit cast test fails in Union All query (reports type mismatch) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ 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)