Bug #117725 Error occurs when modifying the same column twice in a DDL

• Submitted: 17 Mar 6:25
• Modified: 17 Mar 6:45
• Reporter: Huaxiong Song (OCA)
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[17 Mar 6:25] Huaxiong Song

Description:
As the title says, an error occurs when modifying the same column twice in a DDL: ERROR 1054 (42S22): Unknown column ...

How to repeat:
Run follow test case in MTR:

CREATE TABLE t(a INT, b INT);

# Error occurs when modifying the same column twice in a DDL, such as:
--error ER_BAD_FIELD_ERROR
ALTER TABLE t MODIFY COLUMN a INT, MODIFY COLUMN a INT;
--error ER_BAD_FIELD_ERROR
ALTER TABLE t ALTER COLUMN a SET DEFAULT 10, ALTER COLUMN a DROP DEFAULT;
--error ER_BAD_FIELD_ERROR
ALTER TABLE t ALTER COLUMN a SET DEFAULT 10, RENAME COLUMN a to c;

DROP TABLE t;

Suggested fix:
In `prepare_fields_and_keys`, when traversing fields, multiple occurrences of the same column should be handled

[17 Mar 6:45] MySQL Verification Team

Hello Huaxiong Song,

Thank you for the report and test case.

regards,
Umesh

[2 Jul 9:10] Li Yirong

CREATE TABLE t1(id INT KEY, c1 INT);

# this should be accepted
--error ER_BAD_FIELD_ERROR
ALTER TABLE t1 MODIFY c1 BIGINT, RENAME c1 TO c2;

# works
ALTER TABLE t1 CHANGE c1 c2 BIGINT;

# this is unacceptable 
--error ER_BAD_FIELD_ERROR
ALTER TABLE t1 MODIFY c1 TINYINT, MODIFY c1 BIGINT;

One `Field` may altered by many `Create_field`, this while-loop in `prepare_fields_and_keys` should be enlarged and some `Create_field` should be composed.

```
    while ((def = def_it++)) {
      if (def->change &&
          !my_strcasecmp(system_charset_info, field->field_name, def->change))
        break;
    }
    if (def) {  // Field is changed
      def->field = field;
      def->charset = get_sql_field_charset(def, create_info);
......
   }
```