MySQL Bugs: #111702: Alter default value with auto_increment column not report error

Bug #111702	Alter default value with auto_increment column not report error
Submitted:	10 Jul 2023 2:31	Modified:	12 Jul 2023 9:36
Reporter:	hangjie Mo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.33	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
```
mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` bigint NOT NULL AUTO_INCREMENT,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table t alter column a set default 3;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

How to repeat:
see description

Suggested fix:
report error as modify column below

```
mysql> alter table t modify column a bigint auto_increment default 3;
ERROR 1067 (42000): Invalid default value for 'a'
```

Hello, 
I would like to add some details before the verification team takes a look at it.
I was curious, so I tried it (my table name is `t5`).
I created is as described, then I added some records:
SQL> select * from t5;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+
3 rows in set (0.0006 sec)

Did the alter to add the default to 3 and then tried to insert data:

SQL> insert into t5 (b) values (4);
ERROR: 1062 (23000): Duplicate entry '3' for key 't5.PRIMARY'

But the biggest surprise is:

SQL> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `a` bigint NOT NULL AUTO_INCREMENT,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0008 sec)

Where is the default ?

SQL> select * from information_schema.columns 
     where table_name='t5' and column_name='a'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: test
              TABLE_NAME: t5
             COLUMN_NAME: a
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 19
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: bigint
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
   GENERATION_EXPRESSION: 
                  SRS_ID: NULL

If I create the sdi file for the table (ibd2sid), we can see for column a: 

        "columns": [
            {
                "name": "a",
                "type": 9,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": true,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 1,
                "char_length": 20,
                "numeric_precision": 19,
                "numeric_scale": 0,
                "numeric_scale_null": false,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": false,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "AwAAAAAAAAA=",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "table_id=1596;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 2,
                "column_type_utf8": "bigint",
                "elements": [],
                "collation_id": 8,
                "is_explicit_collation": false
            },

Here the default value is stored.

Hi,

Thank you for the report. I'm not sure it is a bug per se as that will set default value even if it does not make much sense to do so. I need to check with SQL standard what should be the norm for such case.

The problem is that this doesn't behave the same as modify column which not support auto_increment with default value.

```
mysql> alter table t modify column a bigint auto_increment default 3;
ERROR 1067 (42000): Invalid default value for 'a'
```

Hi,

yes, I agree there is a bug here somewhere, just need to figure out what should be the correct behavior.