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

["Jinfeng Ni (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-3018?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14537567#comment-14537567 ] 

Jinfeng Ni edited comment on DRILL-3018 at 5/11/15 5:01 AM:
------------------------------------------------------------

Here is the reason for CanNotPlan for this query, after switching to the new join planner:

Under the lopt join planner, the left join between j2 and j7 is changed to  j7 right join with j2. This is because j7 has 7680 rows, while j2 has only 10 rows. For aggregate, we use 10% to estimate the distinct row count, which is larger than 10, that's why this new join order. 

Since NestLoopJoin in physical planning only support inner join / left join, hence CanNotPlan exception.

Under the existing join planner, even through j7's AGG's row count is larger than j2, since we do not enable SwapJoin in logical planning, hence the existing join planner never will consider such join order.

I'm working on a fix for this issue. 

 


was (Author: jni):
Here is the reason for CanNotPlan for this query, after switching to the new join planner:

Under the lopt join planner, the left join between j2 and j7 is changed to  j7 right join with j2. This is because j7 has 7680 rows, while j2 has only 10 rows. For aggregate, we use 10% to estimate the distinct row count, which is larger then 10, that's why this new join order. 

Since NestLoopJoin in physical planning only support inner join / left join, hence CanNotPlan exception.

Under the existing join planner, even through j7's AGG's row count is larger than j2, since we do not enable SwapJoin in logical planning, hence the existing join planner never will consider such join order.

I'm working on a fix for this issue. 

 

> Queries with scalar aggregate  and non equality (non correlated) fail to plan
> -----------------------------------------------------------------------------
>
>                 Key: DRILL-3018
>                 URL: https://issues.apache.org/jira/browse/DRILL-3018
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>    Affects Versions: 1.0.0
>            Reporter: Victoria Markman
>            Assignee: Jinfeng Ni
>             Fix For: 1.0.0
>
>         Attachments: tables.tar
>
>
> 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:
> {code}
> 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 
> {code}
> 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.
> {code}
> -- 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 );
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)