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 99CA21787F for ; Tue, 31 Mar 2015 18:39:55 +0000 (UTC) Received: (qmail 70060 invoked by uid 500); 31 Mar 2015 18:39:54 -0000 Delivered-To: apmail-drill-issues-archive@drill.apache.org Received: (qmail 70002 invoked by uid 500); 31 Mar 2015 18:39:54 -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 69938 invoked by uid 99); 31 Mar 2015 18:39:54 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 31 Mar 2015 18:39:54 +0000 Date: Tue, 31 Mar 2015 18:39:54 +0000 (UTC) From: "Khurram Faraaz (JIRA)" To: issues@drill.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Created] (DRILL-2631) Project one column from output of Union All results in Column count mismatch in UNION ALL MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 Khurram Faraaz created DRILL-2631: ------------------------------------- Summary: Project one column from output of Union All results in Column count mismatch in UNION ALL Key: DRILL-2631 URL: https://issues.apache.org/jira/browse/DRILL-2631 Project: Apache Drill Issue Type: Bug Components: Query Planning & Optimization Affects Versions: 0.9.0 Reporter: Khurram Faraaz Assignee: Jinfeng Ni Projecting values from a single column, from the output of Union All results in SqlValidatorException: Column count mismatch in Union All. Tests were run on a 4 node cluster. case 1) select distinct values from column, fails. {code} Error: exception while executing query: Failure while executing query. (state=,code=0) 0: jdbc:drill:> select distinct sum_salary from (select * from (select sum(cast(columns[3] as int)) over(partition by cast(columns[4] as varchar(25)) order by columns[4]) sum_salary, cast(columns[1] as varchar(50)) name, cast(columns[4] as varchar(25)) department from `testWindow.csv`) where sum_salary > 100000.0 order by name ) union all (select * from (select sum(cast(columns[3] as int)) over(partition by cast(columns[4] as varchar(25)) order by columns[4]) sum_salary, cast(columns[1] as varchar(50)) name, cast(columns[4] as varchar(25)) department from `testWindow.csv`) where sum_salary > 100000.0 order by name); Query failed: SqlValidatorException: Column count mismatch in UNION ALL Error: exception while executing query: Failure while executing query. (state=,code=0) {code} case 2) select non distinct values from column fails. {code} 0: jdbc:drill:> select sum_salary from (select * from (select sum(cast(columns[3] as int)) over(partition by cast(columns[4] as varchar(25)) order by columns[4]) sum_salary, cast(columns[1] as varchar(50)) name, cast(columns[4] as varchar(25)) department from `testWindow.csv`) where sum_salary > 100000.0 order by name ) union all (select * from (select sum(cast(columns[3] as int)) over(partition by cast(columns[4] as varchar(25)) order by columns[4]) sum_salary, cast(columns[1] as varchar(50)) name, cast(columns[4] as varchar(25)) department from `testWindow.csv`) where sum_salary > 100000.0 order by name); Query failed: SqlValidatorException: Column count mismatch in UNION ALL {code} Results returned by the sub-query.(this sub-query is used in the Union All query above in case 1 and case 2) {code} 0: jdbc:drill:> select * from (select sum(cast(columns[3] as int)) over(partition by cast(columns[4] as varchar(25)) order by columns[4]) sum_salary, cast(columns[1] as varchar(50)) name, cast(columns[4] as varchar(25)) department from `testWindow.csv`) where sum_salary > 100000.0 order by name; +------------+------------+------------+ | sum_salary | name | department | +------------+------------+------------+ | 452000 | Bill Sawyer | Engineering | | 452000 | Bob Sr | Engineering | | 452000 | Jane Doe | Engineering | | 452000 | Kumar | Engineering | | 199000 | Patrick | Sales | | 200000 | Rock Breaker | Product Management | | 199000 | Sam | Sales | | 452000 | Susan | Engineering | +------------+------------+------------+ 8 rows selected (0.217 seconds) {code} Case where, select * from (Query 1) Union All (Query 2); This query returns correct results. this is the case where we project all columns from output of Union All {code} 0: jdbc:drill:> select * from (select * from (select sum(cast(columns[3] as int)) over(partition by cast(columns[4] as varchar(25)) order by columns[4]) sum_salary, cast(columns[1] as varchar(50)) name, cast(columns[4] as varchar(25)) department from `testWindow.csv`) where sum_salary > 100000.0 order by name ) union all (select * from (select sum(cast(columns[3] as int)) over(partition by cast(columns[4] as varchar(25)) order by columns[4]) sum_salary, cast(columns[1] as varchar(50)) name, cast(columns[4] as varchar(25)) department from `testWindow.csv`) where sum_salary > 100000.0 order by name); +------------+------------+------------+ | sum_salary | name | department | +------------+------------+------------+ | 452000 | Bill Sawyer | Engineering | | 452000 | Bob Sr | Engineering | | 452000 | Jane Doe | Engineering | | 452000 | Kumar | Engineering | | 199000 | Patrick | Sales | | 200000 | Rock Breaker | Product Management | | 199000 | Sam | Sales | | 452000 | Susan | Engineering | | 452000 | Bill Sawyer | Engineering | | 452000 | Bob Sr | Engineering | | 452000 | Jane Doe | Engineering | | 452000 | Kumar | Engineering | | 199000 | Patrick | Sales | | 200000 | Rock Breaker | Product Management | | 199000 | Sam | Sales | | 452000 | Susan | Engineering | +------------+------------+------------+ 16 rows selected (0.343 seconds) {code} Data from the test file used in above tests {code} 0: jdbc:drill:> select * from `testWindow.csv`; +------------+ | columns | +------------+ | ["100","John Doe","35","80000","IT"] | | ["10","Bill Sawyer","37","90000","Engineering"] | | ["2","Rock Breaker","55","200000","Product Management"] | | ["50","Raj Verma","40","95000","HR"] | | ["55","Bob Sr","45","85000","Engineering"] | | ["67","Kumar","40","90000","Engineering"] | | ["113","Susan","42","100000","Engineering"] | | ["119","Jane Doe","39","87000","Engineering"] | | ["89","Bruce Li","32","80000","Fitness"] | | ["57","Sam","43","100000","Sales"] | | ["61","Patrick","41","99000","Sales"] | +------------+ 11 rows selected (0.132 seconds) {code} {code} Details of version | 9d92b8e319f2d46e8659d903d355450e15946533 | DRILL-2580: Exit early from HashJoinBatch if build side is empty | 26.03.2015 @ 16:13:53 EDT | Unknown | 26.03.2015 @ 16:53:21 EDT | {code} Stack trace from drillbit.log {code} 2015-03-31 18:29:18,184 [2ae51880-f35a-5805-038f-4c9410c26b81:foreman] INFO o.a.drill.exec.work.foreman.Foreman - foreman cleaning up - status: [] 2015-03-31 18:29:18,187 [2ae51880-f35a-5805-038f-4c9410c26b81:foreman] ERROR o.a.drill.exec.work.foreman.Foreman - Error af7b0447-a861-4031-a5a5-730e05bb4821: SqlValidatorException: Column count mismatch in UNION ALL org.apache.drill.exec.planner.sql.QueryInputException: Failure validating SQL. org.eigenbase.util.EigenbaseContextException: At line 1, column 326: Column count mismatch in 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: At line 1, column 326: Column count mismatch in 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: At line 1, column 326: Column count mismatch in UNION ALL at sun.reflect.GeneratedConstructorAccessor68.newInstance(Unknown Source) ~[na:na] 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:68) ~[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.SqlSetOperator.validateCall(SqlSetOperator.java:86) ~[optiq-core-0.9-drill-r20.jar:na] at org.eigenbase.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:3713) ~[optiq-core-0.9-drill-r20.jar:na] at org.eigenbase.sql.SqlCall.validate(SqlCall.java:102) ~[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: Column count mismatch in UNION ALL at sun.reflect.GeneratedConstructorAccessor69.newInstance(Unknown Source) ~[na:na] 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] ... 24 common frames omitted {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)