Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-2688

Use of ORDER BY on right side of Union All results in SqlValidatorException

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Minor
    • Resolution: Invalid
    • 0.9.0
    • 1.2.0
    • None

    Description

      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)

      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)
      

      Note that the Union All query works when there is no order by in the right leg.

      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)
      

      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.

      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)
      

      Stack trace from drillbit.log

      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
      

      Attachments

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            seanhychu Sean Hsuan-Yi Chu
            khfaraaz Khurram Faraaz
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment