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

Can't refer the same column twice in one ALTER TABLE

Details

    Description

      I have a simple table with the following structure:

      CREATE TABLE `test` (
      		 
        `a` int(11) DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8

      When I'm trying to run following statement:

      ALTER TABLE `test` ADD COLUMN `consultant_id` integer NOT NULL,
      		 
      ALTER COLUMN `consultant_id` DROP DEFAULT;

      I've got an error:

      ERROR 1054 (42S22): Unknown column 'consultant_id' in 'test'

      However when I'm splitting these changes into two separate ALTER TABLE, it works:

      MariaDB> ALTER TABLE `test` ADD COLUMN `consultant_id` integer NOT NULL;
      		 
      Query OK, 0 rows affected (0.01 sec)
      		 
      Records: 0  Duplicates: 0  Warnings: 0
      		 
      MariaDB> ALTER TABLE `test` ALTER COLUMN `consultant_id` DROP DEFAULT;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
      Records: 0  Duplicates: 0  Warnings: 0

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          Thanks for the report.

          It's an upstream issue present in all MySQL versions from 5.1 to 5.7 (I didn't check 5.0 and earlier), and correspondingly in all MariaDB versions from 5.1 to 10.1.

          I imagine it must be either a known bug, or a feature, but I failed to find it either in documentation or in MySQL bug base.

          dmalinovsky, our normal routine is to also report upstream problems at bugs.mysql.com, are you willing to do it, or do we need to report it on your behalf?

          elenst Elena Stepanova added a comment - Thanks for the report. It's an upstream issue present in all MySQL versions from 5.1 to 5.7 (I didn't check 5.0 and earlier), and correspondingly in all MariaDB versions from 5.1 to 10.1. I imagine it must be either a known bug, or a feature, but I failed to find it either in documentation or in MySQL bug base. dmalinovsky , our normal routine is to also report upstream problems at bugs.mysql.com, are you willing to do it, or do we need to report it on your behalf?
          dmalinovsky Denis Malinovskiy added a comment - - edited

          elenst, if you can report it on my behalf, please go for it—I'm not sure my report will be as detailed and correct.

          Спасибо!

          dmalinovsky Denis Malinovskiy added a comment - - edited elenst , if you can report it on my behalf, please go for it—I'm not sure my report will be as detailed and correct. Спасибо!
          elenst Elena Stepanova added a comment -

          Actually, your report already had all details, but no problem, filed as http://bugs.mysql.com/bug.php?id=78891.

          elenst Elena Stepanova added a comment - Actually, your report already had all details, but no problem, filed as http://bugs.mysql.com/bug.php?id=78891 .
          jplindst Jan Lindström (Inactive) added a comment -

          https://github.com/mariadb/server/commit/4c26b71c77d58fbeb3192e2142ef66efe5f122dd

          jplindst Jan Lindström (Inactive) added a comment - https://github.com/mariadb/server/commit/4c26b71c77d58fbeb3192e2142ef66efe5f122dd
          sanja Oleksandr Byelkin added a comment -

          OK to push!

          sanja Oleksandr Byelkin added a comment - OK to push!
          jplindst Jan Lindström (Inactive) added a comment -

          commit ce6c0e584e35b516297f2afdaea5b31e508b7570
          Author: Jan Lindström <jan.lindstrom@mariadb.com>
          Date: Thu Jul 27 13:17:13 2017 +0300

          MDEV-8960: Can't refer the same column twice in one ALTER TABLE

          Problem was that if column was created in alter table when
          it was refered again it was not tried to find from list
          of current columns.

          mysql_prepare_alter_table:
          There is two cases
          (1) If alter table adds a new column and then later alter
          changes the field definition, there was no check from
          list of new columns, instead an incorrect error was given.
          (2) If alter table adds a new column and then later alter
          changes the default, there was no check from list of
          new columns, instead an incorrect error was given.

          jplindst Jan Lindström (Inactive) added a comment - commit ce6c0e584e35b516297f2afdaea5b31e508b7570 Author: Jan Lindström <jan.lindstrom@mariadb.com> Date: Thu Jul 27 13:17:13 2017 +0300 MDEV-8960 : Can't refer the same column twice in one ALTER TABLE Problem was that if column was created in alter table when it was refered again it was not tried to find from list of current columns. mysql_prepare_alter_table: There is two cases (1) If alter table adds a new column and then later alter changes the field definition, there was no check from list of new columns, instead an incorrect error was given. (2) If alter table adds a new column and then later alter changes the default, there was no check from list of new columns, instead an incorrect error was given.

          People

            jplindst Jan Lindström (Inactive)
            dmalinovsky Denis Malinovskiy
            Votes:
            3 Vote for this issue
            Watchers:
            8 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.