[DRILL-2748] Filter is not pushed down into subquery with the group by

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.9.0, 1.0.0, 1.1.0
• Fix Version/s: 1.2.0
• Component/s: Query Planning & Optimization
• Assignee: Jinfeng Ni
• Reporter: Victoria Markman
• Created: 10/Apr/15 17:28
• Updated: 23/Sep/15 15:05
• Resolved: 22/Sep/15 17:41

Description

I'm not sure about this one, theoretically filter could have been pushed into the subquery.

0: jdbc:drill:schema=dfs> explain plan for select x, y, z from (select a1, b1, avg(a1) from t1 group by a1, b1) as sq(x, y, z) where x = 10;
+------------+------------+
|    text    |    json    |
+------------+------------+
| 00-00    Screen
00-01      Project(x=[$0], y=[$1], z=[$2])
00-02        Project(x=[$0], y=[$1], z=[CAST(/(CastHigh(CASE(=($3, 0), null, $2)), $3)):ANY NOT NULL])
00-03          SelectionVectorRemover
00-04            Filter(condition=[=($0, 10)])
00-05              HashAgg(group=[{0, 1}], agg#0=[$SUM0($0)], agg#1=[COUNT($0)])
00-06                Project(a1=[$1], b1=[$0])
00-07                  Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:/drill/testdata/predicates/t1]], selectionRoot=/drill/testdata/predicates/t1, numFiles=1, columns=[`a1`, `b1`]]])

Same with distinct in subquery:

0: jdbc:drill:schema=dfs> explain plan for select x, y, z from ( select distinct a1, b1, c1 from t1 ) as sq(x, y, z) where x = 10;
+------------+------------+
|    text    |    json    |
+------------+------------+
| 00-00    Screen
00-01      Project(x=[$0], y=[$1], z=[$2])
00-02        Project(x=[$0], y=[$1], z=[$2])
00-03          SelectionVectorRemover
00-04            Filter(condition=[=($0, 10)])
00-05              HashAgg(group=[{0, 1, 2}])
00-06                Project(a1=[$2], b1=[$1], c1=[$0])
00-07                  Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:/drill/testdata/predicates/t1]], selectionRoot=/drill/testdata/predicates/t1, numFiles=1, columns=[`a1`, `b1`, `c1`]]])

Issue Links

• duplicates: DRILL-3716 Drill should push filter past aggregate in order to improve query performance.