DEFAULT NULL in string column definitions becomes DEFAULT 0 on MySQL #485

With MySQL, a string column definition with DEFAULT NULL is created with DEFAULT 0 for integer columns.

ColumnDefinitionParser stores the unquoted literal NULL as defaultValueRaw = 'NULL'. The MySQL ColumnFactory overrides normalizeDefaultValue() without the NULL check of AbstractColumnFactory, so the string 'NULL' is typecast by the column and becomes 0 for int.

The override itself looks intentional. For schema read from the database, MySQL returns information_schema.COLUMNS.COLUMN_DEFAULT unquoted. For example, varchar(10) DEFAULT 'NULL' is returned as NULL (the string), and the current code correctly keeps it as the string 'NULL'. So the literal cannot simply be checked there.

This breaks the yiisoft/queue-db migration (M240409200600CreateQueueTable) on MySQL. There reserved_at, attempt and done_at are defined as 'int DEFAULT NULL' and are created with DEFAULT 0. The adapter only reserves messages with reserved_at IS NULL, so pushed messages are never processed.

Steps to reproduce

$command = $db->createCommand()->createTable('queue', [
    'id' => 'int NOT NULL AUTO_INCREMENT',
    'reserved_at' => 'int DEFAULT NULL',
    'PRIMARY KEY ([[id]])',
]);
echo $command->getRawSql();
$command->execute();

Expected result

CREATE TABLE `queue` (
	`id` int NOT NULL AUTO_INCREMENT,
	`reserved_at` int DEFAULT NULL,
	PRIMARY KEY (`id`)
)

Actual result

CREATE TABLE `queue` (
	`id` int NOT NULL AUTO_INCREMENT,
	`reserved_at` int DEFAULT 0,
	PRIMARY KEY (`id`)
)

SHOW CREATE TABLE queue then shows ``reserved_at int(11) DEFAULT '0'.

Possible fix

In a column definition, string defaults are always quoted, so an unquoted NULL can only be SQL NULL. It could be handled where the definition is parsed, in Yiisoft\Db\Syntax\ColumnDefinitionParser::extraInfo() after the DEFAULT value is read:

if (isset($info['defaultValueRaw']) && strcasecmp($info['defaultValueRaw'], 'NULL') === 0) {
    $info['defaultValueRaw'] = null;
}

This does not touch schema reading, so varchar DEFAULT 'NULL' from information_schema keeps working. Tested locally with this change (MySQL 5.7.44):

Environment