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. tables.tar
          47 kB
          Victoria Markman
        2. 0001-DRILL-3018-Fix-CanNotPlan-for-nestLoop-left-join-cau.patch
          6 kB
          Jinfeng Ni

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: