[
https://issues.apache.org/jira/browse/IMPALA-5022?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltán Borók-Nagy updated IMPALA-5022:
--------------------------------------
Description:
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 right outer join, while a null filtering condition on the left side
converts it to a left 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.
was:
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.
> 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 right outer join, while a null filtering condition on the left side
> converts it to a left 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.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
