[
https://issues.apache.org/jira/browse/DRILL-2631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14389280#comment-14389280
]
Khurram Faraaz commented on DRILL-2631:
---------------------------------------
Project two columns from output of Union All, also gives same Exception
{code}
select sum_salary, name 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}
> 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: Sean Hsuan-Yi Chu
>
> 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)
