Обсуждение: BUG #2869: COALESCE in criteria and multiple joins behavior

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

"Pablo Giancarelli" <pgiancarelli@gmail.com> writes:
> 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)

I can't reproduce this in CVS HEAD; I think it is already fixed by this
change:

2006-12-07 14:33  tgl

    * src/backend/optimizer/plan/: initsplan.c (REL8_2_STABLE),
    initsplan.c: Repair incorrect placement of WHERE clauses when there
    are multiple, rearrangeable outer joins and the WHERE clause is
    non-strict and mentions only nullable-side relations.  New bug in
    8.2, caused by new logic to allow rearranging outer joins.  Per bug
    #2807 from Ross Cohen; thanks to Jeff Davis for producing a usable
    test case.

            regards, tom lane