YEAR values '0' and '00' are stored as 0000 instead of 2000 #545

Reproduction

CREATE TABLE t (y YEAR);
INSERT INTO t VALUES (0), ('0'), ('00'), (5), ('5');

SELECT y FROM t;
-- 0000, 0000, 0000, 2005, 2005   (MySQL: 0000, 2000, 2000, 2005, 2005)

UPDATE t SET y = '0' stores 0000 as well. Defaults with these strings are stored verbatim:

CREATE TABLE d (id INT, y YEAR NOT NULL DEFAULT '0');
INSERT INTO d (id) VALUES (1);

SELECT y FROM d;
-- '0'   (MySQL: 2000)

Reproduced on trunk (3.1.0) and with #542, with PHP 8.5 and SQLite 3.54.1.

In MySQL, a numeric 0 of a YEAR column is the year 0000, but the strings '0' and '00' are the year 2000 (docs). Other 1- and 2-digit values, such as 5 and '5', are the same year in both forms.

Cause

• Inserted and updated values: cast_value_for_saving() converts YEAR values with CAST(value AS INTEGER) and maps 0 to '0000'. The cast loses whether the value was a string, so '0' and '00' become 0000.
• Defaults: the information schema builder stores YEAR string defaults verbatim. Normalize zero defaults of temporal columns #542 keeps them that way, so that defaults stay consistent with inserted values until both are fixed.

A possible fix is to check whether the value is text, e.g., with typeof(), before the cast, and to store the string defaults '0' and '00' as '2000'.

Related

Driver 3.1.0 and older stored both YEAR DEFAULT 0 and YEAR DEFAULT '0' as '0', so these stored defaults can't be told apart and fixed later.