diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/join.out | 49 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 21 |
2 files changed, 70 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index a7b7b73ad6f..c7b46928f2a 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2367,3 +2367,52 @@ select * from a left join b on i = x and i = y and x = i; (0 rows) rollback; +-- +-- test NULL behavior of whole-row Vars, per bug #5025 +-- +select t1.q2, count(t2.*) +from int8_tbl t1 left join int8_tbl t2 on (t1.q2 = t2.q1) +group by t1.q2 order by 1; + q2 | count +-------------------+------- + -4567890123456789 | 0 + 123 | 2 + 456 | 0 + 4567890123456789 | 6 +(4 rows) + +select t1.q2, count(t2.*) +from int8_tbl t1 left join (select * from int8_tbl) t2 on (t1.q2 = t2.q1) +group by t1.q2 order by 1; + q2 | count +-------------------+------- + -4567890123456789 | 0 + 123 | 2 + 456 | 0 + 4567890123456789 | 6 +(4 rows) + +select t1.q2, count(t2.*) +from int8_tbl t1 left join (select * from int8_tbl offset 0) t2 on (t1.q2 = t2.q1) +group by t1.q2 order by 1; + q2 | count +-------------------+------- + -4567890123456789 | 0 + 123 | 2 + 456 | 0 + 4567890123456789 | 6 +(4 rows) + +select t1.q2, count(t2.*) +from int8_tbl t1 left join + (select q1, case when q2=1 then 1 else q2 end as q2 from int8_tbl) t2 + on (t1.q2 = t2.q1) +group by t1.q2 order by 1; + q2 | count +-------------------+------- + -4567890123456789 | 0 + 123 | 2 + 456 | 0 + 4567890123456789 | 6 +(4 rows) + diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 29992ced0c2..e6ee2215f92 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -522,3 +522,24 @@ create temp table b (x integer, y integer); select * from a left join b on i = x and i = y and x = i; rollback; + +-- +-- test NULL behavior of whole-row Vars, per bug #5025 +-- +select t1.q2, count(t2.*) +from int8_tbl t1 left join int8_tbl t2 on (t1.q2 = t2.q1) +group by t1.q2 order by 1; + +select t1.q2, count(t2.*) +from int8_tbl t1 left join (select * from int8_tbl) t2 on (t1.q2 = t2.q1) +group by t1.q2 order by 1; + +select t1.q2, count(t2.*) +from int8_tbl t1 left join (select * from int8_tbl offset 0) t2 on (t1.q2 = t2.q1) +group by t1.q2 order by 1; + +select t1.q2, count(t2.*) +from int8_tbl t1 left join + (select q1, case when q2=1 then 1 else q2 end as q2 from int8_tbl) t2 + on (t1.q2 = t2.q1) +group by t1.q2 order by 1; |