WL#5370: Keep forward-compatibility when changing 'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour

Change behavior of 'CREATE TABLE IF NOT EXISTS  SELECT ...'
statement on MySQL 5.5 when the destination table exists.
 
The original behavior:
  If the table exists, CREATE TABLE IF NOT EXISTS ... SELECT
  is converted to
  INSERT ... SELECT, i.e. the result of 'SELECT ...' is inserted
  into the existing table.
Behavior in 5.5+:
  If the table exists, do nothing.

Example
========

mysql> create table t1 (a varchar(10));
Query OK, 0 rows affected (0.01 sec)

-- Current behaviour
-- ---------------

mysql> create table if not exists t1 select "hello" as a;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Note  | 1050 | Table 't1' already exists | 
+-------+------+---------------------------+
1 row in set (0.00 sec)

mysql> select * from  t1;
+-------+
| a     |
+-------+
| hello | 
+-------+
1 row in set (0.00 sec)

-- New behaviour
-- -----------

mysql> create table if not exists t1 select "hello" as a;
Query OK, 0 row affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show warnings;
+-------+------+---------------------------+
| Level | Code | Message                   |
+-------+------+---------------------------+
| Note  | 1050 | Table 't1' already exists | 
+-------+------+---------------------------+
1 row in set (0.00 sec)

mysql> select * from  t1;
Empty set (0.00 sec)

This change in implementation of CREATE TABLE ... SELECT introduces
a potential failure of statement based replication when a 5.5 
instance is set as a slave for a pre-5.5 master.
Indeed, rows inserted on the master will not appear in the table
on the slave.
To address the potential statement based replication failure,
replication of CREATE TABLE ... SELECT in 5.1 will be changed
to be forward-compatible.

What is changed
===============

In 5.1:

* CREATE TABLE IF NOT EXISTS ... SELECT will insert nothing and
  not be binlogged if the underlying table exists and is a view. 

* Replication of CREATE TABLE .. IF NOT EXISTS ... SELECT
  is changed to write two binary log events:
  CREATE TABLE IF NOT EXISTS
  and INSERT ... SELECT.

* The manual is updated to reflect the changes upcoming in 5.5.

In 5.5:

* Execution of CREATE TABLE IF NOT EXISTS ... SELECT is changed

from:                              to:
                                                                  
IF NOT (table exists)              IF NOT (table exists)
  create table                       create table
ELSE                                 copy rows
  warning                          ELSE 
END IF                               warning
copy rows                          END IF

For CREATE TABLE statement, "table exists" check returns true when 
a base table or view with the same is present in the schema.
For CREATE TEMPORARY TABLE, "table exists" check returns true
when a temporary table with the same name is present in the session.
This definition of "table exists" was introduces by the fix 
for BUG#47418 and is kept.

The optional IGNORE keyword clause continues to apply to "copy rows" 
part of the statement. 
The optional REPLACE keyword only has effect on records, produced
by SELECT (i.e. on potential duplicate key errors).

Rationale for the change
========================

The current semantics turned out to be too difficult to support,
as reflected int the 'Affected bugs' section. It is found by many
counter-intuitive:

* In case an underlying table exists, CREATE TABLE statement produces an
  error, CREATE TABLE IF NOT EXISTS, a warning. In either case
  nothing is changed.
  However, CREATE TABLE ... SELECT, produces an error, and does nothing,
  whereas CREATE TABLE IF NOT EXISTS SELECT, produces a warning,
  but inserts records. Seen from that angle, lack of symmetry can be
  observed.
* The result of CREATE TABLE IF NOT EXISTS ... SELECT can be
  unpredictable if SELECT column list differs from the definition
  of the destination table.
* The reason to use IF NOT EXISTS clause, in many cases, is to
  make execution idempotent. Currently each execution of the
  statements modifies the content of the table.
* Old results can always be achieved by splitting the original
  statement in two: CREATE IF NOT EXISTS and INSERT ... SELECT.

Making cross-version replication work
=====================================

This solution consists of two parts, one in 5.1, another
in 5.5.

In 5.5 and onward:

- If the table (or a view with the same name) exists, the
  statement is not binlogged, neither in statement-based nor in
  row-based mode. This ensures that both 5.1 and 5.5 slaves are in
  sync with the master operating in new mode, (when metadata on
  master and slave were in sync). This also fixes BUG#47442.


In 5.1:

- If the table exists, binlog two events: CREATE TABLE IF NOT EXISTS
  (generated based on show_create_info output) and INSERT ... SELECT
  (based on manual query rewrite, stripping the first part of
  CREATE TABLE, and replacing it with INSERT/REPLACE statement.

- If the underlying table is a view, the statement will not insert
  anything and not be binlogged.

The transformation from CREATE to INSERT should be as follows:

  Let us have 

     CREATE TABLE IF EXISTS  () SELECT  
 
  It is converted into: 
 
     INSERT INTO () SELECT  
 
  where the list  := The existing table's rightmost N columns.
  N is the column number of the SELECT clause.

- Change to row mode if --binlog-format is MIXED, as master and
  slave always keep consistency in row mode.

Statement-based replication might go out of sync on the slave if
it just applies the two statements separately. This limitation
of 5.1 to 5.5 replication needs to be documented in the upgrade
manual. Note: the problem is not present when all servers are
5.5+.

Affected bugs
=============

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

Solution in 5.5: If the existing object is a view, nothing is 
done (except a warning). 

BUG#47442 RBR breaks on CREATE TABLE IF EXISTS  AS SELECT

Solution in 5.5: If the underlying table is a view, nothing
is written into the binary log, including CREATE TABLE ... IF EXISTS
statement itself. Note, that if the underlying table is a base table,
we currently (5.1) write CREATE TABLE IF NOT EXISTS statement
into the binary log, to make sure that the table is created
on the slave if it does not exist there. This changed in 5.5
to never binlog the statement if the underlying object exists.

BUG#49494 CREATE TABLE IF NOT EXISTS does wrong insert when view exists
with the name

If the view with the same name exists, no insertion is done.

BUG#48814 CREATE TABLE IF NOT EXISTS  SELECT does
            not fail

The statement continues to produce no error. It, however, does
not attempt an insert.

BUG#47899 CREATE TABLE...SELECT binlogged wrongly if binlog_format=row

QQ: Mats adds a solution.

BUG#39804 Failing CREATE ... SELECT doesn't appear in binary log.

This no longer leads to slave going out of sync, and thus does
not need fixing.

Not a bug:

BUG#23992 "CREATE TABLE .. IF NOT EXISTS" confusion

Two trees are created to contain all changes required by this worklog:
mysql-5.1-wl5370
mysql-trunk-wl5370

Fixes for all affected bugs will be pushed into these trees one by one,
and the trees will be handed off to System QA when coding is complete.
After QA sign-off, the changes from 5.1-wl5370 will be null-merged
into trunk-wl5370 and the trees will be pushed into the main trees.
In other words, forward-compatible replication code will only be present
in 5.1 release series.