Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-2748

Filter is not pushed down into subquery with the group by

    Details

    • 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
    • Labels:
      None

      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`]]])
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                jni Jinfeng Ni
                Reporter:
                vicky Victoria Markman
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: