Re: Incorrect result set for nested joins

At 13:08 18/3/2004, Marczisovszky Daniel wrote:
Hi,
>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 got the same result like you. Just now I am open a bug
report in our bug database.

Thank you for the report.

>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]

Regards,

For technical support contracts, visit https://order.mysql.com/
Are you MySQL certified?, http://www.mysql.com/certification/

Miguel Angel Solórzano <[email protected]>
São Paulo - Brazil



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