Repository navigation
Validate and normalize literal column defaults like MySQL #551
Description
Problem
MySQL converts a column default to the column's type when a table is created, and it rejects defaults that can't be converted. The driver stores most literal defaults exactly as written. SHOW CREATE TABLE, INFORMATION_SCHEMA.COLUMNS, and the values of new rows can then differ from MySQL, and some exports don't import into MySQL.
| Column definition | Driver | MySQL |
|---|---|---|
TIME DEFAULT '0' |
'0' |
'00:00:00' |
DATETIME DEFAULT '0' |
'0' |
Error 1067 (strict mode) |
YEAR DEFAULT '0' |
'0' |
'2000' |
DATE DEFAULT 20200102 |
'20200102' |
'2020-01-02' |
INT DEFAULT 1.0 |
'1.0' |
'1' |
INT DEFAULT 1e3 |
'1e3' |
'1000' |
INT DEFAULT 0x10 |
'0x10' |
'16' |
VARCHAR(10) DEFAULT 0x61 |
'0x61' |
'a' |
VARCHAR(10) DEFAULT _utf8mb4'abc' |
'_utf8mb4abc' |
'abc' |
The driver values are from SHOW CREATE TABLE on trunk (3.1.0).
Direction
Validate and normalize each literal default from its AST node and the full column definition, as MySQL does: convert it to the column's type, store the resulting value, and reject defaults that can't be converted with error 1067. The @TODO in get_literal_default() lists the known cases.
Related
These fix parts of this:
- Normalize BIT column default literals #439 normalized
BITdefaults, and Reject invalid BIT column defaults #487 rejects invalid ones. - SHOW CREATE TABLE quotes numeric temporal defaults (DEFAULT 0 → '0'), breaking MySQL import #541 and Normalize zero defaults of temporal columns #542 normalize numeric zero defaults of date/time columns, such as
TIMESTAMP DEFAULT 0. YEARvalues'0'and'00'are stored as0000instead of2000#545 tracksYEARstring zeros, for both defaults and inserted values.- Fix
DATE_FORMATformatting and numeric comparisons #508 adds a MySQL date parser that could convert numeric dates, such asDEFAULT 20200102.
Activity
JanJakes commentedon Oct 22, 2026
Some more cases, found while reviewing #555:
Binary columns. Literal defaults on
BINARYandVARBINARYcolumns are stored as text, so every insert that omits the column fails withcannot store TEXT value in BLOB column:The same happens with
DEFAULT 'F',DEFAULT 0x41, andDEFAULT ('F'). OnlyDEFAULT (0x41)works, because it is treated as an expression. MySQL stores the bytes and shows binary defaults in hex inINFORMATION_SCHEMA.COLUMNS(0x46for'F').Inserts with hex defaults.
INT DEFAULT 0x10doesn't only show'0x10'. Every insert that omits the column also fails withcannot store TEXT value in INTEGER column.Other literal kinds:
N'abc''N''abc''''abc'DATE '2020-01-03''DATE2020-01-03'ALTER ... SET DEFAULTALTER COLUMN. #555 records
ALTER ... SET DEFAULTwith the same literal handling, so it has the same results. Like MySQL, it treats a parenthesized constant such as(0x41)as a literal.A fix could follow the existing
BIThandling. Store binary defaults in hex and write them to SQLite asX'…'. For text columns, decode hex and bit literals to their bytes; for numeric columns, convert them to their value. Existing tables may already hold unconverted text such as0x41.