Bug #118547 SET DEFAULT (expr) on PK column requires issuing command twice

• Submitted: 27 Jun 17:26
• Modified: 30 Jun 6:36
• Reporter: Duke Lee
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 8.0, 8.4, 8.0.42
• OS: Any
• CPU Architecture: Any

[27 Jun 17:26] Duke Lee

Description:
1) If a PK column is created with no default value and is later altered to have a computed default value from an expression, the ALTER command needs to be run twice for the change to "stick." The first issued command will NOT set the default (Default is null, but Extra shows DEFAULT_GENERATED.) The 2nd command will set the default to the specified expression.

2) If a PK column is created with a default literal value and is later altered to have a computed default value from an expression, the first issued command will set the default to NULL (Extra shows DEFAULT_GENERATED.) The 2nd command will set the default to the specified expression.

3) If a PK column is created with a default expression and is later altered to have a different expression, the ALTER command only needs to be issued once as it should.

How to repeat:
-- Example for above Scenario #1:

CREATE TABLE scenario1 (
  id INT NOT NULL PRIMARY KEY
);

ALTER TABLE scenario1 ALTER id SET DEFAULT (1+1);

DESCRIBE scenario1; -- Default should be NULL, DEFAULT_GENERATED
+-------+------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra             |
+-------+------+------+-----+---------+-------------------+
| id    | int  | NO   | PRI | NULL    | DEFAULT_GENERATED |
+-------+------+------+-----+---------+-------------------+

ALTER TABLE scenario1 ALTER id SET DEFAULT (1+1); -- Run it a 2nd time

DESCRIBE scenario1; -- Default should now be (1+1)
+-------+------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra             |
+-------+------+------+-----+---------+-------------------+
| id    | int  | NO   | PRI | (1 + 1) | DEFAULT_GENERATED |
+-------+------+------+-----+---------+-------------------+

-- End exmaple for Scenario #1

-- Example for Scenario #2:

CREATE TABLE scenario2 (
  id INT NOT NULL PRIMARY KEY DEFAULT 1
);

ALTER TABLE scenario2 ALTER id SET DEFAULT (1+1);

DESCRIBE scenario2; -- Default is now NULL, DEFAULT_GENERATED
+-------+------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra             |
+-------+------+------+-----+---------+-------------------+
| id    | int  | NO   | PRI | NULL    | DEFAULT_GENERATED |
+-------+------+------+-----+---------+-------------------+

ALTER TABLE scenario2 ALTER id SET DEFAULT (1+1); -- Run again

DESCRIBE scenario2; -- Default is now (1+1)
+-------+------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra             |
+-------+------+------+-----+---------+-------------------+
| id    | int  | NO   | PRI | (1 + 1) | DEFAULT_GENERATED |
+-------+------+------+-----+---------+-------------------+

-- End example for Scenario #2

Suggested fix:
ALTER TABLE tbl_name ALTER [COLUMN] col_name SET DEFAULT ... should only need to be issued once.

The workaround is to set the default expression via MODIFY instead:

ALTER TABLE tbl_name MODIFY [COLUMN] col_name ...

[27 Jun 17:33] Duke Lee

Ah, some clarification from further testing:

Issuing the first ALTER will set the DDL's default to NULL w/ Extra DEFAULT_GENERATED, so SHOW CREATE TABLE and DESCRIBE will not show the default expression. HOWEVER, inserting a row will actually run the default expression even though it's not reflected in the DDL:

CREATE TABLE test (
  id INT NOT NULL PRIMARY KEY
);

ALTER TABLE test ALTER id SET DEFAULT (1+1);

DESCRIBE test;
+-------+------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra             |
+-------+------+------+-----+---------+-------------------+
| id    | int  | NO   | PRI | NULL    | DEFAULT_GENERATED |
+-------+------+------+-----+---------+-------------------+

INSERT INTO test VALUES ();

SELECT * FROM test;
+----+
| id |
+----+
|  2 |
+----+

DESCRIBE test; -- Still NULL
+-------+------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra             |
+-------+------+------+-----+---------+-------------------+
| id    | int  | NO   | PRI | NULL    | DEFAULT_GENERATED |
+-------+------+------+-----+---------+-------------------+

Needless to say, "something's not right." :)

[30 Jun 6:36] MySQL Verification Team

Hello Duke Lee,

Thank you for the report and test case.

regards,
Umesh