Bug #6084 SELECT on temporary table fails when query cache is on
Submitted: 14 Oct 2004 9:16 Modified: 24 Oct 2004 7:14
Reporter: Georg Richter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0 OS:Linux (Linux)
Assigned to: Oleksandr Byelkin

[14 Oct 2004 9:16] Georg Richter
Description:
Not sure if it's a bug or a limitation, but it's not listed in Section A.7.3 (Temporary table problems) of 
the manual. 
 
When creating a temporary table t1 and a table t1 already exists, which is referenced by another 
table t2, an insert command will add values to the temporary table, but a select command retrieves 
the value from the orginal table. 

How to repeat:
5.0.2-alpha-debug-log 
[11:13] root@test> CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; 
Query OK, 0 rows affected (0.14 sec) 
 
5.0.2-alpha-debug-log 
[11:13] root@test> CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), 
    -> FOREIGN KEY (t1_id) REFERENCES t1(id)  ON DELETE CASCADE, 
    -> FOREIGN KEY (t1_id) REFERENCES t1(id)  ON UPDATE CASCADE) ENGINE=INNODB; 
Query OK, 0 rows affected (0.11 sec) 
 
5.0.2-alpha-debug-log 
[11:13] root@test> insert into t1 values (1),(2),(3); 
Query OK, 3 rows affected (0.03 sec) 
Records: 3  Duplicates: 0  Warnings: 0 
 
5.0.2-alpha-debug-log 
[11:14] root@test> select * from t1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
+----+ 
3 rows in set (0.01 sec) 
 
5.0.2-alpha-debug-log 
[11:14] root@test> create temporary table t1 (a int not null auto_increment primary key); 
Query OK, 0 rows affected (0.03 sec) 
 
5.0.2-alpha-debug-log 
[11:14] root@test> insert into t1 values (9); 
Query OK, 1 row affected (0.00 sec) 
 
5.0.2-alpha-debug-log 
[11:14] root@test> select * from t1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
+----+ 
3 rows in set (0.00 sec)
[14 Oct 2004 16:15] Victoria Reznichenko
Do you have a query cache turned on?
[14 Oct 2004 17:51] Georg Richter
Victoria, 
 
you're right, it's a table cache problem, which happens also with other tables (non referenced). 
Works ok, if query cache is disabled. (I changed synopsis)
[21 Oct 2004 20:57] Oleksandr Byelkin
ChangeSet 
  1.2049 04/10/21 23:56:12 bell@sanja.is.com.ua +4 -0 
  Check of temporary tables hiding for query fetched from QC (BUG#6084)
[24 Oct 2004 7:14] Oleksandr Byelkin
Thank you for bugreport! Bug is fixed, patch is pushed into our source repository.
[17 Feb 2006 16:01] Baron Schwartz
Is this bug really fixed at this time?  I am seeing similar behavior in 4.1.7-standard-log  on Gentoo Linux.  Perhaps it is a regression.  I have a more detailed test case as well, if that is of interest.  I can, for example, select * and get no rows from the temp table, but selecting count(*) returns 4 (the number of rows in the temp table).  Selecting a named column will also return 4 rows.
[24 Feb 2006 14:08] Oleksandr Byelkin
4.1.7 is _really_ old version