Обсуждение: left join bug?

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 
----454334
(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 | 3443 |   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

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

Got it --- was the proverbial one-line fix --- thanks for the report!
        regards, tom lane