drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khurram Faraaz (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-2631) Project one column from output of Union All results in Column count mismatch in UNION ALL
Date Tue, 31 Mar 2015 18:39:54 GMT
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)

Mime
View raw message