[jira] [Closed] (DRILL-3255) Queries must fail when invalid-positions are specified in order by clause of a window function

["Deneche A. Hakim (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DRILL-3255?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Deneche A. Hakim closed DRILL-3255.
-----------------------------------
    Resolution: Invalid

Tried a similar query using Calcite and Postgres and both seem to ignore invalid positions specified in order by clause. In fact, Postgres seem to ignore any position when specified in OVER clause.

> 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)