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

CalciteContextException - SELECT star, window function

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    Description

      This is a problem seen with regular window functions (not specific to nested aggregates).

      SELECT *, <window-function> OVER...
does not work on Drill

      Same query works on Postgres and on same data.

      postgres=# select *, MIN(col9) over(partition by col7 order by col8) from fewrwspqq_101 GROUP BY col0,col1,col2,col3,col4,col5,col6,col7,col8,col9;
    col0    |        col1         |   col2   |   col3    |     col4     |          col5           |    col6    | col7 | col8 |                         col9                         |                         min                          
------------+---------------------+----------+-----------+--------------+-------------------------+------------+------+------+------------------------------------------------------+------------------------------------------------------
          1 |               65534 |      256 |    1234.9 | 20:26:18.58  | 2014-03-02 00:28:02.338 | 1952-08-14 | f    | CA   | AXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXZ | AXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXZ
         13 |                 200 |        1 |    -65534 | 19:20:30.5   | 2004-06-02 00:28:02.418 | 1972-04-03 | f    | GA   | PXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXD | AXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXZ
          7 |                  17 |      -33 |      33.9 | 13:13:13.13  | 2006-05-02 00:28:02.748 | 1992-12-12 | f    | I
...

      Drill throws error

      0: jdbc:drill:schema=dfs.tmp> select *, MIN(col9) over(partition by col7 order by col8) from `allTypsUniq.parquet` GROUP BY col0,col1,col2,col3,col4,col5,col6,col7,col8,col9;
Error: VALIDATION ERROR: At line 1, column 8: Expression 'allTypsUniq.parquet.*' is not being grouped

SQL Query null

[Error Id: f83e438f-5cdf-42f2-a548-5dc17b49da07 on centos-01.qa.lab:31010] (state=,code=0)

      Upon expanding star in the project Drill returns results

      0: jdbc:drill:schema=dfs.tmp> select col0,col1,col2,col3,col4,col5,col6,col7,col8,col9, MIN(col9) over(partition by col7 order by col8) from `allTypsUniq.parquet` GROUP BY col0,col1,col2,col3,col4,col5,col6,col7,col8,col9;
+-------------+----------------------+-----------+------------+---------------+--------------------------+-------------+--------+-------+-------------------------------------------------------+-------------------------------------------------------+
|    col0     |         col1         |   col2    |    col3    |     col4      |           col5           |    col6     |  col7  | col8  |                         col9                          |                        EXPR$10                        |
+-------------+----------------------+-----------+------------+---------------+--------------------------+-------------+--------+-------+-------------------------------------------------------+-------------------------------------------------------+
| 1           | 65534                | 256.0     | 1234.9     | 20:26:18.580  | 2014-03-02 00:28:02.338  | 1952-08-14  | false  | CA    | AXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXZ  | AXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXZ  |
| 13          | 200                  | 1.0       | -65534.0   | 19:20:30.500  | 2004-06-02 00:28:02.418  | 1972-04-03  | false  | GA    | PXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXD  | AXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXZ  |
| 7           | 17                   | -33.0     | 33.9       | 13:13:13.130  | 2006-05-02 00:28:02.748  | 1992-12-12  | false  | IA    | UXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXB  | AXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXZ  |
...

      Stack trace from drillbit.log

      2016-07-27 13:52:45,103 [28674351-99f0-2691-02f8-e6d576c85be1:foreman] INFO  o.a.drill.exec.work.foreman.Foreman - Query text for query id 28674351-99f0-2691-02f8-e6d576c85be1: select *, MIN(col9) over(partition by col7 order by col8) from `allTypsUniq.parquet` GROUP BY col0,col1,col2,col3,col4,col5,col6,col7,col8,col9
2016-07-27 13:52:45,132 [28674351-99f0-2691-02f8-e6d576c85be1:foreman] INFO  o.a.d.exec.planner.sql.SqlConverter - User Error Occurred
org.apache.drill.common.exceptions.UserException: VALIDATION ERROR: At line 1, column 8: Expression 'allTypsUniq.parquet.*' is not being grouped

SQL Query null

[Error Id: c6758b36-8cb6-4965-861f-2aaf573c4002 ]
        at org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:543) ~[drill-common-1.8.0-SNAPSHOT.jar:1.8.0-SNAPSHOT]
        at org.apache.drill.exec.planner.sql.SqlConverter.validate(SqlConverter.java:164) [drill-java-exec-1.8.0-SNAPSHOT.jar:1.8.0-SNAPSHOT]
        at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:600) [drill-java-exec-1.8.0-SNAPSHOT.jar:1.8.0-SNAPSHOT]
        at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:192) [drill-java-exec-1.8.0-SNAPSHOT.jar:1.8.0-SNAPSHOT]
        at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:164) [drill-java-exec-1.8.0-SNAPSHOT.jar:1.8.0-SNAPSHOT]
        at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:94) [drill-java-exec-1.8.0-SNAPSHOT.jar:1.8.0-SNAPSHOT]
        at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:978) [drill-java-exec-1.8.0-SNAPSHOT.jar:1.8.0-SNAPSHOT]
        at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:257) [drill-java-exec-1.8.0-SNAPSHOT.jar:1.8.0-SNAPSHOT]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_101]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_101]
        at java.lang.Thread.run(Thread.java:745) [na:1.7.0_101]
Caused by: org.apache.calcite.runtime.CalciteContextException: At line 1, column 8: Expression 'allTypsUniq.parquet.*' is not being grouped
        at sun.reflect.GeneratedConstructorAccessor96.newInstance(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_101]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_101]
        at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:765) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:753) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3958) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:110) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:39) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:274) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:229) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:3468) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3002) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:884) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:870) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:210) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:844) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:558) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.drill.exec.planner.sql.SqlConverter.validate(SqlConverter.java:155) [drill-java-exec-1.8.0-SNAPSHOT.jar:1.8.0-SNAPSHOT]
        ... 9 common frames omitted
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Expression 'allTypsUniq.parquet.*' is not being grouped
        at sun.reflect.GeneratedConstructorAccessor95.newInstance(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_101]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_101]
        at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514) ~[calcite-core-1.4.0-drill-r14.jar:1.4.0-drill-r14]
        ... 26 common frames omitted

      Attachments

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

          People

            Unassigned Unassigned
            khfaraaz Khurram Faraaz
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Slack

                Issue deployment