From 2260c7f6d90ecf76d3806d32890a0890688b41e8 Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Tue, 29 Apr 2025 14:34:44 +0300 Subject: Fixes for ChangeVarNodes_walker() This commit fixes two bug in ChangeVarNodes_walker() function. * When considering RestrictInfo, walk down to its clauses based on the presense of relid to be deleted not just in clause_relids but also in required_relids. * Incrementally adjust num_base_rels based on the change of clause_relids instead of recalculating it using clause_relids, which could contain outer-join relids. Reported-by: Richard Guo Discussion: https://postgr.es/m/CAMbWs49PE3CvnV8vrQ0Dr%3DHqgZZmX0tdNbzVNJxqc8yg-8kDQQ%40mail.gmail.com Author: Andrei Lepikhov Reviewed-by: Alexander Korotkov --- src/test/regress/expected/join.out | 16 +++++++++++++++- src/test/regress/sql/join.sql | 8 +++++++- 2 files changed, 22 insertions(+), 2 deletions(-) (limited to 'src/test') diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index fa2c7405519..f35a0b18c37 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -7260,7 +7260,21 @@ WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code; Index Cond: (id = emp1.id) (5 rows) -INSERT INTO emp1 VALUES (1, 1), (2, 1); +-- Check that SJE correctly replaces relations in OR-clauses +EXPLAIN (COSTS OFF) +SELECT * FROM emp1 t1 + INNER JOIN emp1 t2 ON t1.id = t2.id + LEFT JOIN emp1 t3 ON t1.code = 1 AND (t2.code = t3.code OR t2.code = 1); + QUERY PLAN +--------------------------------------------------------------------------- + Nested Loop Left Join + Join Filter: ((t2.code = 1) AND ((t2.code = t3.code) OR (t2.code = 1))) + -> Seq Scan on emp1 t2 + -> Materialize + -> Seq Scan on emp1 t3 +(5 rows) + + INSERT INTO emp1 VALUES (1, 1), (2, 1); WITH t1 AS (SELECT * FROM emp1) UPDATE emp1 SET code = t1.code + 1 FROM t1 WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code; diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index d01d1da4ef8..cc5128add4d 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2807,7 +2807,13 @@ WITH t1 AS (SELECT * FROM emp1) UPDATE emp1 SET code = t1.code + 1 FROM t1 WHERE t1.id = emp1.id RETURNING emp1.id, emp1.code, t1.code; -INSERT INTO emp1 VALUES (1, 1), (2, 1); +-- Check that SJE correctly replaces relations in OR-clauses +EXPLAIN (COSTS OFF) +SELECT * FROM emp1 t1 + INNER JOIN emp1 t2 ON t1.id = t2.id + LEFT JOIN emp1 t3 ON t1.code = 1 AND (t2.code = t3.code OR t2.code = 1); + + INSERT INTO emp1 VALUES (1, 1), (2, 1); WITH t1 AS (SELECT * FROM emp1) UPDATE emp1 SET code = t1.code + 1 FROM t1 -- cgit v1.2.3