Description:
On MySQL 9.7.0 with sql_mode='ALLOW_INVALID_DATES', several date functions wrongly return NULL (with Warning 1292 'Incorrect datetime value') for a calendar-invalid but well-formed date such as '2024-02-30', even though ALLOW_INVALID_DATES is documented to accept month-in-1..12 / day-in-1..31 dates. 9.6.0 computes the correct values. Affected: TIMESTAMPDIFF, DAYOFYEAR, WEEKDAY, DAYOFWEEK, WEEK, YEARWEEK (and DAYNAME). Sibling functions TO_DAYS, DATEDIFF, DATE(), MONTH(), DAY() correctly accept the same value in the same session, which makes the result self-contradictory.
Regression introduced by WL#16895 "Refactor DATE handling in server" (commit 876fd7ffe0db, 2026-02-17). These functions pass the hardcoded flag constant TIME_ONLY_VALID_DATES to value extraction:
sql/item_timefunc.cc:3865-3866 Item_func_timestamp_diff::val_int -> val_datetime(..., TIME_ONLY_VALID_DATES)
sql/item_timefunc.cc:1436 Item_func_dayofyear::val_int -> val_arg0_date(..., TIME_ONLY_VALID_DATES)
sql/item_timefunc.cc:1867 Item_func_weekday::val_int (also DAYOFWEEK)
sql/item_timefunc.cc:1817 Item_func_week::val_int
sql/item_timefunc.cc:1841 Item_func_yearweek::val_int
sql/item_timefunc.cc:1888 Item_func_dayname::val_str
TIME_ONLY_VALID_DATES (include/my_time.h:111) bundles TIME_NO_INVALID_DATES, which forces check_date() (mysys/my_time.cc:204) to reject calendar-invalid dates regardless of sql_mode. The correct, sql_mode-gated idiom is used elsewhere, e.g. sql/item.cc:441, sql/sql_time.cc:202, sql/item_timefunc.cc:4059 (include TIME_NO_INVALID_DATES only when MODE_INVALID_DATES is NOT set). TO_DAYS/DATEDIFF still work because they use TIME_NO_ZERO_DATE|TIME_NO_ZERO_IN_DATE (no TIME_NO_INVALID_DATES), which is what exposes the contradiction.
WL#16895's own retained test mysql-test/r/type_date.result (Bug#20583945) asserts that under ALLOW_INVALID_DATES such dates are processed (DATE / DATE_FORMAT / +1 preserve '2014-02-31'), confirming the correct behavior is to compute, not NULL.
The buggy flags are still present at the current 9.7 branch HEAD, so this still ships in 9.7.1.
Thank you,
Yakir Gibraltar
How to repeat:
SET sql_mode='ALLOW_INVALID_DATES';
SELECT TIMESTAMPDIFF(DAY,'2024-02-30','2024-03-15') AS tsdiff, -- 9.7.0: NULL 9.6.0: 14
DAYOFYEAR('2024-02-30') AS doy, -- 9.7.0: NULL 9.6.0: 61
WEEKDAY('2024-02-30') AS wd, -- 9.7.0: NULL 9.6.0: 4
DATEDIFF('2024-03-15','2024-02-30') AS ctrl_dd, -- both: 14 (control)
TO_DAYS('2024-02-30') AS ctrl_td; -- both: 739311 (control)
-- Internal-consistency oracle (proves 9.7.0 is self-contradictory within one session/mode,
-- independent of comparing to 9.6.0): TO_DAYS, DATEDIFF, DATE('2024-02-30'), MONTH, DAY all
-- accept '2024-02-30', and the identity TIMESTAMPDIFF(DAY,b,a) = TO_DAYS(a)-TO_DAYS(b) = 14
-- must hold -- yet TIMESTAMPDIFF/DAYOFYEAR/WEEKDAY return NULL.
Suggested fix:
In these val_int/val_str functions, replace the hardcoded TIME_ONLY_VALID_DATES with the sql_mode-gated flag used at sql/item.cc:441 and sql/item_timefunc.cc:4059 -- i.e. include TIME_NO_INVALID_DATES only when MODE_INVALID_DATES is not set -- so ALLOW_INVALID_DATES is honored consistently with TO_DAYS / DATEDIFF / DATE().
