From: | pgsql-bugs(at)postgresql(dot)org |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Bug #795: null resulting from left join corrupts select |
Date: | 2002-10-10 13:02:14 |
Message-ID: | 20021010130214.3AA944764DE@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
karsten merkle (karsten(dot)merkle(at)artundweise(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2002-10-10 14:18:16 | Re: user name lost from dictionary |
Previous Message | Elielson Fontanezi | 2002-10-10 11:28:17 | user name lost from dictionary |