| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.0 | OS: | Linux (Linux) |
| Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[14 Oct 2004 16:15]
MySQL Verification Team
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
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)