diff options
| -rw-r--r-- | src/test/regress/expected/subselect.out | 14 | ||||
| -rw-r--r-- | src/test/regress/sql/subselect.sql | 13 |
2 files changed, 15 insertions, 12 deletions
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 29b11f11aad..9eecdc1e924 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1977,7 +1977,7 @@ select * from x for update; Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.f3 (2 rows) --- Pull-up the direct-correlated ANY_SUBLINK +-- Pull up direct-correlated ANY_SUBLINKs explain (costs off) select * from tenk1 A where hundred in (select hundred from tenk2 B where B.odd = A.odd); QUERY PLAN @@ -2009,7 +2009,7 @@ WHERE c.odd = b.odd)); (8 rows) -- we should only try to pull up the sublink into RHS of a left join --- but a.hundred is not avaiable. +-- but a.hundred is not available. explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); @@ -2026,7 +2026,7 @@ ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); (8 rows) -- we should only try to pull up the sublink into RHS of a left join --- but a.odd is not avaiable for this. +-- but a.odd is not available for this. explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd); @@ -2042,7 +2042,7 @@ ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd); Filter: (odd = a.odd) (8 rows) --- should be able to pull up since all the references is available +-- should be able to pull up since all the references are available. explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); @@ -2063,7 +2063,8 @@ ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); -- we can pull up the sublink into the inner JoinExpr. explain (costs off) SELECT * FROM tenk1 A INNER JOIN tenk2 B -ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); +ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd) +WHERE a.thousand < 750; QUERY PLAN ------------------------------------------------- Hash Join @@ -2071,13 +2072,14 @@ ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); -> Hash Join Hash Cond: (a.hundred = c.hundred) -> Seq Scan on tenk1 a + Filter: (thousand < 750) -> Hash -> HashAggregate Group Key: c.odd, c.hundred -> Seq Scan on tenk2 c -> Hash -> Seq Scan on tenk2 b -(11 rows) +(12 rows) -- we can pull up the aggregate sublink into RHS of a left join. explain (costs off) diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 7c42ebc36fc..75a9b718b2f 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -983,7 +983,7 @@ explain (verbose, costs off) with x as (select * from subselect_tbl) select * from x for update; --- Pull-up the direct-correlated ANY_SUBLINK +-- Pull up direct-correlated ANY_SUBLINKs explain (costs off) select * from tenk1 A where hundred in (select hundred from tenk2 B where B.odd = A.odd); @@ -994,18 +994,18 @@ where A.hundred in (select C.hundred FROM tenk2 C WHERE c.odd = b.odd)); -- we should only try to pull up the sublink into RHS of a left join --- but a.hundred is not avaiable. +-- but a.hundred is not available. explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); -- we should only try to pull up the sublink into RHS of a left join --- but a.odd is not avaiable for this. +-- but a.odd is not available for this. explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd); --- should be able to pull up since all the references is available +-- should be able to pull up since all the references are available. explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); @@ -1013,9 +1013,10 @@ ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); -- we can pull up the sublink into the inner JoinExpr. explain (costs off) SELECT * FROM tenk1 A INNER JOIN tenk2 B -ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd); +ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd) +WHERE a.thousand < 750; -- we can pull up the aggregate sublink into RHS of a left join. explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B -ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
\ No newline at end of file +ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd); |