From 0ebc922bb3741f670b13c7ca725a747d33ed814f Mon Sep 17 00:00:00 2001 From: zachzqshi Date: Tue, 25 Aug 2026 20:17:36 +0800 Subject: [PATCH] Fix hypergraph dropping LATERAL dependency on materialized derived table access paths In the hypergraph optimizer a correlated LATERAL derived table that is materialized (derived_merge=off) was mis-evaluated: an index/ref access into the materialized table derived parameter_tables only from the key match, so the table's lateral dependency on its outer tables was lost. The optimizer could then hash-join the derived table (built once) against its lateral dependency instead of re-driving it per outer row, producing empty/stale results. Set parameter_tables |= node.lateral_dependencies() at the single per-node choke point ApplyPredicatesForBaseTable() instead of per-proposer, so every single-table access method (table scan, ref, index, range, index-merge, skip-scan) carries the lateral dependency consistently. No-op for non-lateral nodes. --- .../r/hypergraph_lateral_derived_mat.result | 49 +++++++++++++++++++ .../t/hypergraph_lateral_derived_mat.test | 49 +++++++++++++++++++ sql/join_optimizer/join_optimizer.cc | 18 ++++++- 3 files changed, 115 insertions(+), 1 deletion(-) create mode 100644 mysql-test/r/hypergraph_lateral_derived_mat.result create mode 100644 mysql-test/t/hypergraph_lateral_derived_mat.test diff --git a/mysql-test/r/hypergraph_lateral_derived_mat.result b/mysql-test/r/hypergraph_lateral_derived_mat.result new file mode 100644 index 00000000000..548b5e9d084 --- /dev/null +++ b/mysql-test/r/hypergraph_lateral_derived_mat.result @@ -0,0 +1,49 @@ +CREATE TABLE g_t9 (x INT); +CREATE TABLE g_t0 (x INT); +CREATE TABLE g_t1 (a INT); +CREATE TABLE g_t2 (a INT); +INSERT INTO g_t9 VALUES (1),(2); +INSERT INTO g_t0 VALUES (1),(3); +INSERT INTO g_t1 VALUES (10),(20); +INSERT INTO g_t2 VALUES (10),(30); +CREATE TABLE f0 (a INT, x INT); +CREATE TABLE f1 (a INT); +CREATE TABLE f2 (a INT, x INT, y INT); +CREATE TABLE f3 (y INT); +INSERT INTO f0 VALUES (1,100),(2,200); +INSERT INTO f1 VALUES (1),(2); +INSERT INTO f2 VALUES (1,100,7),(2,200,8),(3,300,9); +INSERT INTO f3 VALUES (7),(8); +SET @save_optimizer_switch = @@optimizer_switch; +SET optimizer_switch='derived_merge=off'; +# Form G (correlation references only outer tables g_t9,g_t0) +SELECT g_t9.x, g_t0.x, g_t1.a, dt.a +FROM g_t9 JOIN g_t0 ON TRUE +LEFT JOIN ( g_t1 LEFT JOIN LATERAL +( SELECT g_t2.a FROM g_t2 WHERE g_t9.x = g_t0.x ) AS dt +ON dt.a = g_t1.a ) ON TRUE +ORDER BY 1,2,3,4; +x x a a +1 1 10 10 +1 1 20 NULL +1 3 10 NULL +1 3 20 NULL +2 1 10 NULL +2 1 20 NULL +2 3 10 NULL +2 3 20 NULL +# Form F (correlation f2.x=f0.x on the inner LEFT JOIN ON inside dt) +SELECT f0.a, f0.x, f1.a, dt.a, dt.yy +FROM f0 LEFT JOIN +( f1 LEFT JOIN LATERAL +( SELECT f2.a, f3.y AS yy FROM f2 LEFT JOIN f3 +ON f2.y = f3.y AND f2.x = f0.x ) AS dt +ON dt.a = f1.a ) ON TRUE +ORDER BY 1,2,3,4,5; +a x a a yy +1 100 1 1 7 +1 100 2 2 NULL +2 200 1 1 NULL +2 200 2 2 8 +DROP TABLE g_t9,g_t0,g_t1,g_t2,f0,f1,f2,f3; +SET optimizer_switch = @save_optimizer_switch; diff --git a/mysql-test/t/hypergraph_lateral_derived_mat.test b/mysql-test/t/hypergraph_lateral_derived_mat.test new file mode 100644 index 00000000000..3c537ec9877 --- /dev/null +++ b/mysql-test/t/hypergraph_lateral_derived_mat.test @@ -0,0 +1,49 @@ +--source include/have_hypergraph.inc + +# Bug: hypergraph optimizer mis-evaluates a correlated LATERAL derived table +# when it is materialized (derived_merge=off). A ref/index access into the +# materialized derived table dropped the table's lateral dependency from +# parameter_tables, letting the optimizer materialize it once (or hash-join it +# against its lateral dependency) instead of re-driving it per outer row. + +CREATE TABLE g_t9 (x INT); +CREATE TABLE g_t0 (x INT); +CREATE TABLE g_t1 (a INT); +CREATE TABLE g_t2 (a INT); +INSERT INTO g_t9 VALUES (1),(2); +INSERT INTO g_t0 VALUES (1),(3); +INSERT INTO g_t1 VALUES (10),(20); +INSERT INTO g_t2 VALUES (10),(30); + +CREATE TABLE f0 (a INT, x INT); +CREATE TABLE f1 (a INT); +CREATE TABLE f2 (a INT, x INT, y INT); +CREATE TABLE f3 (y INT); +INSERT INTO f0 VALUES (1,100),(2,200); +INSERT INTO f1 VALUES (1),(2); +INSERT INTO f2 VALUES (1,100,7),(2,200,8),(3,300,9); +INSERT INTO f3 VALUES (7),(8); + +SET @save_optimizer_switch = @@optimizer_switch; +SET optimizer_switch='derived_merge=off'; + +--echo # Form G (correlation references only outer tables g_t9,g_t0) +SELECT g_t9.x, g_t0.x, g_t1.a, dt.a +FROM g_t9 JOIN g_t0 ON TRUE +LEFT JOIN ( g_t1 LEFT JOIN LATERAL + ( SELECT g_t2.a FROM g_t2 WHERE g_t9.x = g_t0.x ) AS dt + ON dt.a = g_t1.a ) ON TRUE +ORDER BY 1,2,3,4; + +--echo # Form F (correlation f2.x=f0.x on the inner LEFT JOIN ON inside dt) +SELECT f0.a, f0.x, f1.a, dt.a, dt.yy +FROM f0 LEFT JOIN + ( f1 LEFT JOIN LATERAL + ( SELECT f2.a, f3.y AS yy FROM f2 LEFT JOIN f3 + ON f2.y = f3.y AND f2.x = f0.x ) AS dt + ON dt.a = f1.a ) ON TRUE +ORDER BY 1,2,3,4,5; + +DROP TABLE g_t9,g_t0,g_t1,g_t2,f0,f1,f2,f3; +SET optimizer_switch = @save_optimizer_switch; +--source include/disable_hypergraph.inc diff --git a/sql/join_optimizer/join_optimizer.cc b/sql/join_optimizer/join_optimizer.cc index 9147fc56863..385c6bb16a4 100644 --- a/sql/join_optimizer/join_optimizer.cc +++ b/sql/join_optimizer/join_optimizer.cc @@ -4351,7 +4351,6 @@ void CostingReceiver::ProposeAccessPathForBaseTable( &new_fd_set); path->ordering_state = m_orderings->ApplyFDs(path->ordering_state, new_fd_set); - path->parameter_tables |= m_graph->nodes[node_idx].lateral_dependencies(); ProposeAccessPathWithOrderings( TableBitmap(node_idx), new_fd_set, /*obsolete_orderings=*/0, path, materialize_subqueries ? "mat. subq" : description_for_trace); @@ -4427,6 +4426,23 @@ void CostingReceiver::ApplyPredicatesForBaseTable( double materialize_cost = 0.0; const NodeMap my_map = TableBitmap(node_idx); + + // A node may carry lateral dependencies on outer tables it references: a + // LATERAL derived table / table function (via Query_expression::m_lateral_deps), + // or even a regular table whose in-subtree join conditions reference + // out-of-subtree tables (see FindLateralDependencies()). Those dependencies + // must be reflected in parameter_tables for EVERY access path of the node, so + // the join enumerator re-drives the node per outer row (and AllowHashJoin() + // forbids hashing it against its lateral dependency). This is the single + // choke point shared by all single-table access proposers (table scan, ref, + // index, range, index-merge, skip-scan); applying it here keeps every access + // method consistent. Without it, e.g. an index/ref or range access into a + // materialized correlated LATERAL derived table would drop the dependency + // (its parameter_tables come only from the key/range match) and the table + // could be wrongly materialized once - producing stale or empty results for + // correlated LATERAL derived tables with derived_merge=off. + path->parameter_tables |= m_graph->nodes[node_idx].lateral_dependencies(); + set_count_examined_rows(path, true); path->set_num_output_rows(path->num_output_rows_before_filter); path->set_cost(path->cost_before_filter()); -- 2.35.1