Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.21
-
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
|
Attachments
Issue Links
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
I have a simple table with the following structure: {code:sql} CREATE TABLE `test` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 {code} When I'm trying to run following statement: {code:sql} ALTER TABLE `test` ADD COLUMN `consultant_id` integer NOT NULL, ALTER COLUMN `consultant_id` DROP DEFAULT; {code} I've got an error: {noformat} ERROR 1054 (42S22): Unknown column 'consultant_id' in 'test' {noformat} However when I'm splitting these changes into two separate {{ALTER TABLE}}s, it works: {code:sql} 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 {code} |
I have a simple table with the following structure: {code:sql} CREATE TABLE `test` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 {code} When I'm trying to run following statement: {code:sql} ALTER TABLE `test` ADD COLUMN `consultant_id` integer NOT NULL, ALTER COLUMN `consultant_id` DROP DEFAULT; {code} I've got an error: {noformat} ERROR 1054 (42S22): Unknown column 'consultant_id' in 'test' {noformat} However when I'm splitting these changes into two separate {{ALTER TABLE}}, it works: {code:sql} 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 {code} |
| Labels | upstream |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
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.
Спасибо! 
Actually, your report already had all details, but no problem, filed as http://bugs.mysql.com/bug.php?id=78891.
| Remote Link | This issue links to "Bug #78891 Can't refer the same column twice in one ALTER TABLE: ER_BAD_FIELD_ERROR (Web Link)" [ 25505 ] |
| Fix Version/s | 10.1 [ 16100 ] |
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Assignee | Jan Lindström [ jplindst ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Jan Lindström [ jplindst ] | Oleksandr Byelkin [ sanja ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Oleksandr Byelkin [ sanja ] |
| Assignee | Jan Lindström [ jplindst ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
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.
| issue.field.resolutiondate | 2017-08-18 08:41:19.0 | 2017-08-18 08:41:19.267 |
| Fix Version/s | 10.0.33 [ 22552 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Fix Version/s | 10.1.27 [ 22609 ] | |
| Fix Version/s | 10.2.9 [ 22611 ] | |
| Fix Version/s | 10.3.2 [ 22533 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Fix Version/s | 5.5.58 [ 22540 ] |
| Workflow | MariaDB v3 [ 72145 ] | MariaDB v4 [ 149721 ] |
| Zendesk Related Tickets | 130492 |
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?