[DRILL-1394] COUNT(*) with UNION subquery returns two rows

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Cannot Reproduce
• Affects Version/s: 0.5.0
• Fix Version/s: 0.8.0
• Component/s: Query Planning & Optimization
• Assignee: Sean Hsuan-Yi Chu
• Reporter: Bob Rumsby
• Due: 06/Jan/15
• Created: 10/Sep/14 01:28
• Updated: 07/Jan/15 04:08
• Resolved: 05/Jan/15 17:47

Description

The following COUNT query with a UNION subquery returns two rows, one count for each side of the union. Run by itself, the subquery returns 70000 rows.

0: jdbc:drill:> select count from (select trans_id from `clicks/clicks.campaign.json` union all select trans_id from `clicks/clicks.json`);
------------

EXPR$0

------------

40000

30000

------------
2 rows selected (5.896 seconds)

0: jdbc:drill:> explain plan for select count from (select trans_id from `clicks/clicks.campaign.json` union all select trans_id from `clicks/clicks.json`);
----------------------+

text

json

----------------------+

00-00 Screen 00-01 StreamAgg(group=[{}], EXPR$0=[COUNT()]) 00-02 Project($f0=[0]) 00-03 UnionAll(all=[true]) 00-05 Project(trans_id=[$1]) 00-07 Scan(groupscan=[EasyGroupScan [selectionRoot=/mapr/demorig/data/nested/clicks/clicks.campaign.json, columns = null]]) 00-04 Project(trans_id=[$1]) 00-06 Scan(groupscan=[EasyGroupScan [selectionRoot=/mapr/demorig/data/nested/clicks/clicks.json, columns = null]])

{ "head" : Unknown macro: { "version" } , "graph" : [ { "pop" : "fs-scan", "@id" : 7, "files" : [ "maprfs:/mapr/demorig/data/nested/clicks/clicks.campaign.json" ], "storage" : { "type" : "file", "enabled" : true, "connection" : "maprfs:///", "workspaces" : Unknown macro: { "root" } , "formats" : Unknown macro: { "psv" } }, "format" : { "type" : "json" } , "selectionRoot" : "/mapr/demorig/data/nested/clicks/clicks.campaign.json", "cost" : 7876.0 }, Unknown macro: { "pop" } , { "pop" : "fs-scan", "@id" : 6, "files" : [ "maprfs:/mapr/demorig/data/nested/clicks/clicks.json" ], "storage" : { "type" : "file", "enabled" : true, "connection" : "maprfs:///", "workspaces" : Unknown macro: { "root" } , "formats" : Unknown macro: { "psv" } }, "format" : { "type" : "json" } , "selectionRoot" : "/mapr/demorig/data/nested/clicks/clicks.json", "cost" : 5097.0 }, Unknown macro: { "pop" } , { "pop" : "union-all", "@id" : 3, "children" : [ 5, 4 ], "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 12973.0 } , Unknown macro: { "pop" } , Unknown macro: { "pop" } , { "pop" : "screen", "@id" : 0, "child" : 1, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 1297.3 } ] }

----------------------+
1 row selected (0.142 seconds)