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
Triage: Triaged: D4 (Minor)

[28 Jul 2010 21:48] Elena Stepanova
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
[30 Jul 2010 10:01] Nidhi Shrotriya
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.
[2 Aug 2010 10:10] Bugs System
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.
[2 Aug 2010 10:59] Bugs System
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.
[18 Aug 2010 9:27] Bugs System
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.
[18 Aug 2010 9:38] Bugs System
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.
[19 Aug 2010 7:45] Libing Song
It was fixed in the final patch for WL#5370.
[25 Aug 2010 9:22] Bugs System
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)
[30 Aug 2010 8:30] Bugs System
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)
[30 Aug 2010 8:34] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[8 Sep 2010 2:10] Paul Dubois
Bug does not appear in any released version. No changelog entry needed.