[DRILL-2411] Scalar SUM/AVG over empty result set returns no rows instead of NULL

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 1.0.0
• Component/s: Execution - Relational Operators
• Assignee: Mehant Baid
• Reporter: Victoria Markman
• Created: 09/Mar/15 23:10
• Updated: 05/May/15 18:05
• Resolved: 27/Apr/15 20:51

Description

Queries below should return NULL:

0: jdbc:drill:schema=dfs> select sum(a2) from t2 where 1=0;
+------------+
|   EXPR$0   |
+------------+
+------------+
No rows selected (0.08 seconds)

0: jdbc:drill:schema=dfs> select avg(a2) from t2 where 1=0;
+------------+
|   EXPR$0   |
+------------+
+------------+
No rows selected (0.074 seconds)

When grouped, result is correct:

0: jdbc:drill:schema=dfs> select a2, sum(a2) from t2 where 1=0 group by a2;
+------------+------------+
|     a2     |   EXPR$1   |
+------------+------------+
+------------+------------+
No rows selected (0.11 seconds)

I'm not convinced and it is not very intuitive that correct result should be NULL, but this is what postgres returns and Aman thinks NULL is the correct behavior

Issue Links

• is duplicated by: DRILL-2277 COUNT(*) should return 0 instead of an empty result set when there are no records