Re: LEFT JOIN Issue in 5.0

[Martin Dubuc]

Here is a more complete database schema and some data to go with it.

Given the following schema and data input:

CREATE TABLE testjoin1 (
id int(11) unsigned NOT NULL,
sid int(11) unsigned NOT NULL,
PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE testjoin2 (
id int(11) unsigned NOT NULL,
join2_data int(11) unsigned NOT NULL,
PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE testjoin3 (
sid int(11) unsigned NOT NULL,
join3_data int(11) unsigned NOT NULL,
PRIMARY KEY (sid)
) TYPE=InnoDB;

INSERT INTO testjoin1 VALUES (1, 2);
INSERT INTO testjoin1 VALUES (2, 3);
INSERT INTO testjoin1 VALUES (3, 3);

INSERT INTO testjoin2 VALUES (1, 11);
INSERT INTO testjoin2 VALUES (2, 22);

INSERT INTO testjoin3 VALUES (2, 32);
INSERT INTO testjoin3 VALUES (3, 33);

I would like to perform the following query:

SELECT testjoin1.id, testjoin1.sid, testjoin2.*, testjoin3.* FROM testjoin1, testjoin3 LEFT JOIN testjoin2 ON testjoin1.id=testjoin2.id WHERE testjoin1.sid=testjoin3.sid;

MySQL 5.0 will complain if such statement is used. MySQL 4.1 will not.

MySQL 4.1 returns:
+----+-----+-----+------------+------+------------+
| id | sid | sid | join3_data | id | join2_data |
+----+-----+-----+------------+------+------------+
| 1 | 2 | 2 | 32 | NULL | NULL |
| 2 | 3 | 3 | 33 | 2 | 22 |
+----+-----+-----+------------+------+------------+