Zoltán Borók-Nagy created IMPALA-15440:
------------------------------------------
Summary: Outer join simplification leaves stale or removes needed
outer-join bookkeeping, so predicates are evaluated below outer joins
Key: IMPALA-15440
URL: https://issues.apache.org/jira/browse/IMPALA-15440
Project: IMPALA
Issue Type: Bug
Components: Frontend
Reporter: Zoltán Borók-Nagy
With ENABLE_OUTER_JOIN_TO_INNER_TRANSFORMATION=true,
Analyzer.simplifyOuterJoins()
(IMPALA-5022) updates the outer-join bookkeeping incorrectly after it converts
a join. The
bookkeeping consists of outerJoinedTupleIds, fullOuterJoinedTupleIds and
fullOuterJoinedConjuncts. Predicates then get assigned below outer joins that
are still
in the plan, and the query returns wrong results.
These maps only remember the last outer join that null-extends a tuple:
TableRef.analyzeJoin()
overwrites the entry for every tuple on the nullable side. simplifyOuterJoins()
then
patches the maps one tuple at a time, which goes wrong in two ways:
# *FULL -> LEFT OUTER JOIN leaves stale entries.*
removeFullOuterJoinedTupleIdsAndConjuncts(ids) drops the full-outer-join guard
for every
left-side tuple. But removeOuterJoinedTupleIds(nullRejectingTids) only un-marks
the
tuples that were NULL-rejected by name. The other left-side tuples still map to
the
converted join, although a LEFT OUTER JOIN does not null-extend its left side.
So
canEvalOnClauseConjunct() lets an ON-clause predicate on such a tuple be
evaluated in its
scan, which filters the preserved side.
# *The inner-join ON-clause path removes entries of later joins.*
When simplifyOuterJoinsByIjOnClause() converts an earlier LEFT OUTER JOIN,
removeOuterJoinedTupleIds() removes the tuple's entry. At that point the entry
may point
at a later RIGHT or FULL OUTER JOIN, which still null-extends the tuple.
removeFullOuterJoinedTupleIdsAndConjuncts() can likewise delete the conjunct
guards of a
later FULL OUTER JOIN.
This only happens when ENABLE_OUTER_JOIN_TO_INNER_TRANSFORMATION is enabled,
which is not
the default. It blocks IMPALA-15430.
h3. Repro
"off" and "on" are the results with the option set to false and to true. The
"off"
results are correct.
||Query||off||on||
|{{select count(*) from functional.alltypestiny a join functional.alltypestiny
b on a.id = b.id full join functional.alltypestiny c on b.id = c.id and
b.int_col = 1 where a.int_col >= 0}}|8|4|
|{{select count(t2.id) from functional.alltypestiny t1 full join
functional.alltypestiny t2 on t1.id = t2.id full join functional.alltypestiny
t3 on t2.id = t3.id and t2.int_col = 1 where t1.int_col >= 0}}|8|4|
|{{select count(*) from functional.alltypestiny t1 left join
functional.alltypestiny t2 on t1.id = t2.id join functional.alltypestiny t4 on
t2.id = t4.id right join functional.alltypessmall t3 on t4.id = t3.id where
t2.int_col is null}}|92|100|
|{{select count(*) from functional.alltypestiny t1 full join
functional.alltypestiny t2 on t1.id = t2.id join functional.alltypestiny t4 on
t1.id = t4.id full join functional.alltypestiny t3 on t4.id = t3.id and
t3.int_col = 1}}|12|8|
First query: the FULL OUTER JOIN is converted, and its ON-clause predicate on
the
preserved side ends up in the b scan:
{noformat}
04:HASH JOIN [RIGHT OUTER JOIN]
| hash predicates: c.id = b.id
...
01:SCAN HDFS [functional.alltypestiny b]
predicates: b.int_col = 1
{noformat}
Third query: {{t2.int_col IS NULL}} must be evaluated after the RIGHT OUTER
JOIN with
t3. Instead it is pushed into the t2 scan.
Fourth query: the last join stays a FULL OUTER JOIN, but its ON-clause predicate
{{t3.int_col = 1}} is pushed into the t3 scan.
h3. Proposed fix
Don't patch the maps tuple by tuple. After simplifyOuterJoins() has processed a
block,
rebuild outerJoinedTupleIds, fullOuterJoinedTupleIds and
fullOuterJoinedConjuncts for the
block's table refs by replaying the final join operators in order. That is the
registration logic of TableRef.analyzeJoin() plus
registerFullOuterJoinedConjunct().
Add the queries above to QueryTest/outer-to-inner-joins.test, which runs with
the option
both off and on.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
