Обсуждение: Bug #795: null resulting from left join corrupts select

karsten merkle (karsten.merkle@artundweise.de) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
null resulting from left join corrupts select

Long Description
Version:
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96

Error:
equal in where clause fails.

Condition:
having two tables, the second is referencing the first
table test1 :
 test1_id
----------
        1
        2

and table test2:
 test2_id | test1_id
----------+----------
        1 |        1


I will get the correct result without a left join:

select
 *
from
 test1, test2
where
 test1.test1_id = test2.test2_id;

 test1_id | test2_id | test1_id
----------+----------+----------
        1 |        1 |        1


but using a left join the where clause won't work:
select
 *
from
 test1 left join test2 on (test1.test1_id = test2.test1_id)
where
 test1.test1_id = test2.test1_id;

 test1_id | test2_id | test1_id
----------+----------+----------
        1 |        1 |        1
        2 |          |

Expectation:
the test2.test1_id '' doesn't look like test1.test1_id '2' !
I would have expected the same result as it was given by the statement before.


I would be lucky to support you
(according to my abilities)
--karsten merkle

Sample Code
CREATE TABLE test1 (
test1_id INTEGER ,
PRIMARY KEY (test1_id)
);

CREATE TABLE test2 (
test2_id INTEGER ,
test1_id INTEGER ,
PRIMARY KEY (test1_id)
);

insert into test1 values (1);
insert into test1 values (2);

insert into test2 values (1,1);

select
 *
from
 test1, test2
where
 test1.test1_id = test2.test2_id;

select
 *
from
 test1 left join test2 on (test1.test1_id = test2.test1_id)
where
 test1.test1_id = test2.test1_id;



No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> select
>  *
> from
>  test1 left join test2 on (test1.test1_id = test2.test1_id)
> where
>  test1.test1_id = test2.test1_id;

Hm.  I think what is happening is that the planner is (mistakenly)
deciding that the where-clause and on-clause are equivalent, and
so dropping the second one ...

            regards, tom lane

pgsql-bugs@postgresql.org writes:
> select
>  *
> from
>  test1 left join test2 on (test1.test1_id = test2.test1_id)
> where
>  test1.test1_id = test2.test1_id;

>  test1_id | test2_id | test1_id
> ----------+----------+----------
>         1 |        1 |        1
>         2 |          |

This is fixed for 7.3.  If you need a fix now, the diff should work in
7.2 as well ... but I haven't tried it.  See
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/optimizer/util/relnode.c.diff?r1=1.39&r2=1.40&f=c

            regards, tom lane