Hanyu Zheng created FLINK-40975:
-----------------------------------
Summary: FlinkBushyJoinReorderRule drops or misplaces join
conditions, producing wrong results
Key: FLINK-40975
URL: https://issues.apache.org/jira/browse/FLINK-40975
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Reporter: Hanyu Zheng
In batch mode with \{{table.optimizer.join-reorder-enabled = true}},
FlinkBushyJoinReorderRule can drop a join condition, or apply it in the wrong
place. The query then silently returns wrong results. The bushy rule handles
every join with at most \{{table.optimizer.bushy-join-reorder-threshold}}
inputs (12 by default), so this affects most batch join queries once join
reorder is enabled.
h3. Reproduction
{code:sql}
SET 'execution.runtime-mode' = 'batch';
SET 'table.optimizer.join-reorder-enabled' = 'true';
WITH d AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS
t(n))
SELECT d1.n + 10 * d2.n + 100 * d3.n, * FROM d d1, d d2, d d3
WHERE d1.n + 10 * d2.n + 100 * d3.n = 1;
{code}
Expected: 1 row. Actual: all 1000 rows. The WHERE filter is dropped and both
joins are planned with \{{where=[true]}}. With join reorder disabled, the
result is correct.
h3. Root cause
{\{getJoinConditions}} only hands a join filter to a join when every input it
references is on the left or right side of that join. A filter that references
3 or more inputs can never be satisfied by a 2-way join. No pairwise plans are
built, so the rule falls through to \{{addCrossJoinToTop}}, which joins the
remaining inputs with \{{ON true}}. \{{createTopProject}} only re-applies the
multi-join's post-join filter, so the unconsumed join filter is lost.
h3. Related cases in the same rule
All of the following also return wrong results with join reorder enabled:
# An outer join added last loses its ON condition:
{code:sql}
SELECT * FROM T1 JOIN T3 ON a1 = a3 LEFT JOIN T2 ON a1 = a2 LEFT JOIN T4 ON a2
= a4; -- second LEFT JOIN becomes ON true
SELECT * FROM T1 FULL JOIN T2 ON b1 > 10;
-- becomes an INNER join ON true
{code}
# Only part of an outer join's ON condition is applied, even with 2 tables:
{code:sql}
SELECT * FROM T1 LEFT JOIN T2 ON a1 = a2 AND b1 > 10; -- b1 > 10 is dropped
{code}
# An inner join condition on the null-generating side of an outer join ends up
in the outer join's ON clause, so rows it should remove are kept with NULLs:
{code:sql}
SELECT * FROM T1 LEFT JOIN T2 ON a1 = a2 JOIN T3 ON a1 = a3 AND b2 > b3;
{code}
h3. Workaround
Disable \{{table.optimizer.join-reorder-enabled}}, or rewrite the filter into
pairwise join conditions.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
