Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4440

IF NOT EXISTS in multi-action ALTER does not work when the problem is created by a previous part of the ALTER

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.0.2
    • 10.0.12
    • None
    • None

    Description

      If ALTER TABLE contains multiple parts, and one of them creates a problem for a next one, IF NOT EXISTS or IF EXISTS clause in the following one does not work, the statement still produces an error, and does not add the column at all:

      MariaDB [test]> show create table t1;
      		 
      +-------+---------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                          |
      		 
      +-------+---------------------------------------------------------------------------------------+
      		 
      | t1    | CREATE TABLE `t1` (
      		 
        `i` int(11) DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      		 
      +-------+---------------------------------------------------------------------------------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [test]> alter table t1 add column a int, add column if not exists a int;
      		 
      ERROR 1060 (42S21): Duplicate column name 'a'
      		 
       
      		 
      MariaDB [test]> show create table t1;
      		 
      +-------+---------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                          |
      		 
      +-------+---------------------------------------------------------------------------------------+
      		 
      | t1    | CREATE TABLE `t1` (
      		 
        `i` int(11) DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      		 
      +-------+---------------------------------------------------------------------------------------+
      		 
      1 row in set (0.00 sec)

      Same for DROP COLUMN.

      Test case:

      create table t1 (i int);
      		 
      alter table t1 add column a int, add column if not exists a int;
      		 
      show create table t1;
      		 

      revision-id: bar@mariadb.org-20130424142022-u4xhikvoqggze9b0
      		 
      revno: 3745
      		 
      branch-nick: 10.0

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-318 IF (NOT) EXIST clauses for ALTER TABLE (MWL #252)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik added a comment -

            let's just document that. ALTER isn't executing its clauses sequentially, it's doing everything at once. So all IF [NOT] EXISTS clauses apply to the table structure before ALTER, there's no intermediate state.

            serg Sergei Golubchik added a comment - let's just document that. ALTER isn't executing its clauses sequentially, it's doing everything at once. So all IF [NOT] EXISTS clauses apply to the table structure before ALTER, there's no intermediate state.
            holyfoot Alexey Botchkov added a comment -

            Fixing patch:
            http://lists.askmonty.org/pipermail/commits/2014-June/006190.html

            holyfoot Alexey Botchkov added a comment - Fixing patch: http://lists.askmonty.org/pipermail/commits/2014-June/006190.html

            People

              holyfoot Alexey Botchkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.