diff options
author | Tom Lane <tgl@sss.pgh.pa.us> | 2023-02-07 18:26:16 -0500 |
---|---|---|
committer | Tom Lane <tgl@sss.pgh.pa.us> | 2023-02-07 18:26:16 -0500 |
commit | fee7b77b9000f35e445de9954a8cbf241f181e60 (patch) | |
tree | 8d575411ac4540dc31eb440cb9c99710b1f4357c /src/test/regress/expected | |
parent | e2c78e7ab4b6056ceb79415fddcf126868d028f5 (diff) | |
download | postgresql-fee7b77b9000f35e445de9954a8cbf241f181e60.tar.gz |
Rethink nullingrel marking rules in build_joinrel_tlist().
The logic for when to add the current outer join's own relid
to the nullingrels sets of output Vars and PHVs was overly
complicated and underly correct. Not sure why I didn't think
of this before, but since what we want is marking per the
syntactic structure, we can just consult our records about
the syntactic structure, ie syn_righthand/syn_lefthand.
Also, tighten the rule about when to add the commute_above_r
bits, in hopes of eliminating some squishy reasoning. I do not
know of a reason to think that that's broken as-is, but this way
seems better.
Per bug #17781 from Robins Tharakan.
Discussion: https://postgr.es/m/17781-c0405c8b3cd5e072@postgresql.org
Diffstat (limited to 'src/test/regress/expected')
-rw-r--r-- | src/test/regress/expected/join.out | 44 |
1 files changed, 44 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index eea8978fad..18b5e8f750 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -5008,6 +5008,50 @@ select a1.id from Seq Scan on a a1 (1 row) +-- another example (bug #17781) +explain (costs off) +select ss1.f1 +from int4_tbl as t1 + left join (int4_tbl as t2 + right join int4_tbl as t3 on null + left join (int4_tbl as t4 + right join int8_tbl as t5 on null) + on t2.f1 = t4.f1 + left join ((select null as f1 from int4_tbl as t6) as ss1 + inner join int8_tbl as t7 on null) + on t5.q1 = t7.q2) + on false; + QUERY PLAN +-------------------------------- + Nested Loop Left Join + Join Filter: false + -> Seq Scan on int4_tbl t1 + -> Result + One-Time Filter: false +(5 rows) + +-- variant with Var rather than PHV coming from t6 +explain (costs off) +select ss1.f1 +from int4_tbl as t1 + left join (int4_tbl as t2 + right join int4_tbl as t3 on null + left join (int4_tbl as t4 + right join int8_tbl as t5 on null) + on t2.f1 = t4.f1 + left join ((select f1 from int4_tbl as t6) as ss1 + inner join int8_tbl as t7 on null) + on t5.q1 = t7.q2) + on false; + QUERY PLAN +-------------------------------- + Nested Loop Left Join + Join Filter: false + -> Seq Scan on int4_tbl t1 + -> Result + One-Time Filter: false +(5 rows) + -- check that join removal works for a left join when joining a subquery -- that is guaranteed to be unique by its GROUP BY clause explain (costs off) |