The following bug has been logged online:
Bug reference: 2869
Logged by: Pablo Giancarelli
Email address: pgiancarelli@gmail.com
PostgreSQL version: 8.2
Operating system: Ubuntu 6.10 Server
Description: COALESCE in criteria and multiple joins behavior
Details:
select version();
PostgreSQL 8.2.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20060928 (prerelease) (Ubuntu 4.1.1-13ubuntu5)
-- How to reproduce situation
create table rel_null (idnull integer,nullfield varchar(1));
create table rel_head (id integer, descripcion varchar(20));
create table rel_det (id integer,iddet integer,idnull integer);
insert into rel_null values (1000,'N');
insert into rel_head values (10,'one');
insert into rel_det values (10,1,1000);
select rh.*,rd.iddet from rel_head rh
left join rel_det rd on rh.id = rd.id
left join rel_null rn on rd.idnull = rn.idnull
where COALESCE(rn.nullfield,'S') = 'S';
-- Result with : set join_collapse_limit to 8;
id;description;iddet
10;"one"; (null)
(1 row)
-- Result with : set join_collapse_limit to 1;
id;description;iddet
(0 rows) (this is the one I expected)
Thanks!
Pablo Giancarelli