[DRILL-6146] UNION with empty input on any one side returns incorrect results

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 1.12.0, 1.14.0
• Component/s: Query Planning & Optimization
• Assignee: Vitalii Diravka
• Reporter: Khurram Faraaz
• Created: 08/Feb/18 21:54
• Updated: 06/Jan/22 12:55

Description

When any one side of the UNION has an empty file as input, Drill returns incorrect results.
 
table t3 does not have any data inserted into its rows. Postgress returns 1 as the result for both the queries, whereas Drill does not.
 

postgres=# create table t3(id int, name varchar(25));
CREATE TABLE 
postgres=# select * from (values(1)) t union select id from t3;
       1
 
postgres=# select id from t3 union select * from (values(1)) t;
  1
 

 
 
Results from Drill 1.12.0-mapr, note we return result 1 as result after the union.
We have a directory named empty_JSON_f , and it has a single empty JSON file (that JSON file has no content in it, it is empty).
 

0: jdbc:drill:schema=dfs.tmp> select * from (values(1)) UNION select id from empty_JSON_f;
+---------+
| EXPR$0  |
+---------+
| 1       |
+---------+
1 row selected (2.272 seconds)

However, in this query we return null and loose the value 1 from the right hand side, after the union, this doesn't seem correct 

0: jdbc:drill:schema=dfs.tmp> select id from empty_JSON_f UNION select * from (values(1));
+-------+
|  id   |
+-------+
| null  |
+-------+
1 row selected (0.33 seconds)