[jira] [Updated] (DRILL-2232) Flatten functionality not well defined when we use flatten in an order by without projecting it

["Chris Westin (JIRA)" <ji...@apache.org>]

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

Chris Westin updated DRILL-2232:
--------------------------------
    Fix Version/s:     (was: 0.9.0)
                   1.0.0

> Flatten functionality not well defined when we use flatten in an order by without projecting it
> -----------------------------------------------------------------------------------------------
>
>                 Key: DRILL-2232
>                 URL: https://issues.apache.org/jira/browse/DRILL-2232
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Execution - Relational Operators
>            Reporter: Rahul Challapalli
>            Assignee: Jason Altekruse
>            Priority: Critical
>             Fix For: 1.0.0
>
>
> git.commit.id.abbrev=3d863b5
> Data Set :
> {code}
> {
>   "id" : 1,
>   "lst" : [1,2,3,4]
> }
> {code}
> The below query returns 4 rows instead of 1. The expected behavior in this case is not documented properly
> {code}
> select id from `data.json` where 2 in (select flatten(lst) from `data.json`) order by flatten(lst);
> +------------+
> |     id     |
> +------------+
> | 1          |
> | 1          |
> | 1          |
> | 1          |
> +------------+
> {code}
> The below projects a flatten. 
> {code}
> 0: jdbc:drill:schema=dfs_eea> select id, flatten(lst) from `temp.json` where 2 in (select flatten(lst) from `temp.json`) order by flatten(lst);
> +------------+------------+
> |     id     |   EXPR$1   |
> +------------+------------+
> | 1          | 1          |
> | 1          | 2          |
> | 1          | 3          |
> | 1          | 4          |
> +------------+------------+
> {code}
> We can agree on one of the 3 possibilites when flatten is not projected:
> 1. Irrespective of whether flatten is in the select list or not, we would still return more records based on flatten in the order by
> 2. Flatten in the order by clause does not change the no of records we return
> 3. Using flatten in an order by (or probably group by) is not supported
> Whatever we agree on, we should document it more clearly. Let me know your thoughts



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)