Hanyu Zheng created FLINK-40976:
-----------------------------------
Summary: JoinToMultiJoinForReorderRule applies a post-join filter
twice when merging joins for reorder
Key: FLINK-40976
URL: https://issues.apache.org/jira/browse/FLINK-40976
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Reporter: Hanyu Zheng
In batch mode with \{{table.optimizer.join-reorder-enabled = true}}, a filter
on the null-generating side of an outer join is applied a second time above
that outer join after JoinToMultiJoinForReorderRule merges the joins. Rows that
should come back with NULLs are removed. The rule runs before both reorder
strategies (bushy and LOPT), so both are affected.
h3. Reproduction
{code:sql}
SET 'execution.runtime-mode' = 'batch';
SET 'table.optimizer.join-reorder-enabled' = 'true';
SELECT * FROM T1 LEFT JOIN
(SELECT * FROM T2 LEFT JOIN T3 ON a2 = a3 WHERE COALESCE(b3, b2) > 3) v
ON a1 = a2;
{code}
Expected: every T1 row, with NULLs where v has no match. Actual: T1 rows
without a match in v are missing, because \{{COALESCE(b3, b2) > 3}} is applied
again above the outer LEFT JOIN. With join reorder disabled, the result is
correct.
h3. Workaround
Disable \{{table.optimizer.join-reorder-enabled}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
