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

["Rahul Challapalli (JIRA)" <ji...@apache.org>]

Rahul Challapalli created DRILL-2232:
----------------------------------------

             Summary: 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: Functions - Drill
            Reporter: Rahul Challapalli
            Assignee: Jason Altekruse
            Priority: Critical


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)