Incorrect result set for nested joins

Hi all,

I have a person and an address table, joined with a link table.
Address may contain different type of addresses according to the
addrtype field. I'd like to see a person with his/her different type
of addresses in one record.

I've tested this query with both MySQL 4.1.1 and MySQL 5.0.0 and they
resulted the same (wrong) result set:

id      id      id      id
1       NULL    2       NULL
1       NULL    NULL    NULL
1       NULL    2       3
1       NULL    NULL    3

I've also tested exactly the same query with Firebird, Postgres and
Oracle and it works find on those DBs. The correct result should be:

id      id      id      id
1       NULL    2       3


Best wishes,
Daniel Marczisovszky

SQL script:

DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS address;
DROP TABLE IF EXISTS person_address;

CREATE TABLE person (id INT, name VARCHAR(50));
CREATE TABLE address (id INT, street VARCHAR(50), addrtype VARCHAR(50));
CREATE TABLE person_address (person_id INT, address_id INT);

INSERT INTO person VALUES (1, 'john the ripper');
INSERT INTO address VALUES (2, 'street 1', 'billing');
INSERT INTO address VALUES (3, 'street 2', 'post');
INSERT INTO person_address VALUES (1, 2);
INSERT INTO person_address VALUES (1, 3);

SELECT DISTINCT person.id, address1.id, address2.id, address3.id FROM person
LEFT JOIN 
    (person_address person_address1 INNER JOIN address address1 
     ON address1.id=person_address1.address_id)
ON person.id=person_address1.person_id AND (address1.addrtype = 'home')

LEFT JOIN 
    (person_address person_address2 INNER JOIN address address2 
     ON address2.id=person_address2.address_id)
ON person.id=person_address2.person_id AND (address2.addrtype = 'billing')

LEFT JOIN 
    (person_address person_address3 INNER JOIN address address3 
     ON address3.id=person_address3.address_id)
ON person.id=person_address3.person_id AND (address3.addrtype = 'post')


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]