ALTER COLUMN ... DROP DEFAULT incorrectly sets DEFAULT NULL on nullable columns #556

Problem

After ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT, MySQL keeps a nullable column with no default. The driver stores it as DEFAULT NULL, so SHOW CREATE TABLE and strict-mode inserts differ from MySQL.

Reproduction

CREATE TABLE t (id INT, v VARCHAR(10) DEFAULT 'x');
ALTER TABLE t ALTER COLUMN v DROP DEFAULT;
SHOW CREATE TABLE t;
INSERT INTO t (id) VALUES (1);

MySQL results are from MySQL 8.0.46 and 8.4. Driver results are from #555 at 0e17bbd0e034ed3659f35fb87db4a16755741c14, which implements DROP DEFAULT, with PHP 8.5.11 and SQLite 3.54.1.

WordPress is not affected in practice. It disables strict mode, where MySQL also stores NULL, and dbDelta() never drops defaults. The difference matters for PDO and MySQL proxy users in strict mode, and for exports made with SHOW CREATE TABLE.

Cause

A nullable column has two possible states in MySQL:

• DEFAULT NULL: Every nullable column gets this when it is created without a default.
• No default: Only DROP DEFAULT produces this.

INFORMATION_SCHEMA shows NULL for both. The driver keeps defaults only in its information schema tables, so it can't tell them apart. The SQLite table can't express the second state either, because SQLite always stores NULL in an omitted nullable column.

Direction

• Store a private "no default" flag for nullable columns, for both permanent and temporary tables. It must not appear in the emulated INFORMATION_SCHEMA tables.
• Omit DEFAULT NULL from SHOW CREATE TABLE for these columns.
• Reject strict-mode inserts that omit the column with error 1364. Also check how MySQL handles DEFAULT in VALUES, INSERT ... SELECT, and REPLACE.
• Clear the flag on SET DEFAULT, CHANGE, and MODIFY, and when the column or table is dropped. Keep it through table rebuilds.

The strict-mode table in translate_insert_or_replace_body() says that a nullable column without a default saves NULL when omitted. That describes DEFAULT NULL, not this state.

Related

• ALTER COLUMN ignores SET DEFAULT and DROP DEFAULT #553 and Add support for ALTER COLUMN with SET DEFAULT and DROP DEFAULT #555 add SET DEFAULT and DROP DEFAULT. ALTER COLUMN ignores SET DEFAULT and DROP DEFAULT #553 says MySQL gives NULL for the dropped default, which is true only in non-strict mode.
• AUTO_INCREMENT columns have a similar hidden state. MySQL hides SET DEFAULT and DROP DEFAULT on them but applies them to inserts that omit the column. Add support for ALTER COLUMN with SET DEFAULT and DROP DEFAULT #555 ignores these on purpose. The same private flag storage could support them, but that is not planned.