You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Khurram Faraaz (JIRA)" <ji...@apache.org> on 2016/07/27 14:02:20 UTC
[jira] [Created] (DRILL-4810) CalciteContextException - SELECT
star, window function
Khurram Faraaz created DRILL-4810:
-------------------------------------
Summary: CalciteContextException - SELECT star, window function
Key: DRILL-4810
URL: https://issues.apache.org/jira/browse/DRILL-4810
Project: Apache Drill
Issue Type: Bug
Components: Query Planning & Optimization
Affects Versions: 1.8.0
Reporter: Khurram Faraaz
This is a problem seen with regular window functions (not specific to nested aggregates).
{noformat}
SELECT *, <window-function> OVER...
does not work on Drill
{noformat}
Same query works on Postgres and on same data.
{noformat}
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
...
{noformat}
Drill throws error
{noformat}
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)
{noformat}
Upon expanding star in the project Drill returns results
{noformat}
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 |
...
{noformat}
Stack trace from drillbit.log
{noformat}
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
{noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)