Bug #24508 Inconsistent results of CREATE TABLE ... SELECT when temporary table exists
Submitted: 22 Nov 2006 14:54 Modified: 16 May 2007 19:34
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.32-bk, 5.1 BK, 4.1 BK OS:Linux (Linux Suse 10.1)
Assigned to: CPU Architecture:Any

[22 Nov 2006 14:54] Dmitry Lenev
Description:
Attempt to execute CREATE TABLE IF NOT EXISTS t1 SELECT ... statement produces inconsistent results if there is already temporary table with same name. Non-temporary table is created correctly in this case but rows from SELECT part are inserted into temporary table.

I think that correct behavior in such cases is either:
1) not to create non-temporary table, produce proper warning,
   insert rows into temporary table
or
2) create non-temporary table and insert rows into it

(I suspect that 4.1 and earlier versions are affected as well)

How to repeat:
drop table if exists t1;
# Warnings:
# Note  1051    Unknown table 't1'
create temporary table t1 (j int);
# "create table t1 select 1 as i" behaves in the same way
create table if not exists t1 select 1 as i;
select * from t1;
# The row is inserted into temporary table
# j
# 1
drop temporary table t1;
select * from t1;
# Oops but we also have empty base table now!
# i
drop table t1;
[22 Nov 2006 19:55] Sveta Smirnova
Thank you for the report.

Verified as described. All versions are affected.
[16 May 2007 13:48] Bugs System
Pushed into 5.0.42
[16 May 2007 13:49] Bugs System
Pushed into 5.1.19-beta
[16 May 2007 19:34] Paul Dubois
Noted in 5.0.42, 5.1.19 changelogs.

An attempt to execute CREATE TABLE ... SELECT when a temporary table
with the same name already existed led to the insertion of data into
the temporary table and creation of an empty non-temporary table.
[30 Nov 2009 8:09] Roel Van de Paar
See bug #49193