LEFT JOIN Issue in 5.0

[Martin Dubuc]

I am trying to migrate an application to use MySQL 5.0 (from 4.1). I expected this would be seemless, but to my surprise it seems that version 5.0 does not handle some of the SQL queries the same as 4.1.

Here is a case. Given the following set of tables:

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

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

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

If I run the following query on 5.0.19:
SELECT * FROM testjoin1, testjoin3 LEFT JOIN testjoin2 ON testjoin1.id=testjoin2.id;
I get the following error:
ERROR 1054 (42S22): Unknown column 'testjoin1.id' in 'on clause'

If I run the same query in version 4.1.14, MySQL server processes the request and returns result.

I narrowed the problem to the presence of testjoin3 between the FROM and the LEFT JOIN, but I don't know why MySQL version 5.0 does not allowed this.

I need to be able to pull in some data from tables other than the ones involved in the join to get complete data set (using a where clause in my query to pull in the right data form the other tables).

Why is the same request processed in 4.1.14 and rejected in 5.0.19? A bug?

Martin