Bug #119497 Failed if execute multiple times DDL on same column in one query

• Submitted: 3 Dec 7:58
• Reporter: Adria Lee
• Status: Open
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[3 Dec 7:58] Adria Lee

Description:
If we modify the same column multiple times in the one SQL query, we will
meet ERROR 1054 (Unknown column).

The logic of the function prepare_fields_and_keys is:

```
for (each table column) {
    for (each modified column) {
        if (cur_modified_column.field_name == cur_table_column.field_name) {
          break;
        }
    }
    do something and add to new_create_list
}
```

Therefore, for the same column, only the first one will be added.

I checked the usage manuals, and couldn't find any explanation about the
limitation of modifying the same column multiple times in one query.

How to repeat:
Execute:

```
CREATE TABLE test_inplace_8 (
    id INT PRIMARY KEY,
    old_name VARCHAR(50)
);
ALTER TABLE test_inplace_8 
MODIFY COLUMN id INT(10), 
MODIFY COLUMN id INT AUTO_INCREMENT
;
```

Then ERROR occurs:
ERROR 1054 (42S22): Unknown column 'id' in 'test_inplace_8'

Suggested fix:
If modify columns multiple times, only add the last one in new_create_list.

FYI:
+++ b/sql/sql_table.cc
@@ -16159,12 +16159,14 @@ bool prepare_fields_and_keys(THD *thd, const dd::Table *src_table, TABLE *table,
     }
     /* Check if field is changed */
     def_it.rewind();
+    bool my_found = false;
     while ((def = def_it++)) {
       if (def->change &&
-          !my_strcasecmp(system_charset_info, field->field_name, def->change))
-        break;
-    }
-    if (def) {  // Field is changed
+          !my_strcasecmp(system_charset_info, field->field_name, def->change)) {
+    //     break;
+    // }
+    // if (def) {  // Field is changed
+      my_found = true;
       def->field = field;
       def->charset = get_sql_field_charset(def, create_info);

@@ -16193,6 +16195,18 @@ bool prepare_fields_and_keys(THD *thd, const dd::Table *src_table, TABLE *table,
         Note that columns with AFTER clauses are added to the end
         of the list for now. Their positions will be corrected later.
       */
+      if (new_create_list.size()) {
+        List_iterator<Create_field> remove_it(new_create_list);
+        Create_field *del_field;
+        while ((del_field = remove_it++)) {
+          if (!my_strcasecmp(system_charset_info,
+                             del_field->field_name,
+                             def->field_name)) {
+            remove_it.remove();
+            break;
+          }
+        }
+      }
       new_create_list.push_back(def);

       /*

@@ -16212,7 +16226,10 @@ bool prepare_fields_and_keys(THD *thd, const dd::Table *src_table, TABLE *table,
         alter_ctx->error_if_not_empty |=
             Alter_table_ctx::GEOMETRY_WITHOUT_DEFAULT;
       }
-    } else {
+      }
+    }
+    // } else {
+    if (!my_found) {