MySQL Forums
Forum List  »  Optimizer & Parser

LEFT JOIN Issue in 5.0
Posted by: Martin Dubuc
Date: April 10, 2006 10:28AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
LEFT JOIN Issue in 5.0
3654
April 10, 2006 10:28AM
1944
April 10, 2006 10:37AM
1891
April 10, 2006 11:38AM
1988
April 10, 2006 12:50PM
2029
April 10, 2006 03:11PM
1838
April 10, 2006 03:24PM
1828
April 11, 2006 07:34AM
1927
April 11, 2006 07:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.