hi, there!

test=# create table a(id int primary key);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for
table 'a'
CREATE
test=# create table b(id int references a);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
test=# insert into a values(45);
INSERT 34924 1
test=# insert into a values(43);
INSERT 34925 1
test=# insert into a values(34);
INSERT 34926 1
test=# select a.id, b.id from a left join b using(id);
 id | id 
----+----
 43 |   
 45 |   
(2 rows)

test=# select * from a;
 id 
----
 45
 43
 34
(3 rows)

test=# select * from b;
 id 
----
(0 rows)

test=# insert into b values(34);
INSERT 34927 1
test=# select a.id, b.id from a left join b using(id);
 id | id 
----+----
 34 | 34
 43 |   
 45 |   
(3 rows)

test=# 
 
lark:~$psql --version
psql (PostgreSQL) 7.1beta1
contains readline, history, multibyte support
[...]
lark:~$uname -a
FreeBSD xxx 4.2-STABLE FreeBSD 4.2-STABLE #0: Wed Dec
6 17:16:57 NOVT 2000 xxx:/usr/obj/usr/src/sys/alf i386

sorry, if it has already been fixed

/fjoe


Wed, 13 Dec 2000 18:45:55 +0600 (NS)
Max Khon <fjoe@iclub.nsu.ru>
left join bug?
Max Khon <fjoe@iclub.nsu.ru> writes:
> test=# select a.id, b.id from a left join b using(id);
>  id | id 
> ----+----
>  43 |   
>  45 |   
> (2 rows)

> test=# select * from a;
>  id 
> ----
>  45
>  43
>  34
> (3 rows)

Ugh.  It looks like mergejoin must be mishandling the first left-side
item when the right side is empty.  Will take a look...

			regards, tom lane

Wed, 13 Dec 2000 11:37:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: left join bug? 
Got it --- was the proverbial one-line fix --- thanks for the report!

			regards, tom lane

Wed, 13 Dec 2000 18:47:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: left join bug? 