The following bug has been logged online:
Bug reference: 3588
Logged by: Richard Harris
Email address: richard_haris@adp.com
PostgreSQL version: 8.2.4
Operating system: Linux
Description: coalesce not working in join
Details:
/*
The queries, query1 and query2, below are identical except that query1 has a
'left join' where query2 has a 'join'. Both queries return three rows.
However query2 (with the 'join') returns all non-null values in column
t1b_pkt1 where query 1 with the left join returns some null values.
This is the behavior when the queries are run on PG 8.2.4. When the queries
are run on PG 8.0.3, both queries return the same results as query2 with the
'join'.
Is PG 8.2.4 behavior correct?
*/
-- Create and populate tables
create table t1 (pkt1 int);
create table t2 (pkt2 int, pkt1 int);
insert into t1 (pkt1) values (1);
insert into t1 (pkt1) values (2);
insert into t1 (pkt1) values (3);
insert into t2 (pkt2, pkt1) values (101, 1);
insert into t2 (pkt2, pkt1) values (201, 2);
insert into t2 (pkt2, pkt1) values (301, NULL);
-- query1
select t2.pkt2 as t2_pkt2, t2.pkt1 as t2_pkt1, t2a.pkt2 as t2a_pkt2,
t1a.pkt1 as t1a_pkt1, t1b.pkt1 as t1b_pkt1
from t2
left join (SELECT pkt2, pkt1 FROM t2 where pkt2 = 101 ) as t2a ON t2.pkt2 =
t2a.pkt2
left join t1 t1a on t2a.pkt1 = t1a.pkt1
left join t1 t1b on coalesce (t1a.pkt1, 1) = t1b.pkt1
-- query2
select t2.pkt2 as t2_pkt2, t2.pkt1 as t2_pkt1, t2a.pkt2 as t2a_pkt2,
t1a.pkt1 as t1a_pkt1, t1b.pkt1 as t1b_pkt1
from t2
left join (SELECT pkt2, pkt1 FROM t2 where pkt2 = 101 ) as t2a ON t2.pkt2 =
t2a.pkt2
left join t1 t1a on t2a.pkt1 = t1a.pkt1
join t1 t1b on coalesce (t1a.pkt1, 1) = t1b.pkt1