[
https://issues.apache.org/jira/browse/DRILL-3255?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14577355#comment-14577355
]
Deneche A. Hakim commented on DRILL-3255:
-----------------------------------------
More specifically. Postgres documentation states:
bq. The elements of the PARTITION BY list are interpreted in much the same
fashion as elements of a GROUP BY Clause, except that they are always simple
expressions and *never the name or number of an output column*. Similarly, the
elements of the ORDER BY list are interpreted in much the same fashion as
elements of an ORDER BY Clause, except that the expressions are always taken as
simple expressions and *never the name or number of an output column*.
> Queries must fail when invalid-positions are specified in order by clause of
> a window function
> ----------------------------------------------------------------------------------------------
>
> Key: DRILL-3255
> URL: https://issues.apache.org/jira/browse/DRILL-3255
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 1.1.0
> Reporter: Abhishek Girish
> Assignee: Deneche A. Hakim
> Labels: window_function
> Fix For: 1.1.0
>
>
> Drill fails to validate invalid positions within the ORDER BY clause of a
> window function:
> {code:sql}
> > select s_city, s_store_sk, sum(s_number_employees) over (PARTITION BY
> > s_city ORDER BY 40) from store limit 10;
> +-----------+-------------+---------+
> | s_city | s_store_sk | EXPR$2 |
> +-----------+-------------+---------+
> | Fairview | 5 | 860 |
> | Fairview | 8 | 860 |
> | Fairview | 12 | 860 |
> | Midway | 1 | 2320 |
> | Midway | 2 | 2320 |
> | Midway | 3 | 2320 |
> | Midway | 4 | 2320 |
> | Midway | 6 | 2320 |
> | Midway | 7 | 2320 |
> | Midway | 9 | 2320 |
> +-----------+-------------+---------+
> 10 rows selected (0.224 seconds)
> {code}
> Drill validates the same, when the ORDER BY clause is a part of the main query
> {code:sql}
> > select s_city, s_store_sk, sum(s_number_employees) from store group by
> > s_city, s_store_sk ORDER BY 40 limit 10;
> Error: PARSE ERROR: From line 1, column 101 to line 1, column 102: Ordinal
> out of range
> [Error Id: 0458fba1-afd6-48cf-89ba-1e4c107d2426 on abhi8.qa.lab:31010]
> (state=,code=0)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
