[jira] [Commented] (IMPALA-5022) Outer join simplification

["Aman Sinha (Jira)" <ji...@apache.org>]

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

Aman Sinha commented on IMPALA-5022:
------------------------------------

[~xqhe] quick update on the part 1 patch..it is in the process of additional functional testing.  There were delays in merging the functionality to a testable branch. I will update once things look good.

> Outer join simplification
> -------------------------
>
>                 Key: IMPALA-5022
>                 URL: https://issues.apache.org/jira/browse/IMPALA-5022
>             Project: IMPALA
>          Issue Type: Improvement
>          Components: Frontend
>    Affects Versions: Impala 2.9.0
>            Reporter: Greg Rahn
>            Assignee: Xianqing He
>            Priority: Major
>              Labels: planner, tpc-ds
>
> As a general rule, an outer join can be converted to an inner join if there is a condition on the inner table that filters out non‑matching rows. In a left outer join, the right table is the inner table, while it is the left table in a right outer join. In a full outer join, both tables are inner tables. Conditions that are FALSE for nulls are referred to as null filtering conditions, and these are the conditions that enable the outer‑to‑inner join conversion to be made.
> An outer join can be converted to an inner join if at least one of the following conditions is true.
> * The WHERE clause contains at least one null filtering condition on the inner table.
> * The outer join is involved in another join, and the other join condition has one or more null filtering conditions on the inner table. The other join in this case can be an inner join, left outer join, or right outer join. It cannot be a full outer join because there is no inner table in this case.
> A null filtering condition on the right side of a full outer join converts it to a left outer join, while a null filtering condition on the left side converts it to a right outer join.
> For example the following query
> {noformat}
> select t1.c1, t2.c1
> from t1 left outer join t2 using (x)
> where t2.c2 > 5
> {noformat}
> can safely be converted to
> {noformat}
> select t1.c1, t2.c1
> from t1 join t2 using (x)
> where t2.c2 > 5
> {noformat}
> because the predicate {{t2.c2 > 5}} is interpreted as FALSE if {{t2.c2}} is NULL and therefore the condition removes all non‑matching rows of the outer join.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

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