diff options
Diffstat (limited to 'src/test/regress/expected/join.out')
| -rw-r--r-- | src/test/regress/expected/join.out | 29 |
1 files changed, 29 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 7c3c9aced20..5340dd20ba3 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2900,6 +2900,35 @@ select f1, unique2, case when unique2 is null then f1 else 0 end (1 row) -- +-- another case with equivalence clauses above outer joins (bug #8591) +-- +explain (costs off) +select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) + from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand) + where a.unique2 = 5530 and coalesce(b.twothousand, a.twothousand) = 44; + QUERY PLAN +--------------------------------------------------------------------------------------------- + Nested Loop Left Join + -> Nested Loop Left Join + Filter: (COALESCE(b.twothousand, a.twothousand) = 44) + -> Index Scan using tenk1_unique2 on tenk1 a + Index Cond: (unique2 = 5530) + -> Bitmap Heap Scan on tenk1 b + Recheck Cond: (thousand = a.unique1) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (thousand = a.unique1) + -> Index Scan using tenk1_unique2 on tenk1 c + Index Cond: ((unique2 = COALESCE(b.twothousand, a.twothousand)) AND (unique2 = 44)) +(11 rows) + +select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) + from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand) + where a.unique2 = 5530 and coalesce(b.twothousand, a.twothousand) = 44; + unique1 | unique1 | unique1 | coalesce +---------+---------+---------+---------- +(0 rows) + +-- -- test ability to push constants through outer join clauses -- explain (costs off) |