MySQL Bugs: #27880: Bug with LEFT JOIN and TEMPORARY Tables

Bug #27880	Bug with LEFT JOIN and TEMPORARY Tables
Submitted:	17 Apr 2007 12:37	Modified:	17 Apr 2007 21:08
Reporter:	Martijn Korse	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.37	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	join, left join, table, temporary, temporary table

Description:
When joining a normal table on a temporary table, mysql claims the column from the temporary table doesn't exist in some cases.

How to repeat:
/* creating the tables */
DROP TABLE IF EXISTS test1;
DROP TABLE IF EXISTS test2;
DROP TEMPORARY TABLE IF EXISTS test3;

CREATE TABLE test1 (
	memberID INT(10) unsigned NOT NULL auto_increment,
	name VARCHAR(255),
	PRIMARY KEY (memberID)
);

CREATE TABLE test2 (
	countryID INT(10) unsigned NOT NULL auto_increment,
	countryName VARCHAR(255),
	PRIMARY KEY (countryID)
);

CREATE TEMPORARY TABLE test3 (
	logID INT(10) unsigned NOT NULL auto_increment,
	ownerID INT(10),
	countryID INT(10),
	PRIMARY KEY (logID)
);
/* filling them with some data */
INSERT INTO test1 VALUES (1,'marty'), (2,'hugo');
INSERT INTO test2 VALUES (1,'NL'), (2,'SE');
INSERT INTO test3 VALUES (NULL,1,1), (NULL,1,2), (NULL,2,2), (NULL,2,NULL);

/* end of creating tables */

This query doesn't work:

SELECT
	t3.*, t2.countryName
FROM test3 AS t3, test1 AS t1
	LEFT JOIN test2 AS t2 ON t2.countryID=t3.countryID
WHERE t3.ownerID=t1.memberID;

It will generate the error:

Unknown column `t3.countryID' in 'on clause'
----------------------------

It seems however that these queries will work:

- same amount of tables, but not a left join:
SELECT
	t3.*, t2.countryName
FROM test3 AS t3, test1 AS t1, test2 AS t2
WHERE t3.ownerID=t1.memberID AND t3.countryID=t2.countryID

- a left join, but with an extra table (and therefore, without WHERE clause)
SELECT
 t3.*, t2.countryName
FROM test3 AS t3
	LEFT JOIN test2 AS t2 ON t2.countryID=t3.countryID

Suggested fix:
I've tested it in 4.x versions and the first query (the one that fails in 5.0.27) works in all of those versions.

Although this bug:
http://bugs.mysql.com/bug.php?id=5080
is marked as FIXED, maybe it's related?

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.37, and inform about the results.

Ok, i downloaded and installed the windows binary so i could check it on my desktop computer and i still get the same error.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You JOIN tables t1 and t2, but reference to table t3. Please read about JOIN syntax at http://dev.mysql.com/doc/refman/5.0/en/join.html

"You JOIN tables t1 and t2"

if with JOIN you mean INNER JOIN as in the WHERE, you are mistaken. In the FROM clause i specify:
FROM test3 AS t3, test1 AS t1
so, test3 and test1. These tables i also specify in my WHERE clause:
WHERE t3.ownerID=t1.memberID;
Perfectly sound mysql code

Apart from that i do a LEFT JOIN, where i join test2 on test3
Also perfectly sound mysql code.

The fact that the exact same code runs smoothly on any 4.x server i can find (4 in total) also suggest this clearly is a bug.

Please read chapter "Join Processing Changes in MySQL 5.0.12" from the page link to I provided in previous comment. Look special attention to paragraph which starts from "Previously, the comma operator (,) and JOIN both had the same precedence"