drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khurram Faraaz (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-4810) CalciteContextException - SELECT star, window function
Date Wed, 27 Jul 2016 14:02:20 GMT
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)

Mime
View raw message