[
https://issues.apache.org/jira/browse/IMPALA-5022?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
hexianqing reassigned IMPALA-5022:
----------------------------------
Assignee: hexianqing
> 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: hexianqing
> 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-unsubscr...@impala.apache.org
For additional commands, e-mail: issues-all-h...@impala.apache.org
