diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/join.out | 24 | ||||
| -rw-r--r-- | src/test/regress/expected/join_1.out | 24 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 22 |
3 files changed, 70 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 3b7bdd9cbb..3dd4f5ed7d 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2266,3 +2266,27 @@ select * from tt5,tt6 where tt5.f1 = tt6.f1 and tt5.f1 = tt5.f2 - tt6.f2; 1 | 10 | 1 | 9 (1 row) +-- +-- regression test for problems of the sort depicted in bug #3588 +-- +create temp table xx (pkxx int); +create temp table yy (pkyy int, pkxx int); +insert into xx values (1); +insert into xx values (2); +insert into xx values (3); +insert into yy values (101, 1); +insert into yy values (201, 2); +insert into yy values (301, NULL); +select yy.pkyy as yy_pkyy, yy.pkxx as yy_pkxx, yya.pkyy as yya_pkyy, + xxa.pkxx as xxa_pkxx, xxb.pkxx as xxb_pkxx +from yy + left join (SELECT * FROM yy where pkyy = 101) as yya ON yy.pkyy = yya.pkyy + left join xx xxa on yya.pkxx = xxa.pkxx + left join xx xxb on coalesce (xxa.pkxx, 1) = xxb.pkxx; + yy_pkyy | yy_pkxx | yya_pkyy | xxa_pkxx | xxb_pkxx +---------+---------+----------+----------+---------- + 101 | 1 | 101 | 1 | 1 + 201 | 2 | | | 1 + 301 | | | | 1 +(3 rows) + diff --git a/src/test/regress/expected/join_1.out b/src/test/regress/expected/join_1.out index f1bf4b9af2..9e10c73acb 100644 --- a/src/test/regress/expected/join_1.out +++ b/src/test/regress/expected/join_1.out @@ -2266,3 +2266,27 @@ select * from tt5,tt6 where tt5.f1 = tt6.f1 and tt5.f1 = tt5.f2 - tt6.f2; 1 | 10 | 1 | 9 (1 row) +-- +-- regression test for problems of the sort depicted in bug #3588 +-- +create temp table xx (pkxx int); +create temp table yy (pkyy int, pkxx int); +insert into xx values (1); +insert into xx values (2); +insert into xx values (3); +insert into yy values (101, 1); +insert into yy values (201, 2); +insert into yy values (301, NULL); +select yy.pkyy as yy_pkyy, yy.pkxx as yy_pkxx, yya.pkyy as yya_pkyy, + xxa.pkxx as xxa_pkxx, xxb.pkxx as xxb_pkxx +from yy + left join (SELECT * FROM yy where pkyy = 101) as yya ON yy.pkyy = yya.pkyy + left join xx xxa on yya.pkxx = xxa.pkxx + left join xx xxb on coalesce (xxa.pkxx, 1) = xxb.pkxx; + yy_pkyy | yy_pkxx | yya_pkyy | xxa_pkxx | xxb_pkxx +---------+---------+----------+----------+---------- + 101 | 1 | 101 | 1 | 1 + 201 | 2 | | | 1 + 301 | | | | 1 +(3 rows) + diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index ad8957cd6e..4efb3c7283 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -440,3 +440,25 @@ insert into tt6 values(1, 2); insert into tt6 values(2, 9); select * from tt5,tt6 where tt5.f1 = tt6.f1 and tt5.f1 = tt5.f2 - tt6.f2; + +-- +-- regression test for problems of the sort depicted in bug #3588 +-- + +create temp table xx (pkxx int); +create temp table yy (pkyy int, pkxx int); + +insert into xx values (1); +insert into xx values (2); +insert into xx values (3); + +insert into yy values (101, 1); +insert into yy values (201, 2); +insert into yy values (301, NULL); + +select yy.pkyy as yy_pkyy, yy.pkxx as yy_pkxx, yya.pkyy as yya_pkyy, + xxa.pkxx as xxa_pkxx, xxb.pkxx as xxb_pkxx +from yy + left join (SELECT * FROM yy where pkyy = 101) as yya ON yy.pkyy = yya.pkyy + left join xx xxa on yya.pkxx = xxa.pkxx + left join xx xxb on coalesce (xxa.pkxx, 1) = xxb.pkxx; |