MySQL Bugs: #47132: CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables

Bug #47132	CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
Submitted:	4 Sep 2009 12:08	Modified:	9 Sep 2010 17:44
Reporter:	Satya B	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0, 5.1+	OS:	Any
Assigned to:	Libing Song	CPU Architecture:	Any
Tags:	CREATE TABLE, create view, if not exists

Description:
From http://dev.mysql.com/doc/refman/5.0/en/create-table.html documentation

 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.
    * ... 

 but this doesn't apply if the table is a view is over multiple tables. i.e, the data is not inserted if the table exists

 Please see how to repeat for example
   

How to repeat:
CREATE TABLE t1(a INT);
CREATE TABLE t2(b INT);
CREATE TABLE t3(c INT);

INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (4),(5),(6);
INSERT INTO t3 VALUES (7),(8),(9);

CREATE VIEW v1 AS SELECT * FROM t1,t2;
CREATE VIEW v2 AS SELECT * FROM t1;

mysql> CREATE TABLE IF NOT EXISTS v1(a INT, b INT) SELECT * FROM t3;
ERROR 1050 (42S01): Table 'v1' already exists

// Since v1 is a view over multiple tables, data is not inserted.

mysql> CREATE TABLE IF NOT EXISTS v2(a INT) SELECT * FROM  t3;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

// v2 is a view over single table, v2 is overwritten and becomes a table v2
// and the data from t3 is inserted.

mysql> show create table v2;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------+
| v2    | CREATE TABLE `v2` (
  `a` int(11) default NULL,
  `c` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from v2;
+------+------+
| a    | c    |
+------+------+
| NULL |    7 | 
| NULL |    8 | 
| NULL |    9 | 
+------+------+
3 rows in set (0.00 sec)

Suggested fix:
should make a decision to either fix  or document it

It is even worse for me on 5.1.38 (Windows7 64 bit, 64 bit server).

USE satya;

CREATE TABLE t1(a INT);
CREATE TABLE t2(b INT);
CREATE TABLE t3(c INT);

INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (4),(5),(6);
INSERT INTO t3 VALUES (7),(8),(9);

CREATE VIEW v1 AS SELECT * FROM t1,t2;
CREATE VIEW v2 AS SELECT * FROM t1;

CREATE TABLE IF NOT EXISTS v1(a INT, b INT) SELECT * FROM t3;
-- server crashed!! 
-- after starting server we continue

CREATE TABLE IF NOT EXISTS v2(a INT) SELECT * FROM  t3;
-- (3 row(s) affected, 1 warning(s))
SHOW WARNINGS;
-- Table 'v2' already exists
SHOW FULL TABLES;
/*
Tables_in_satya  Table_type
---------------  ----------
t1               BASE TABLE
t2               BASE TABLE
t3               BASE TABLE
v1               VIEW      
v2               VIEW      
*/
SELECT * FROM v2;
/*
   a  
------
     1
     2
     3
     7
     8
     9
*/

so conclusions..

1) this is an 'S1' bug due to the crash
2) the succesfull statement should not insert anything - not even do anything. As long as tables and views in a MySQL database cannot be named the same the statement should return 'table already exists',

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.85-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > create database gg;
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > use gg
Database changed
mysql 5.0 > CREATE TABLE t1(a INT);
Query OK, 0 rows affected (0.25 sec)

mysql 5.0 > CREATE TABLE t2(b INT);
Query OK, 0 rows affected (0.14 sec)

mysql 5.0 > CREATE TABLE t3(c INT);
Query OK, 0 rows affected (0.13 sec)

mysql 5.0 >
mysql 5.0 > INSERT INTO t1 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 > INSERT INTO t2 VALUES (4),(5),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 > INSERT INTO t3 VALUES (7),(8),(9);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.0 >
mysql 5.0 > CREATE VIEW v1 AS SELECT * FROM t1,t2;
Query OK, 0 rows affected (0.11 sec)

mysql 5.0 > CREATE VIEW v2 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.09 sec)

mysql 5.0 >
mysql 5.0 > CREATE TABLE IF NOT EXISTS v1(a INT, b INT) SELECT * FROM t3;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql 5.0 >

Thank you for the bug report. On my side 5.0/5.1 both crashes and 5.4.4 has different results than you reported. Which server version had you tested. Thanks in advance.

@Miguel

Wasn't it more relistic to mark as 'verified' than 'need feedback'? After all you found two problems yourself:

1) a crash
2) insistent behavior across versions

The problem is that only the original reporter can change status (by replying). If he does not, this report will expire with 'no feedback' classification. And then I will have to create a new bug report after 1 month (as I cannot change status of this one) and starting fixing the bug(s) will take 1 month than what it needs to! This has happened some times before.

There is a crash in the two still-supported stable versions. So no matter what CREATE TABLE .. SELECT has problems if a VIEW exists named as the table specified in CREATE.

By the way: don't you think it is bug too what I reported that the CREATE TABLE statement that succeeds INSERTS into existing VIEW? You did not comment on that.

I can create one or two new bug reports of course if you want this one strictly focused on what Satya reported, but then Sveta will reply and scold me and tell that I should not report bugs that have already been reported! :-)

For me there is no difference between 5.1.38 and 5.4.1. They both exhibit the behaviour: 

1) crash on the statement "CREATE TABLE IF NOT EXISTS v1(a INT, b INT) SELECT * FROM t3;"
2) values (7,8,9) get INSERTED to t1 (and thus on v2 too) on the statement "CREATE TABLE IF NOT EXISTS v2(a INT) SELECT * FROM  t3;"

My personal opinion is that 2) is worse than 1) actually (but official guidelines here seems to be opposite: a server crash is panic - corruption of user's data not very important! :-( )

This bug is a spin off from Bug#46384 which addresses the crash and the bug is marked private for due to its seriousness but the patch is public. This bug focus on analyzing the specific problem reported by Satya, which spans over all versions of MySQL, while the crash only occurred on 5.0 and 5.1 -- the reason is that the code is quite different among the server versions.

Please, don't open up any extra bug reports. This bug has been opened because we are already working on the related issues.

"while the crash only occurred on 5.0 and 5.1"
well .. 5.4.1 crashes for me too. But it may be fixed in 5.4 trunk of course.

I have no problems waiting for conclusions in this and the other report mentioned.

My Bad, should have mentioned that the crash is fixed by BUG#46384 and this bug is a spin off.

It wasn't crashing in 5.4 (verified in mysql-pe) branch, may be there are some fixes to be merged to main trunk.

I think it's likely we can close this as not a bug, adding a line or two in the manual. First let us get some things straight:

1) This bug does not deal with the crash, since that bug (Bug#46384) is closed.

2) Hence, let us not consider what happened before that patch was pushed.

3) Rather, let us look at the strange new behaviour that results (slightly minimized test case, did away with t3)

SELECT version();
version()
5.1.39-debug-log // Same behaviour in all versions, 5.0, 5.1, 5.4
CREATE TABLE t1(a INT);
CREATE TABLE t2(b INT);
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (4),(5),(6);
CREATE VIEW v1 AS SELECT * FROM t1,t2;
CREATE VIEW v2 AS SELECT * FROM t1;
CREATE TABLE IF NOT EXISTS v1(a INT, b INT) SELECT 100 AS a, 200 AS b;
ERROR 42S01: Table 'v1' already exists
SELECT * FROM v1;
a	b
1	4
2	4
3	4
1	5
2	5
3	5
1	6
2	6
3	6
SHOW CREATE TABLE v1;
View	Create View	character_set_client	collation_connection
v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b` from (`t1` join `t2`)	latin1	latin1_swedish_ci
CREATE TABLE IF NOT EXISTS v2(a INT) SELECT 100 AS a;
Warnings:
Note	1050	Table 'v2' already exists
SELECT * FROM v2;
a
1
2
3
100
SHOW CREATE TABLE v2;
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a` from `t1`	latin1	latin1_swedish_ci

Strange indeed. Ostensibly, v2 is still view over t1. Yet it retains the value 100 from the new view definition. This has to come from somewhere! Now what about t1?

SELECT * FROM t1;
a
1
2
3
100

Yes indeed, it ended up in t1!

Actually, this behaviour doesn't seem too far-fetched. Since according to the manual, CREATE TABLE IF NOT EXISTS is supposed to act more or less like an INSERT INTO ... SELECT ... if that table-to-be-created exists. One might have opinions on this (and personally I don't like it) but it is established and documented behaviour. And insertion into a single-table view will insert into its underlying table. However, insertion into a join view is forbidden, and rightfully so.

Not completely intuitive, but it's logical if you think about it.

I do not understand. I think you only complicate. Let me repeat:

CREATE TABLE IF NOT EXISTS v2(a INT) SELECT * FROM  t3;
-- (3 row(s) affected, 1 warning(s))
SHOW WARNINGS;
-- Table 'v2' already exists

If table exists then CREATE TABLE IF NOT EXISTS should return a error simply. What else does IF NOT EXISTS mean? "CREATE TABLE IF NOT EXISTS" and the warning "table already exists" are inconsistent.

Also nobody has a change to foresee that values (7,8,9) get INSERTED:

SELECT * FROM v2;
/*
   a  
------
     1
     2
     3
     7
     8
     9
*/

Is it more important to close reports than to fix bugs?

"IF NOT EXISTS" was explicitly designed to not emit a error if the object exists. This is documented behavior.

Also, your testy comments are uncalled for. Please, let's keep a civic tone and solely based on technical arguments.

The core of the discussion is this:

CREATE TABLE one_table ...;
..results in..
INSERT INTO another_table ...;

..what is nonsense to me. For this discussion *technical arguments* are not required at all - *common sense arguments* are enough. No documentation can change that. In this particular situation CREATE TABLE should raise an error - not a warning - and nothing should happen at all.

It does not matter if my comments were 'called for'.  Guidelines here are that 'anyone can comment on a bug'. 

But you can disclose that other bug report, so that we can see the complete discussion about this. That wold be the best way to ensure proper communication!

> The core of the discussion is this:
> 
> CREATE TABLE one_table ...;
> ..results in..
> INSERT INTO another_table ...;

No. It has to be a CREATE TABLE IF NOT EXISTS. On the other hand, CREATE TABLE does raise a error. I invite you to read the documentation for "IF NOT EXISTS":

"The keywords IF NOT EXISTS prevent an error from occurring if the table exists. However, there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement."

and

"For CREATE TABLE ... SELECT, if IF NOT EXISTS is given and the table already exists, MySQL handles the statement as follows" ..

Which details how it behaves and that the rows are inserted only if the "IF NOT EXISTS" keywords are supplied and the table exists. BTW, this is very similar to how other databases implement the "IF NOT EXISTS" keywords.

Now, this behavior might not make sense to you but it has been this way for several years and there are lot of applications and users that expect this behavior. In my personal opinion, this doesn't make much sense either, but opinions are not compelling arguments to change something that has be relied upon for years. On top, our guidelines don't allow us to change this in stable releases. In this case, what is documented prevails.

What is (hopefully) being discussed here is that the documented behavior does not apply if the underlying object is a multiple table view.

> It does not matter if my comments were 'called for'.  Guidelines here are that
> 'anyone can comment on a bug'.

Sure, but comments like "Is it more important to close reports than to fix bugs?" (the testy comment) adds nothing to the discussion and can only alienate people from having a technical discussion with you. I'm not asking for no discussion, I'm asking for a respectful discussion.

" I invite you .."  .. so now it is me who will have to ask for a civic tone here.

No matter if IF EXISTS is used or not, CREATE TABLE should not do anything with any other table than the one specified as parameter for CREATE TABLE.

> " I invite you .."  .. so now it is me who will have to ask for a civic tone here.

OK.

> No matter if IF EXISTS is used or not, CREATE TABLE should not do anything
> with any other table than the one specified as parameter for CREATE TABLE.

I agree.

Additionally I wrote this "Is it more important to close reports than to fix bugs?" as a reply to Martin who wrote "I think it's likely we can close this as not a bug, adding a line or two in the manual." "adding a line or two in the manual" changes nothing.

I also do not believe users will expect this behavior as you claim.  On the opposite I think it would surprise everybody who knew.

> I also do not believe users will expect this behavior as you claim.  On the opposite
> I think it would surprise everybody who knew.

But it would certainly break various applications. For example, phpMyAdmin uses "IF NOT EXISTS" where it intends the statement to be a no-op if the table exists -- there are certainly other applications relying on the documented behavior.

If the user expects a error, do not use "IF NOT EXISTS". Now, I agree with you that the implicit INSERT behavior is indeed counter intuitive, but... it is what it is and seems to have been designed and documented this way. We can't change it now without causing even more confusion (and incompatible changes by consequence). Backwards compatibility is important.

What we propose for now is to expand the documentation (in 5.0 and up) to clarify this behavior, especially with regard to views.

I do not believe any application - also not PMA - expects the behavior that data are INSERTED on a CREATE TABLE [IF NOT EXISTS] statement.  

But OK .. an error may cause application failures (like terminating completion of a script) if it is unexpected. 

My main concern is and was the INSERTS, that may corrupt table data for users should they accidentally specify a colliding table name. This is actually serious in my opinion.  Equally or more serious than the server crash according to my priorities (but not to official criteria here, I think).

In my logic CREATE TABLE [IF NOT EXISTS] should mean:
[IF NOT EXISTS]: CREATE TABLE
[IF EXISTS]: do nothing (because no action was specified for the condition).

Actually and with hindsight there is no error and should not be if nothing is done. So warning is consistent.  I admit to have been mistaken here.

I think we have made clear our positions now and I can only repeat what I have said already.  So I will probably not write here again.

btw: the argumentation that it is implemented and documented like does not seem valid to me. Then there was no reason to fix bugs - you could simply always update documentation instead! So fire all developers and hire 50 documentationists :-)

"Another testy comment that only downgrades the level of the discussion" .. my point was that *because it is implemented and documented like that* is not always a valid criteria for deciding on bug/not a bug. If it was you would be able to document any buggy behavior and call it 'not a bug' and my comment would be valid. 

Now you refer to another criteria named 'intended behavior'. Maybe the person who coded this *intended* it - but I rather think he *overlooked* it.  Who ultimately decides what is *intended behavior* here?

Anyway: will it be considered *a bug* or *intended behavior* that "CREATE TABLE ... some_table ..." does "INSERT INTO ... some_other_table ..." in specific scenarios?  Could you please reply to that unambigously.  Because that reply I am still missing.

> "Another testy comment that only downgrades the level of the discussion" .. my
> point was that *because it is implemented and documented like that* is not always
> a valid criteria for deciding on bug/not a bug. If it was you would be able to
> document any buggy behavior and call it 'not a bug' and my comment would be valid. 

Agree.

> Now you refer to another criteria named 'intended behavior'. Maybe the person who
> coded this *intended* it - but I rather think he *overlooked* it. 

Exactly, and this where we disagree.

> Who ultimately decides what is *intended behavior* here?

Not me. In this case, probably a senior developer that was around back when this was introduced.

> Anyway: will it be considered *a bug* or *intended behavior* that "CREATE TABLE ...
> some_table ..." does "INSERT INTO ... some_other_table ..." in specific scenarios? 
> Could you please reply to that unambigously.  Because that reply I am still missing.

I personally think it is intended behavior as it has been this way for about 10 years -- there are even test cases to ensure this behavior. The code itself predates the use of revision control for the source code, so i can't find the exact changeset that introduced this.

"it is intended behavior as it has been this way for about 10 years" .. but then later introduction of VIEWS also introduced some non-intended behavior I think? VIEWS have not been there for 10 years, but 4-5 years only.

The issue occurs due to naming collision with a VIEW, doesn't it?

So the remaining issue for me can be described like this:

"CREATE TABLE tablename IF NOT EXISTS .." may generate INSERTS to an unreferenced table if 'tablename' collides with the name of an existing VIEW.

(If not I will have to replay the test case all over again to refresh my memory!).

> The issue occurs due to naming collision with a VIEW, doesn't it?

Yes, somewhat. Views and tables share the same namespace.

> So the remaining issue for me can be described like this:
> 
> "CREATE TABLE tablename IF NOT EXISTS .." may generate INSERTS to an unreferenced 
> table if 'tablename' collides with the name of an existing VIEW.

Somewhat, yes. The bug and discrepancy is that if the object referenced in the CREATE TABLE exists and is a view, the data is inserted. If the object is a multiple table view, no data is inserted. We need to determine what is the intended behavior with respect to if the object is a view (*). Once this is done, we can determine whether there is a bug to fix or documentation to improve.

* Both the IF NOT EXISTS for a CREATE TABLE and the implicit insert.

I agree with Peter that this is counter-intuitive. I would have expected that CREATE TABLE IF NOT EXISTS does all (if there's no table) or nothing (if the table exists), i.e the correct operation should be :

if (<table> not exists)
{
  CREATE TABLE;
  INSERT ...;
}

and not :

if (<table> not exists)
{
   CREATE TABLE;
}
INSERT ...

However this second behavior is what has been implemented (a long time ago) and documented (a long time ago). And thus it became the behavior that people expect.
And I hope you will understand that we can't just go and change it in a release version bugfix : it will have the potential to break a good deal of not-very-intuitive-but-following-mysql-docs apps.
Instead we should consider this as a feature completion fix for the development branch.

why not (as a 1st step workaround) add the condition

if (<table> is not VIEW)
{
INSERT ...
}

.. but maybe that is not easy-to-go-with as I understand Davi's remark about shared space for tables and views.

I cannot accept data corruption as a result of valid and unreleated statements.  I also find it disappointing that I had to be nasty to get attention to this (my nastiness was a 'tactical nastiness' :-) )

However I can accept of course that priority will have to be decided according to what other bugs and tasks are pending and the relative importance of those.

(*to be fixed later* + documenting) is acceptable if it is absolutely required complications and current workload taken into consideration. *not a bug* is not acceptable in my opinion simply because CREATE should not INSERT (except for CREATE .. SELECT .. of course).

I hope you would agree that replacing 1 non-intuitive solution (the current one) with another is not a good idea.
And it's not about technical arguments or ease of implementation. It is about keeping consistent behavior in a GA release.

I think the conclusion is that we are not able to understand another

Look at this (because this is the only test case required for the discussion in my opinion):

SELECT * FROM t1;
/*
   a  
------
     1
     2
     3
*/

CREATE TABLE IF NOT EXISTS v2(a INT) SELECT * FROM  t3;
-- (3 row(s) affected, 1 warning(s))

SELECT * FROM t1;
/*
   a  
------
     1
     2
     3
     7
     8
     9
*/

This is data corruption simply I my opinion. In my priority this as far more important than any consideration about consistent behavior. Maybe you think that I am nasty again, but if 'consistent behavior' was so important then bugs should never be fixed! Every bugfix breaks 'consistency' in some sense.

If VIEWS did not exist there was no problem (pardon if I miss some detail). But introduction of VIEWS created this with CREATE TABLE IF NOT EXISTS. However it can very well happen that user specifies a table name that collides with the name of a VIEW.

Let's not turn the bug report into a discussion board please. Feel free to contact me on my e-mail (joro at sun dot com).

I was replying to questions "I hope you understand ..".  If the questions had not appeared I would not have posted the last 2 or 3 times. If you do not like the answer then you should not ask the question.

I refuse to make it a private discussion as it affects not only you and me. But I have no plans to post again here as I think I have made my position clear. It is data corruption and this is for me the worst possible kind of a bug. I think it should be a 'verified' bug (but priorities will have to decide if the fix should go into 5.0/5.1 or only from 5.4 or later).

Just for the record: the internals mailing list (http://lists.mysql.com/internals/) is a attractive alternative for technical discussions like this.

I do not find it is a technical discussion and I have no intentions to get involved in MySQL internals at all.

I have started my own discussion here (so that you will not be bothered by more from me here):
http://www.webyog.com/blog/2009/09/16/bug-or-not-a-bug-part-1/

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89358

2939 Alexey Botchkov	2009-11-04
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables 
         if the created table already exists, we insert no rows in the existing tables.
         In the binary log we write CREATE TABLE command with the structure of the existing
         table.
         Several tests have to be fixed then as CREATE TABLE IF EXISTS SELECT statements
         are replaced with the INSERT INTO SELECT
      
      per-file comments:
        mysql-test/r/auto_increment.result
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables 
          result updated
        mysql-test/r/create.result
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables 
          result updated
        mysql-test/r/trigger.result
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables 
          result updated
        mysql-test/t/auto_increment.test
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables 
          test fixed.
        mysql-test/t/create.test
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables 
          test fixed.
        mysql-test/t/trigger.test
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables 
          test fixed.
        sql/sql_insert.cc
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables 
         do nothing if the table already exists.

Alexey,

You should make the entire statement fails, e.g. : 

#setup
CREATE TABLE t0 (a int);
CREATE TABLE t1 (a int);

# this should fail with an error
CREATE TABLE t1(a INT) SELECT * FROM  t0;

# just like this will fail with an error
CREATE TABLE t1(a INT);

# this shouldn't insert any rows :
CREATE TABLE IF NOT EXISTS t1(a INT) SELECT * FROM  t0;

# just like this one :
CREATE TABLE IF NOT EXISTS t1(a INT);

Please also add a more elaborate test case.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90667

2952 Alexey Botchkov	2009-11-16
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
         if the created table already exists, we insert no rows in the existing tables.
         In the binary log we write CREATE TABLE command with the structure of the existing
         table.
         Several tests have to be fixed then as CREATE TABLE IF EXISTS SELECT statements
         are replaced with the INSERT INTO SELECT
      
      per-file comments:
        mysql-test/r/auto_increment.result
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
          result updated
        mysql-test/r/create.result
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
          result updated
        mysql-test/r/trigger.result
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
          result updated
        mysql-test/t/auto_increment.test
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
          test fixed.
        mysql-test/t/create.test
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
          Test case added, and the rest of the test fixed.
        mysql-test/t/trigger.test
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
          test fixed.
        sql/sql_insert.cc
      Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
         do nothing if the table already exists.

Bug#46153 was closed as "Not a Bug", it's a duplicate of this bug.

First I want to claim that I'm OK with the idea to not insert data if the object already exist, but I have some concerns on the proposed patch. After the patch, a CREATE TABLE IF NOT EXISTS...SELECT will be binlogged as a CREATE TABLE statement if the object already exists even if **statement format** is used, which I think is not good, I think we should binlog the statement as is when statement format is used. CREATE TABLE IF NOT EXISTS or CREATE TABLE IF NOT EXISTS LIKE statements are binlogged as is whether the object exists or not in statement format, if we want to change the behavior, then the behavior of these two statement should also be change to make them consistent. I think that the current binlogging behavior for statement format should not be changed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/99399

2952 Alexey Botchkov	2010-02-04
      Bug#47132      CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
          The decision was made to simplify the CREATE TABLE IF NOT EXISTS. It is not going to do
          anything if the table already exists. (Before it tried to insert the SELECT result into
          the existing table).
          Several tests have to be fixed then as CREATE TABLE IF EXISTS SELECT statements
               are replaced with the INSERT INTO SELECT
      
      per-file comments:
        mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test
      Bug#47132      CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
        mysql-test/r/auto_increment.result
          result updated
      Bug#47132      CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
        mysql-test/r/create.result
          result updated
      Bug#47132      CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
        mysql-test/r/trigger.result
          result updated
      Bug#47132      CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
        mysql-test/t/auto_increment.test
      Bug#47132      CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
        mysql-test/t/create.test
      Bug#47132      CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
        mysql-test/t/trigger.test
      Bug#47132      CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
        sql/sql_insert.cc
      Bug#47132      CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
          Do no insert if the table already exists.
          Binlog the statement as it is for the statement format.

If I understood it correctly, won't this make master and slave go
out of sync when statement based replication is used?

For instance (consider O - old, N - new, M - master, S - slave):

  - OM --> NS

    MASTER> CREATE TABLE t0 (a INT);
    MASTER> CREATE TABLE t1 (a INT);
    MASTER> INSERT INTO t0 (1),(2),(3);
    MASTER> CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t0
    MASTER> SELECT * FROM t1;
    {1,2,3}
    -- sync_slave_with_master
    SLAVE> SELECT * FROM t1;
    {}

  - NM --> OS

    MASTER> CREATE TABLE t0 (a INT);
    MASTER> CREATE TABLE t1 (a INT);
    MASTER> INSERT INTO t0 (1),(2),(3);
    MASTER> CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t0
    MASTER> SELECT * FROM t1;
    {}
    -- sync_slave_with_master
    SLAVE> SELECT * FROM t1;
    {1,2,3}

To make my previous comment clear, I am considering:

 1. cross-version replication;
 2. statement based replication;
 3. Old server does not contain the proposed fix, whereas New server does.

Regards,
Luís

See also bug#51501, which can also be fixed by the proposed solution for this bug (i.e. not executing the insert part when the table already exists).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/112878

3078 Li-Bing.Song@sun.com	2010-07-05
      Bug#47132      CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables
      
      The decision was made to simplify the CREATE TABLE IF NOT EXISTS. It is not
      going to do anything if the table already exists. (Before it tried to insert the SELECT
      result into the existing table).
      
      Several tests have to be fixed then as CREATE TABLE IF EXISTS SELECT statements
      are replaced with the INSERT INTO SELECT
     @ mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test
        Nothing inserts into the table when it exists.
        So error ER_DUP_ENTRY will not happen.
     @ mysql-test/include/mix1.inc
        Nothing inserts into the table when it exists.
        So error ER_DUP_ENTRY will not happen.
     @ mysql-test/suite/rpl/t/rpl_create_if_not_exists.test
        Verify 'CREATE TABLE IF NOT EXISTS ... SELECT' statements are not binlogged if the table exists.
     @ mysql-test/t/auto_increment.test
        Nothing inserts into the table when it exists.
        So use 'INSERT ... SELECT' instead of 'CREATE ...SELECT'.
     @ mysql-test/t/create.test
        Nothing inserts into the table when it exists.
        So use 'INSERT ... SELECT' instead of 'CREATE ...SELECT'.
        
        Add test to verify that nothing will insert into the table if it exists.
     @ mysql-test/t/trigger.test
        Nothing inserts into the table when it exists.
        So use 'INSERT ... SELECT' instead of 'CREATE ...SELECT'.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/112918

3076 Konstantin Osipov	2010-07-06
      A fix for Bug#47132 (with review comments).

In order to keep consistency between New master and Old slave,
the statement will not be binlogged if the table exists.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/112931

3076 Li-Bing.Song@sun.com	2010-07-06
      Bug#47132   CREATE TABLE.. SELECT.. data not inserted if table is view over
                  multiple tables
      
      The decision was made to simplify the CREATE TABLE IF NOT EXISTS. It is not
      going to do anything if the table already exists. (Before it tried to insert the
      SELECT result into the existing table).
      
      In order to keep consisteny between New master and Old slave, the statement
      will not be binlogged if the table already exists.
      
      Several tests have to be fixed then as CREATE TABLE IF EXISTS SELECT statements
      are replaced with the INSERT INTO SELECT

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116060

3184 Li-Bing.Song@sun.com	2010-08-18
      WL#5370 Keep forward-compatibility when changing 
              'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
      BUG#47132, BUG#47442, BUG49494, BUG#23992 and BUG#48814 will disappear
      automatically after the this patch.
      BUG#55617 is fixed by this patch too.
            
      This is the 5.5 part.
      It implements:
      - 'CREATE TABLE IF NOT EXISTS ... SELECT' statement will not insert
        anything and binlog anything if the table already exists.
        It only generate a warning that table already exists.
      - A couple of test cases for the behavior changing.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116062

3186 Li-Bing.Song@sun.com	2010-08-18
      WL#5370 Keep forward-compatibility when changing 
              'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
      BUG#47132, BUG#47442, BUG49494, BUG#23992 and BUG#48814 will disappear
      automatically after the this patch.
      BUG#55617 is fixed by this patch too.
                  
      This is the 5.5 part.
      It implements:
      - 'CREATE TABLE IF NOT EXISTS ... SELECT' statement will not insert
        anything and binlog anything if the table already exists.
        It only generate a warning that table already exists.
      - A couple of test cases for the behavior changing.

It disappears after the patch for WL#5370. As behavior of CREATE TABLE IF NOT EXISTS SELECT has changed. It will never insert anything and not be binlogged
after the worklog.

Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)

Noted in 5.1.51, 5.5.6, 5.6.1 changelogs.

As of MySQL 5.5.6, handling of CREATE TABLE IF NOT EXISTS ... SELECT
statements has been changed for the case that the destination table
already exists:

* Previously, for CREATE TABLE IF NOT EXISTS ... SELECT, MySQL produced
  a warning that the table exists, but inserted the rows and wrote the
  statement to the binary log anyway. By contrast, CREATE TABLE ...
  SELECT (without IF NOT EXISTS) failed with an error, but MySQL
  inserted no rows and did not write the statement to the binary log.

* MySQL now handles both statements the same way when the destination
  table exists, in that neither statement inserts rows or is written to
  the binary log. The difference between them is that MySQL produces a
  warning when IF NOT EXISTS is present and an error when it is not.

This change in handling of IF NOT EXISTS results in an
incompatibility for statement-based replication from a MySQL 5.1
master with the original behavior and a MySQL 5.5 slave with the new
behavior. Suppose that CREATE TABLE IF NOT EXISTS ... SELECT is
executed on the master and the destination table exists. The result
is that rows are inserted on the master but not on the slave.
(Row-based replication does not have this problem.)

To address this issue, statement-based binary logging for CREATE
TABLE IF NOT EXISTS ... SELECT is changed in MySQL 5.1 as of 5.1.51:

* If the destination table does not exist, there is no change: The
  statement is logged as is.

* If the destination table does exist, the statement is logged as the
  equivalent pair of CREATE TABLE IF NOT EXISTS and INSERT ... SELECT
  statements. (If the SELECT in the original statement is preceded by
  IGNORE or REPLACE, the INSERT becomes INSERT IGNORE or REPLACE,
  respectively.)

This change provides forward compatibility for statement-based
replication from MySQL 5.1 to 5.5 because when the destination table
exists, the rows will be inserted on both the master and slave. To
take advantage of this compatibility measure, the 5.1 server must be
at least 5.1.51 and the 5.5 server must be at least 5.5.6.

To upgrade an existing 5.1-to-5.5 replication scenario, upgrade the
master first to 5.1.51 or higher. Note that this differs from the
usual replication upgrade advice of upgrading the slave first.

A workaround for applications that wish to achieve the original
effect (rows inserted regardless of whether the destination table
exists) is to use CREATE TABLE IF NOT EXISTS and INSERT ... SELECT
statements rather than CREATE TABLE IF NOT EXISTS ... SELECT
statements. 

Along with the change just described, the following related change
was made: Previously, if an existing updatable view was named as the
destination table for CREATE TABLE IF NOT EXISTS ... SELECT, rows
were inserted into the underlying base table and the statement was
written to the binary log. As of MySQL 5.1.51 and 5.5.6, nothing is
inserted or logged.