MySQL Bugs: #24508: Inconsistent results of CREATE TABLE ... SELECT when temporary table exists

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

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;

Thank you for the report.

Verified as described. All versions are affected.

Pushed into 5.0.42

Pushed into 5.1.19-beta

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.

See bug #49193