diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/join.out | 51 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 18 |
2 files changed, 69 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index c0c72833339..e5dceff7658 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2667,6 +2667,57 @@ select * from int4_tbl a full join int4_tbl b on false; (10 rows) -- +-- test for ability to use a cartesian join when necessary +-- +explain (costs off) +select * from + tenk1 join int4_tbl on f1 = twothousand, + int4(sin(1)) q1, + int4(sin(0)) q2 +where q1 = thousand or q2 = thousand; + QUERY PLAN +----------------------------------------------------------------------------- + Hash Join + Hash Cond: (tenk1.twothousand = int4_tbl.f1) + -> Nested Loop + Join Filter: ((q1.q1 = tenk1.thousand) OR (q2.q2 = tenk1.thousand)) + -> Nested Loop + -> Function Scan on q1 + -> Function Scan on q2 + -> Bitmap Heap Scan on tenk1 + Recheck Cond: ((q1.q1 = thousand) OR (q2.q2 = thousand)) + -> BitmapOr + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (q1.q1 = thousand) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (q2.q2 = thousand) + -> Hash + -> Seq Scan on int4_tbl +(16 rows) + +explain (costs off) +select * from + tenk1 join int4_tbl on f1 = twothousand, + int4(sin(1)) q1, + int4(sin(0)) q2 +where thousand = (q1 + q2); + QUERY PLAN +-------------------------------------------------------------- + Hash Join + Hash Cond: (tenk1.twothousand = int4_tbl.f1) + -> Nested Loop + -> Nested Loop + -> Function Scan on q1 + -> Function Scan on q2 + -> Bitmap Heap Scan on tenk1 + Recheck Cond: (thousand = (q1.q1 + q2.q2)) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (thousand = (q1.q1 + q2.q2)) + -> Hash + -> Seq Scan on int4_tbl +(12 rows) + +-- -- test join removal -- begin; diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 2d53cf1725b..bc9b7812cf8 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -690,6 +690,24 @@ select * from int4_tbl a full join int4_tbl b on true; select * from int4_tbl a full join int4_tbl b on false; -- +-- test for ability to use a cartesian join when necessary +-- + +explain (costs off) +select * from + tenk1 join int4_tbl on f1 = twothousand, + int4(sin(1)) q1, + int4(sin(0)) q2 +where q1 = thousand or q2 = thousand; + +explain (costs off) +select * from + tenk1 join int4_tbl on f1 = twothousand, + int4(sin(1)) q1, + int4(sin(0)) q2 +where thousand = (q1 + q2); + +-- -- test join removal -- |