diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2023-02-10 13:31:00 -0500 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2023-02-10 13:31:00 -0500 |
| commit | acc5821e4dcb6b7df6ad1f806459f95fcaebadfc (patch) | |
| tree | a1731087a18e864b46ffc3972d850af832b9eff0 /src/test/regress/expected/join.out | |
| parent | f8ba1bf4e48369450cdc71b5bf12d8f7bdaf10f3 (diff) | |
| download | postgresql-acc5821e4dcb6b7df6ad1f806459f95fcaebadfc.tar.gz | |
Further fixes in qual nullingrel adjustment for outer join commutation.
One of the add_nulling_relids calls in deconstruct_distribute_oj_quals
added an OJ relid to too few Vars, while the other added it to too
many. We should consider the syntactic structure not
min_left/righthand while deciding which Vars to decorate, and when
considering pushing up a lower outer join pursuant to transforming the
second form of OJ identity 3 to the first form, we only want to
decorate Vars coming from its LHS.
In a related bug, I realized that make_outerjoininfo was failing to
check a very basic property that's needed to apply OJ identity 3:
the syntactically-upper outer join clause can't refer to the lower
join's LHS. This didn't break the join order restriction logic,
but it led to setting bogus commute_xxx bits, possibly resulting
in bogus nullingrel markings in modified quals.
Richard Guo and Tom Lane
Discussion: https://postgr.es/m/CAMbWs497CmBruMx1SOjepWEz+T5NWa4scqbdE9v7ZzSXqH_gQw@mail.gmail.com
Discussion: https://postgr.es/m/CAEP4nAx9C5gXNBfEA0JBfz7B+5f1Bawt-RWQWyhev-wdps8BZA@mail.gmail.com
Diffstat (limited to 'src/test/regress/expected/join.out')
| -rw-r--r-- | src/test/regress/expected/join.out | 25 |
1 files changed, 25 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 75f03fcf30..98d611412d 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -5068,6 +5068,31 @@ where current_user is not null; -- this is to add a Result node -> Seq Scan on int4_tbl i4 (6 rows) +-- and further discussion of bug #17781 +explain (costs off) +select * +from int8_tbl t1 + left join (int8_tbl t2 left join onek t3 on t2.q1 > t3.unique1) + on t1.q2 = t2.q2 + left join onek t4 + on t2.q2 < t3.unique2; + QUERY PLAN +------------------------------------------------- + Nested Loop Left Join + Join Filter: (t2.q2 < t3.unique2) + -> Nested Loop Left Join + Join Filter: (t2.q1 > t3.unique1) + -> Hash Left Join + Hash Cond: (t1.q2 = t2.q2) + -> Seq Scan on int8_tbl t1 + -> Hash + -> Seq Scan on int8_tbl t2 + -> Materialize + -> Seq Scan on onek t3 + -> Materialize + -> Seq Scan on onek t4 +(13 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) |