Zoltán Borók-Nagy created IMPALA-15439:
------------------------------------------
Summary: Outer join simplification treats a tuple as NULL-rejected
when a conjunct only rejects NULLs for a group of tuples
Key: IMPALA-15439
URL: https://issues.apache.org/jira/browse/IMPALA-15439
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) can produce wrong results. Two helpers check whether a conjunct
rejects
NULLs for a whole group of tuples, but then mark every single tuple of the
group as
NULL-rejected:
* gatherNullRejectingTids(), used for RIGHT and FULL OUTER JOINs, checks each
conjunct
with isNullableConjunct(e, tupleIds), where tupleIds is the whole left side of
the join.
It then adds every tuple whose conjunct list contains the conjunct.
* getNullRejectingOjTidsFromIjOnClause(), used for the ON clause of a later
inner join,
checks with isNullableConjunct(e, <all left tuples>). It then adds every
outer-joined
tuple that the conjunct references.
A conjunct like {{(t1.x = 1 OR t2.y = 1)}} or {{t3.id = t1.id + coalesce(t2.id,
0)}} only
proves that t1 and t2 cannot both be NULL. It does not prove that t2 is
non-NULL. When t2
comes from an earlier LEFT OUTER JOIN, that join is then turned into an inner
join, or t2
loses its outer-joined status and predicates get evaluated below the join.
This only happens when ENABLE_OUTER_JOIN_TO_INNER_TRANSFORMATION is enabled,
which is not
the default. It blocks IMPALA-15430.
h3. Repro
All queries use functional.alltypestiny. "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 t1 left join
functional.alltypestiny t2 on t1.id = t2.id + 1 right join
functional.alltypestiny t3 on t1.id = t3.id where (t1.int_col = 1 or t2.int_col
= 1) and t2.bool_col is null}}|0|4|
|same with {{full join}} in place of {{right join}}|0|4|
|{{select count(*) from functional.alltypestiny t1 left join
functional.alltypestiny t2 on t1.id = t2.id + 100 join functional.alltypestiny
t3 on (t3.id = t1.id or t3.id = t2.id)}}|8|0|
|{{select count(*) from functional.alltypestiny t1 left join
functional.alltypestiny t2 on t1.id = t2.id + 100 join functional.alltypestiny
t3 on t3.id = t1.id + coalesce(t2.id, 0)}}|8|0|
In the first query, the OR conjunct marks t2 as NULL-rejected, and
{{t2.bool_col IS NULL}}
is pushed into the t2 scan below the t1-t2 outer join:
{noformat}
03:HASH JOIN [LEFT OUTER JOIN]
| hash predicates: t1.id = t2.id + 1
| other predicates: (t1.int_col = 1 OR t2.int_col = 1)
|--01:SCAN HDFS [functional.alltypestiny t2]
| predicates: t2.bool_col IS NULL
{noformat}
In the third query, the ON clause of the t3 join turns the t1-t2 LEFT OUTER
JOIN into
an INNER JOIN:
{noformat}
03:HASH JOIN [INNER JOIN]
| hash predicates: t1.id = t2.id + 100
{noformat}
h3. Proposed fix
Credit a tuple only if the conjunct rejects NULLs for that tuple alone, i.e.
check
{{!isNullableConjunct(e, id.asList())}} and {{!isTrueWithNullSlots(e)}} per
tuple. Keep
the group check only for deciding whether the RIGHT or FULL OUTER JOIN itself
can be
converted. 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)
