ALTER COLUMN ignores SET DEFAULT and DROP DEFAULT #553

Reproduction

CREATE TABLE t (id INT, n INT NOT NULL DEFAULT 1, v VARCHAR(10) DEFAULT 'x');

ALTER TABLE t ALTER COLUMN n SET DEFAULT 5;
ALTER TABLE t ALTER COLUMN v DROP DEFAULT;

INSERT INTO t (id) VALUES (1);
SELECT n, v FROM t;
-- 1, 'x'   (MySQL: 5, NULL)

Both ALTER TABLE statements succeed without an error, but nothing changes. SHOW CREATE TABLE and INFORMATION_SCHEMA.COLUMNS still report DEFAULT '1' and DEFAULT 'x'.

Reproduced on trunk at 09963132adefd68dde404959d393aa2200066d44, with PHP 8.5 and SQLite 3.54.1.

Cause

record_alter_table() handles ADD, CHANGE, MODIFY, and DROP actions, but not ALTER [COLUMN] ... SET DEFAULT and DROP DEFAULT. The information schema stays unchanged, and execute_alter_table_statement() then rebuilds the table from it. So the statement copies the whole table, but the table keeps its old defaults.

A possible fix is to record the new default in record_alter_table(), using the same default handling as column definitions, and NULL for DROP DEFAULT.

Related

• SHOW CREATE TABLE quotes numeric temporal defaults (DEFAULT 0 → '0'), breaking MySQL import #541 and Normalize zero defaults of temporal columns #542 normalize zero defaults of date/time columns in column definitions. ALTER COLUMN created_at SET DEFAULT 0 doesn't reach that code.
• The unnecessary rebuild also has the side effects of ALTER TABLE and OPTIMIZE TABLE reset the AUTO_INCREMENT counter #544 and ALTER TABLE drops legacy ON UPDATE CURRENT_TIMESTAMP triggers #547.