[
https://issues.apache.org/jira/browse/DRILL-2688?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14745608#comment-14745608
]
Victoria Markman commented on DRILL-2688:
-----------------------------------------
ISO/IEC 9075-2:2011(E) (6.41 <multiset value constructor>) states: "b) QE shall
not immediately contain an <order by clause>"
Example from postgres:
{code}
postgres=# select a1
postgres-# from t1
postgres-# where a1 = 1
postgres-# UNION ALL
postgres-# select a2
postgres-# from t2
postgres-# where a2 is not null
postgres-# group by a1
postgres-# order by a1;
ERROR: column "a1" does not exist
LINE 8: group by a1
^
HINT: There is a column named "a1" in table "*SELECT* 1", but it cannot be
referenced from this part of the query.
{code}
If you want to sort result of union all (drill 1.2.0)
{code}
0: jdbc:drill:schema=dfs> select * from
. . . . . . . . . . . . > (
. . . . . . . . . . . . > select a1
. . . . . . . . . . . . > from t1
. . . . . . . . . . . . > where a1 = 1
. . . . . . . . . . . . > UNION ALL
. . . . . . . . . . . . > select a2
. . . . . . . . . . . . > from t2
. . . . . . . . . . . . > where a2 is not null
. . . . . . . . . . . . > ) as dt
. . . . . . . . . . . . > group by a1
. . . . . . . . . . . . > order by a1;
+-----+
| a1 |
+-----+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+-----+
10 rows selected (0.448 seconds)
{code}
> Use of ORDER BY on right side of Union All results in SqlValidatorException
> ---------------------------------------------------------------------------
>
> Key: DRILL-2688
> URL: https://issues.apache.org/jira/browse/DRILL-2688
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 0.9.0
> Environment: 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
> Priority: Minor
> Fix For: 1.2.0
>
>
> Order by in the right leg of Union All results in SqlValidatorException.
> Executing the query individually (the query that is on right side of Union
> All) gives expected results and no Exceptions are seen. However, executing
> the query as part of the right leg of Union All results in Exception.
> Tests were executed on 4 node cluster on CentOS. Both sides of Union All got
> input from CSV files.
> The query below returns correct results when executed individually. (this is
> the same query used on right side of Union All query)
> {code}
> 0: jdbc:drill:> select columns[0] from `testWindow.csv` t2 where
> t2.columns[0] is not null group by columns[0] order by columns[0];
> +------------+
> | EXPR$0 |
> +------------+
> | 10 |
> | 100 |
> | 113 |
> | 119 |
> | 2 |
> | 50 |
> | 55 |
> | 57 |
> | 61 |
> | 67 |
> | 89 |
> +------------+
> 11 rows selected (0.265 seconds)
> {code}
> Note that the Union All query works when there is no order by in the right
> leg.
> {code}
> 0: jdbc:drill:> select columns[0] from `employee.csv` t1 where
> t1.columns[0]=1 union all select columns[0] from `testWindow.csv` t2 where
> t2.columns[0] is not null group by columns[0];
> +------------+
> | EXPR$0 |
> +------------+
> | 1 |
> | 100 |
> | 10 |
> | 2 |
> | 50 |
> | 55 |
> | 67 |
> | 113 |
> | 119 |
> | 89 |
> | 57 |
> | 61 |
> +------------+
> 12 rows selected (0.175 seconds)
> {code}
> In this case we do use an order by on the right side of Union All, which
> results in SqlValidatorException. Same query as above, except that we have an
> order by clause in the query on the right side of Union All.
> {code}
> 0: jdbc:drill:> select columns[0] from `employee.csv` t1 where
> t1.columns[0]=1 union all select columns[0] from `testWindow.csv` t2 where
> t2.columns[0] is not null group by columns[0] order by columns[0];
> Query failed: SqlValidatorException: Column 'columns' not found in any table
> Error: exception while executing query: Failure while executing query.
> (state=,code=0)
> {code}
> Stack trace from drillbit.log
> {code}
> 2015-04-04 04:31:22,882 [2ae096e5-4085-de95-5642-ae10d320a8f7:foreman] ERROR
> o.a.drill.exec.work.foreman.Foreman - Error
> c6a60f71-c959-4ae5-ba8c-23b9c7b6bb7e: SqlValidatorException: Column 'columns'
> not found in any table
> org.apache.drill.exec.planner.sql.QueryInputException: Failure validating
> SQL. org.eigenbase.util.EigenbaseContextException: From line 1, column 178 to
> line 1, column 184: Column 'columns' not found in any table
> 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 178 to line
> 1, column 184: Column 'columns' not found in any table
> 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
> 178 to line 1, column 184: Column 'columns' not found in any table
> 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.validate.EmptyScope.findQualifyingTableName(EmptyScope.java:95)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.ListScope.findQualifyingTableName(ListScope.java:107)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.DelegatingScope.findQualifyingTableName(DelegatingScope.java:104)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.DelegatingScope.fullyQualify(DelegatingScope.java:144)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.OrderByScope.fullyQualify(OrderByScope.java:82)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:4058)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl$Expander.visit(SqlValidatorImpl.java:4038)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at org.eigenbase.sql.SqlIdentifier.accept(SqlIdentifier.java:222)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:126)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild(SqlShuttle.java:93)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at org.eigenbase.sql.SqlOperator.acceptCall(SqlOperator.java:688)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl$Expander.visitScoped(SqlValidatorImpl.java:4090)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:49)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlScopedShuttle.visit(SqlScopedShuttle.java:31)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at org.eigenbase.sql.SqlCall.accept(SqlCall.java:125)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.expand(SqlValidatorImpl.java:3734)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateOrderList(SqlValidatorImpl.java:2939)
> ~[optiq-core-0.9-drill-r20.jar:na]
> at
> org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2829)
> ~[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: Column 'columns'
> not found in any table
> 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]
> ... 37 common frames omitted
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
