MySQL Bugs: #46153: CREATE TABLE IF NOT EXISTS with SELECT

Bug #46153	CREATE TABLE IF NOT EXISTS with SELECT
Submitted:	13 Jul 2009 18:01	Modified:	2 Jun 2010 2:22
Reporter:	Ruvim Pinka	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A new records are inserted to table despite the "IF NOT EXISTS" statement.

How to repeat:
DROP TABLE IF EXISTS ttt;
CREATE TABLE IF NOT EXISTS ttt SELECT 0 AS n;
CREATE TABLE IF NOT EXISTS ttt SELECT 0 AS n;
SELECT COUNT(*) FROM ttt;

 -- Returns 2. Should returns 1 (IMHO ;)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.1/en/create-table.html:

----<q>----
 For CREATE TABLE ... SELECT, if IF NOT EXISTS is given and the table already exists, MySQL handles the statement as follows:

    *      The table definition given in the CREATE TABLE part is ignored. No error occurs, even if the definition does not match that of the existing table.
    *      If there is a mismatch between the number of columns in the table and the number of columns produced by the SELECT part, the selected values are assigned to the rightmost columns. For example, if the table contains n columns and the SELECT produces m columns, where m < n, the selected values are assigned to the m rightmost columns in the table. Each of the initial n – m columns is assigned its default value, either that specified explicitly in the column definition or the implicit column data type default if the definition contains no default.
    *      If strict SQL mode is enabled and any of these initial columns do not have an explicit default value, the statement fails with an error.
----</q>----

So this is expected behavior

Duplicate of bug #47132