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/bugs?unsub=gcdmb-bugs@m.gmane.org

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=3Dperson_address1.address_id)
>ON person.id=3Dperson_address1.person_id AND (address1.addrtype =3D 'home')
>
>LEFT JOIN
> (person_address person_address2 INNER JOIN address address2
> ON address2.id=3Dperson_address2.address_id)
>ON person.id=3Dperson_address2.person_id AND (address2.addrtype =3D=
'billing')
>
>LEFT JOIN
> (person_address person_address3 INNER JOIN address address3
> ON address3.id=3Dperson_address3.address_id)
>ON person.id=3Dperson_address3.person_id AND (address3.addrtype =3D 'post')
>
>
>--
>MySQL Bugs Mailing List
>For list archives: http://lists.mysql.com/bugs
>To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dmiguel@mysql.com

Regards,

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

Miguel Angel Sol=F3rzano
S=E3o Paulo - Brazil



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org