[DRILL-2228] Projecting '*' returns all nulls when we have flatten in a filter and order by

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Later
• Fix Version/s: 1.0.0
• Component/s: Execution - Relational Operators
• Assignee: Jason Altekruse
• Reporter: Rahul Kumar Challapalli
• Created: 12/Feb/15 20:32
• Updated: 05/May/15 00:37
• Resolved: 05/May/15 00:37

Description

git.commit.id.abbrev=3d863b5

The below query returns all nulls currently :

0: jdbc:drill:schema=dfs_eea> select * from `data.json` where 2 in (select flatten(lst_lst[1]) from `data.json`) order by flatten(lst_lst[1]);
+------------+
|     *      |
+------------+
| null       |
| null       |
| null       |
| null       |
| null       |
| null       |
| null       |
| null       |
| null       |
| null       |
+------------+

There seems to be another issue here since the no of records returned also does not look right. I will raise a separate JIRA for that.

The issue goes away, if we do an order by without the flatten. Below query works

select * from `data.json` where 2 in (select flatten(lst_lst[1]) from `data.json`) order by uid;

Attached the data files

Issue Links

• is related to: DRILL-2232 Flatten functionality not well defined when we use flatten in an order by without projecting it
• is related to: DRILL-2012 Flatten fails when we filter by a non-projected column
• is related to: DRILL-2180 Star is not expanded when being used with flatten
• relates to: DRILL-2181 Throw proper error message when flatten is used within an 'order by' or 'group by'