drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sean Hsuan-Yi Chu (JIRA)" <j...@apache.org>
Subject [jira] [Resolved] (DRILL-2688) Use of ORDER BY on right side of Union All results in SqlValidatorException
Date Tue, 02 Jun 2015 17:31:49 GMT

     [ https://issues.apache.org/jira/browse/DRILL-2688?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Sean Hsuan-Yi Chu resolved DRILL-2688.
--------------------------------------
    Resolution: Invalid

Here is the reference:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries004.htm#SQLRF52323

Order-by is not associated with the subquery (union-all 's inputs). Instead, it is applied
at the last. Also, I tried this query in Postgres. Order-by is applied after union-all.



> 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)

Mime
View raw message