Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
-
None
-
Linux x86_64
Description
Adding a nullable column and dropping its explicit default in the same ALTER TABLE statement leaves different metadata from performing the same operations separately. The combined form omits DEFAULT NULL and causes warning 1364 when DEFAULT is inserted, although the resulting value is still NULL.
This occurs when a nullable column is added and its explicit default is dropped within the same ALTER TABLE statement.
Run the following on MariaDB 12.3.3:
CREATE DATABASE defaults_test; |
USE defaults_test; |
SET SESSION sql_mode='STRICT_ALL_TABLES'; |
|
|
CREATE TABLE combined (id INT) ENGINE=InnoDB; |
CREATE TABLE split (id INT) ENGINE=InnoDB; |
|
|
ALTER TABLE combined |
ADD COLUMN a INT DEFAULT 9, |
ALTER COLUMN a DROP DEFAULT; |
|
|
ALTER TABLE split ADD COLUMN a INT DEFAULT 9; |
ALTER TABLE split ALTER COLUMN a DROP DEFAULT; |
|
|
SELECT 'metadata_equal' AS result, |
(c.COLUMN_DEFAULT <=> s.COLUMN_DEFAULT)
|
AND (c.IS_NULLABLE = s.IS_NULLABLE) AS pass |
FROM information_schema.COLUMNS AS c |
JOIN information_schema.COLUMNS AS s |
ON c.TABLE_SCHEMA = s.TABLE_SCHEMA |
AND c.COLUMN_NAME = s.COLUMN_NAME |
WHERE c.TABLE_SCHEMA = 'defaults_test' |
AND c.TABLE_NAME = 'combined' |
AND s.TABLE_NAME = 'split' |
AND c.COLUMN_NAME = 'a'; |
|
|
SHOW CREATE TABLE combined; |
SHOW CREATE TABLE split; |
|
|
INSERT IGNORE INTO combined VALUES (1, DEFAULT); |
SHOW WARNINGS;
|
INSERT IGNORE INTO split VALUES (1, DEFAULT); |
SHOW WARNINGS;
|
|
|
SELECT 'combined_is_null' AS result, a IS NULL AS pass FROM combined; |
SELECT 'split_is_null' AS result, a IS NULL AS pass FROM split; |
|
|
DROP DATABASE defaults_test; |
The recorded result was:
result pass
|
metadata_equal 0
|
|
|
combined CREATE TABLE `combined` (
|
`id` int(11) DEFAULT NULL,
|
`a` int(11)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
|
split CREATE TABLE `split` (
|
`id` int(11) DEFAULT NULL,
|
`a` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
|
Level Code Message
|
Warning 1364 Field 'a' doesn't have a default value
|
|
|
result pass
|
combined_is_null 1
|
split_is_null 1
|
The combined and split forms describe the same nullable column after its explicit default is removed. They should therefore produce equal COLUMN_DEFAULT and IS_NULLABLE metadata, retain the same implicit NULL default in SHOW CREATE TABLE, and produce identical diagnostics when inserting DEFAULT. Instead, only the combined form loses DEFAULT NULL and reports that the nullable column has no default value.