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

Bug #5080	Bug with LEFT JOIN and TEMPORARY Tables
Submitted:	18 Aug 2004 1:30	Modified:	20 Aug 2004 22:43
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.3-beta	OS:	Linux (Debian Linux 2.6.7)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
It seems that simple SELECT statements are ignoring TEMPORARY tables, and retriving information from the non-temporary table with the same name.

How to repeat:
CREATE TABLE a (a int);
INSERT INTO a VALUES (1), (2), (3);
CREATE TEMPORARY TABLE a (a int);
DELETE FROM  a;
INSERT INTO a VALUES (4), (5), (6);
SELECT * FROM a;

result will show 1,2,3 and not 4,5,6.

Hi,

I'm not sure what it going on.  I can't repeat this on linux ( 2.4.21 #12 SMP ) or windows 2000.
I get 4,5,6 back when using 4.1.3-beta.

Are you using our binaries?  I'm going to try and find a machine with a 2.6 kernel on it so I can 
make sure it's not that.

Best Regards

I have downloaded the binary distribution about three weeks ago. Server is fresh installion - Last debian ISO plus some patches. Don't know exactly what, sysadmin did them (I can't ask, if you think it's relevant).

Also I have moved the files around in the system to their more "correct" locations (i.e /usr/lib, /usr/include etc) this was done three weeks ago and no other problems occured.

I have another server here, Redhat 7.3, with a bit older 4-something mysql. I was planning on upgrading it as well, so I will do it tommorow (I am using RPMs on it) and report the outcome here again.

I apologize, I am suddenly unable to re-create the bug as well. The problem started earlier only with a LEFT JOIN statement, and then all of a sudden occured with a simple SELECT. now the same re-creation script that I wrote does not re-create the bug. So the problem seems chaotic. Perhaps it has something to do with mysql's caching ?

I will try to re-create it again from the begining.

Again, sorry for the mess.

Here's the re-creationg script I planned on posting earlier:
DROP TABLE IF EXISTS a1;
CREATE TABLE a1
(
    a int      NOT NULL,
    b1 char(10)
);

DROP TABLE IF EXISTS a2;
DROP TABLE IF EXISTS a2; # temporary
CREATE TABLE a2
(
    a int      NOT NULL,
    b2 char(10)
);

INSERT INTO a1 VALUES (1,'a1-1'),(2,'a1-2');
INSERT INTO a2 VALUES (1,'a2-1'),(3,'a2-3');

SELECT * FROM a2;
SELECT * FROM a1,a2;
SELECT * FROM a1 JOIN a2 USING(a);
SELECT * FROM a1 LEFT JOIN a2 USING(a);

CREATE TEMPORARY TABLE a2
(
    a int      NOT NULL,
    b2 char(10)
);

SELECT * FROM a2;
SELECT * FROM a1,a2;
SELECT * FROM a1 JOIN a2 USING(a);
SELECT * FROM a1 LEFT JOIN a2 USING(a);

DELETE FROM a2;

SELECT * FROM a2;
SELECT * FROM a1,a2;
SELECT * FROM a1 JOIN a2 USING(a);
SELECT * FROM a1 LEFT JOIN a2 USING(a);

The output is:

+---+------+
| a | b2   |
+---+------+
| 1 | a2-1 |
| 3 | a2-3 |
+---+------+
2 rows in set (0.00 sec)

+---+------+---+------+
| a | b1   | a | b2   |
+---+------+---+------+
| 1 | a1-1 | 1 | a2-1 |
| 2 | a1-2 | 1 | a2-1 |
| 1 | a1-1 | 3 | a2-3 |
| 2 | a1-2 | 3 | a2-3 |
+---+------+---+------+
4 rows in set (0.00 sec)

+---+------+---+------+
| a | b1   | a | b2   |
+---+------+---+------+
| 1 | a1-1 | 1 | a2-1 |
+---+------+---+------+
1 row in set (0.00 sec)

+---+------+------+------+
| a | b1   | a    | b2   |
+---+------+------+------+
| 1 | a1-1 |    1 | a2-1 |
| 2 | a1-2 | NULL | NULL |
+---+------+------+------+
2 rows in set (0.00 sec)

And it repeats itself 3 times, exactly the same.

I was able to repeat this on x86 linux (2.4.21 #12 SMP) but *NOT* on windows 2000 or solaris 
8 64-bit all using 4.1.3-beta.

Looks like it might be linux specific.

A note on the script that reproduces the problem.
If one rewrites the problem query:
  SELECT * FROM a1 LEFT JOIN a2 USING(a);
into the equivalent one:
  SELECT * FROM a1 LEFT JOIN a2 ON a1.a = a2.a;
the problem dissapears.
This may serve as a hint in case the bug appears again.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

The bug is not present any more in version 4.1.4.