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

Queries with scalar aggregate and non equality (non correlated) fail to plan

    XMLWordPrintableJSON

    Details

      Description

      This is a regression.
      Both queries worked and returned correct result with May 7 build
      (mapr-drill-1.0.0.31658-1.noarch.rpm)

      Running with the latest build, I got these two failures:

      select * from j2 where c_integer > (select min(c_bigint) from j7 where c_boolean is null)
      Failed with exception
      java.sql.SQLException: SYSTEM ERROR: org.apache.drill.exec.work.foreman.UnsupportedRelOperatorException: This query cannot be planned possibly due to either a cartesian join or an inequality
      
      select * from j2 where c_float < (select min(c_float) from j6 where c_boolean is null )
      Failed with exception
      java.sql.SQLException: SYSTEM ERROR: org.apache.drill.exec.work.foreman.UnsupportedRelOperatorException: This query cannot be planned possibly due to either a cartesian join or an inequality join 
      

      The common pattern between these queries is that function MIN is running over no rows, in both queries there is no correlation to the outer table.

      -- Non correlated
      -- Greater than
      -- MIN returns NULL
      select * from j2 where c_integer > (select min(c_bigint) from j7 where c_boolean is null);
      
      -- Non correlated
      -- Less than
      -- MIN returns NULL
      select * from j2 where c_float < (select min(c_float) from j6 where c_boolean is null );
      

        Attachments

        1. 0001-DRILL-3018-Fix-CanNotPlan-for-nestLoop-left-join-cau.patch
          6 kB
          Jinfeng Ni
        2. tables.tar
          47 kB
          Victoria Markman

          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: