[MDEV-8960] Can't refer the same column twice in one ALTER TABLE Created: 2015-10-18  Updated: 2020-08-25  Resolved: 2017-08-18

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.0.21
Fix Version/s: 5.5.58, 10.0.33, 10.1.27, 10.2.9, 10.3.2

Type: Bug Priority: Major
Reporter: Denis Malinovskiy Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 3
Labels: upstream
Environment:

Linux


 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



 Comments   
Comment by Elena Stepanova [ 2015-10-20 ]

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?

Comment by Denis Malinovskiy [ 2015-10-20 ]

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.

Спасибо!

Comment by Elena Stepanova [ 2015-10-20 ]

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

Comment by Jan Lindström (Inactive) [ 2017-07-27 ]

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

Comment by Oleksandr Byelkin [ 2017-08-04 ]

OK to push!

Comment by Jan Lindström (Inactive) [ 2017-08-18 ]

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.

Generated at Thu Feb 08 07:31:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.