[DRILL-3018] Queries with scalar aggregate and non equality (non correlated) fail to plan

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.0.0
• Fix Version/s: 1.0.0
• Component/s: Query Planning & Optimization
• Assignee: Jinfeng Ni
• Reporter: Victoria Markman
• Created: 10/May/15 23:32
• Updated: 12/May/15 17:41
• Resolved: 11/May/15 20:54

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 );