[jira] [Created] (FLINK-40975) FlinkBushyJoinReorderRule drops or misplaces join conditions, producing wrong results

["Hanyu Zheng (Jira)" <ji...@apache.org>]

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)