[DRILL-2376] UNION ALL on Aggregates with GROUP BY returns incomplete results

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.9.0
• Fix Version/s: 1.0.0
• Component/s: Query Planning & Optimization
• Assignee: Sean Hsuan-Yi Chu
• Reporter: Abhishek Girish
• Created: 04/Mar/15 03:39
• Updated: 14/May/15 18:45
• Resolved: 07/May/15 22:47

Description

The following query returns incomplete results:

select x
from
(SELECT Sum(ss_ext_sales_price) x
FROM  store_sales
UNION ALL
SELECT Sum(cs_ext_sales_price) x
FROM catalog_sales) tmp
GROUP BY x;

Results from Drill:
+------------+
|     x      |
+------------+
| 3658019159.35 |
+------------+
1 row selected (3.474 seconds)

Results from Postgres:
       x       
---------------
 5265207074.51
 3658019159.35
(2 rows)

Removing GROUP BY returns the right results:

select x
from
(SELECT Sum(ss_ext_sales_price) x
FROM  store_sales
UNION ALL
SELECT Sum(cs_ext_sales_price) x
FROM catalog_sales) tmp;

Results from Drill:
+------------+
|     x      |
+------------+
| 5265207074.51 |
| 3658019159.35 |
+------------+

Issue Links

• depends upon: DRILL-2234 IOOB when streaming aggregate is on the left side of hash join
• depends upon: DRILL-2207 Fix Union All Operator
• is depended upon by: DRILL-2377 UNION ALL on Aggregates with GROUP BY returns null when column aliases don't match
• is depended upon by: DRILL-2378 UNION ALL on Aggregates with GROUP BY and ORDER BY fails when column aliases don't match
• relates to: DRILL-1977 Wrong result with aggregation on top of UNION ALL operator
• relates to: DRILL-2380 TPC-DS Query 33 and simplified variants return wrong results
• relates to: DRILL-2377 UNION ALL on Aggregates with GROUP BY returns null when column aliases don't match
• relates to: DRILL-2378 UNION ALL on Aggregates with GROUP BY and ORDER BY fails when column aliases don't match
• relates to: DRILL-2379 UNION ALL with ORDER BY fails when column aliases don't match