[jira] [Commented] (IMPALA-7996) Inefficient plan for pathological ON FALSE clause

["ASF subversion and git services (Jira)" <ji...@apache.org>]

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

ASF subversion and git services commented on IMPALA-7996:
---------------------------------------------------------

Commit 59ef46a6ca0dc6867924005436e79352992e7159 in impala's branch refs/heads/master from Aleksandr Efimov
[ https://gitbox.apache.org/repos/asf?p=impala.git;h=59ef46a6c ]

IMPALA-7996: Optimize constant-false outer joins

Constant FALSE or NULL ON conjuncts eliminate usable equi-join
predicates during expression rewriting. The classic planner then
chooses a nested-loop join although the join cannot produce a match.

Add a synthetic non-matching hash predicate for outer joins and drop
redundant remaining ON conjuncts. The predicate is what keeps this a
hash join: left where the analyzer put it, among the other conjuncts,
the FALSE leaves the planner choosing a nested loop join, and the
constant buys nothing. Replace the non-preserved input of
left and right outer joins with EmptySetNode.

Broadcast the empty build for constant-false left outer joins, even
with a SHUFFLE hint. Invert right outer joins so their empty input also
becomes the broadcast build. Keep full outer joins partitioned and use
random distribution on both inputs to avoid constant-key skew.

Add planner coverage for single-node and distributed plans with
literal, folded, and typed NULL predicates. Add query tests with
constant inline views to verify preserved-side row counts.

Change-Id: I6505825fc958720eec12e13d546661834f221b80
Assisted-by: gpt-5.6-sol (OpenAI Codex)
Reviewed-on: http://gerrit.cloudera.org:8080/24597
Reviewed-by: Impala Public Jenkins <im...@cloudera.com>
Tested-by: Impala Public Jenkins <im...@cloudera.com>


> Inefficient plan for pathological ON FALSE clause
> -------------------------------------------------
>
>                 Key: IMPALA-7996
>                 URL: https://issues.apache.org/jira/browse/IMPALA-7996
>             Project: IMPALA
>          Issue Type: Bug
>          Components: Frontend
>    Affects Versions: Impala 3.1.0
>            Reporter: Paul Rogers
>            Assignee: Aleksandr Efimov
>            Priority: Minor
>
> Bugs in the rewrite code prevented the ON clause from being subject to the full set of rewrite rules. See IMPALA-7997.
> Once that bug was fixed, it uncovered an inefficient plan for the pathological case of {{ON FALSE}}.
> {code:sql}
> # Constant conjunct in the ON-clause of an outer join is
> # assigned to the join.
> select *
> from functional.alltypessmall a
> right outer join functional.alltypestiny b
> on (a.id = b.id and !true)
> {code}
> Note the ON clause and its actual meaning:
> {noformat}
> a.id = b.id and !true --> a.id = b.id AND FALSE --> FALSE
> {noformat}
> (Note that a test should be added for the straight-up FALSE condition.)
> Once rewrites are applied applied, (or, presumably, if we have a test for {{ON FALSE}}), we get a new plan, which is actually worse:
> {noformat}
> PLAN-ROOT SINK
> |
> 02:NESTED LOOP JOIN [RIGHT OUTER JOIN]
> |  join predicates: FALSE
> |
> |--01:SCAN HDFS [functional.alltypestiny b]
> |     partitions=4/4 files=4 size=460B
> |
> 00:SCAN HDFS [functional.alltypessmall a]
>    partitions=4/4 files=4 size=6.32KB
> {noformat}
> This says to loop over both tables, which is a very bad plan. The correct plan is to reduce the query to an empty result set.
> Similar case:
> {code:sql}
> # Constant conjunct in the ON-clause of an outer join is
> # assigned to the join.
> select *
> from functional.alltypessmall a
> left outer join functional.alltypestiny b
> on (a.id = b.id and 1 + 1 > 10)
> {code}
> Reasoning:
> {noformat}
> a.id = b.id and 1 + 1 > 10 --> a.id = b.id AND 2 > 10
>    --> a.id = b.id AND FALSE --> FALSE
> {noformat}
> The plan after performing above rewrites, which is also an unnecessary nested loop join:
> {noformat}
> PLAN-ROOT SINK
> |
> 02:NESTED LOOP JOIN [LEFT OUTER JOIN]
> |  join predicates: FALSE
> |
> |--01:SCAN HDFS [functional.alltypestiny b]
> |     partitions=4/4 files=4 size=460B
> |
> 00:SCAN HDFS [functional.alltypessmall a]
>    partitions=4/4 files=4 size=6.32KB
> {noformat}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org