[
https://issues.apache.org/jira/browse/FLINK-40975?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18128873#comment-18128873
]
Bonnie Varghese commented on FLINK-40975:
-----------------------------------------
Thanks for reporting this [~hanyuzheng]
> 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
> Priority: Major
>
> 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)
