MySQL Bugs: #55617: Irrelevant error on attempt to override an existing view by CREATE IF NOT EXISTS

Bug #55617	Irrelevant error on attempt to override an existing view by CREATE IF NOT EXISTS
Submitted:	28 Jul 2010 21:48	Modified:	8 Sep 2010 2:10
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	trunk-wl5370	OS:	Any
Assigned to:	Libing Song	CPU Architecture:	Any

Description:
If a view v already exists and reads from a table t, and CREATE TABLE IF NOT EXISTS v AS SELECT * FROM t is attempted, it fails with ER_VIEW_PREVENT_UPDATE. It does not seem reasonable with the new logic, since the insert is not going to happen anyway; also, it contradicts the WL#5370 spec:
<quote>
Init: CREATE an updatable view t1.
- verify that CREATE TABLE IF NOT EXISTS t1 ... SELECT
produces ER_TABLE_EXISTS_ERROR and otherwise has no effect
</quote>

It happens with old 5.5 and 5.1, but there it is understandable as it prevents the INSERT which would otherwise be executed; does not happen on 5.1-wl5370, INSERT is not done there now, although it might change (bug#55474 seems to be related).

How to repeat:
--disable_warnings
DROP TABLE IF EXISTS t;
DROP VIEW IF EXISTS v;

CREATE TABLE t ( i INT );
CREATE VIEW v AS SELECT * FROM t;
CREATE TABLE IF NOT EXISTS v AS SELECT * FROM t;

DROP TABLE t;
DROP VIEW v;

--exit

To add here-
- CREATE TABLE..SELECT should give ER_TABLE_EXISTS_ERROR.
- CREATE TABLE IF NOT EXISTS..SELECT should produce the warning.

wl5370 spec. should also be corrected here as below-
Init: CREATE an updatable view t1.
- verify that CREATE TABLE t1 ... SELECT   --update here
produces ER_TABLE_EXISTS_ERROR and otherwise has no effect
- CREATE TABLE IF NOT EXISTS t1 ... SELECT
produces a warning and does not insert into the view.

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/114821

3080 Li-Bing.Song@sun.com	2010-08-02
      Bug#55617 Irrelevant error on attempt to override an existing view 
                by CREATE IF NOT EXISTS
      
      If a view v already exists and reads from a table t, and CREATE TABLE IF NOT EXISTS v AS
      SELECT * FROM t is attempted, it fails with ER_VIEW_PREVENT_UPDATE. It is not
      reasonable with the new logic, since the insert is not going to happen anyway
      
      After this patch, ER_VIEW_PREVENT_UPDATE is replaced by ER_TABLE_EXISTS_ERROR.
     @ mysql-test/t/create.test
        After WL#5370, the statement just generates a warning that the table already exists
     @ mysql-test/t/merge.test
        After WL#5370, the error ER_UPDATE_TABLE_USED will never appear
        on 'CREATE TABLE IF NOT EXISTS ... SELECT' statement. So this test
        for bug#15522 is removed.
     @ mysql-test/t/union.test
        After WL#5370, the statement just generates a warning that the table already exists
     @ sql/sql_parse.cc
        After WL#5370, it doesn't need to check whether the creating table is
        appearing in SELECT clause. As the statement will end immediately with
        an error or a warning if it 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/114826

3080 Li-Bing.Song@sun.com	2010-08-02
      Bug#55617 Irrelevant error on attempt to override an existing view 
                by CREATE IF NOT EXISTS
      
      If a view v already exists and reads from a table t, and CREATE TABLE IF NOT EXISTS v AS
      SELECT * FROM t is attempted, it fails with ER_VIEW_PREVENT_UPDATE. It is not
      reasonable with the new logic, since the insert is not going to happen anyway
      
      After this patch, ER_VIEW_PREVENT_UPDATE is replaced by ER_TABLE_EXISTS_ERROR.
     @ mysql-test/t/create.test
        After WL#5370, the statements just generates a warning or an error
        that  ER_TABLE_EXISTS_ERROR instead of ER_UPDATE_TABLE_USED.
     @ mysql-test/t/merge.test
        After WL#5370, the statement just generates a warning that the table already exists
     @ mysql-test/t/union.test
        After WL#5370, the statement generates the ER_TABLE_EXISTS_ERROR error.
     @ sql/sql_parse.cc
        After WL#5370, it doesn't need to check whether the creating table is
        appearing in SELECT clause. As the statement will end immediately with
        an error or a warning if it 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/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 was fixed in the final patch for WL#5370.

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)

Bug does not appear in any released version. No changelog entry needed.