Description:
MySQL 9.7.0: a nested DATE_ADD/DATE_SUB whose inner argument is itself a temporal function applied to a DATE string renders a spurious "00:00:00" time component. The result string becomes "2024-03-01 00:00:00" (length 19) instead of the date "2024-03-01" (length 10). A single (non-nested) DATE_ADD on the same string is unaffected.
Impact: string comparison against the value then fails, so predicates like "WHERE col = DATE_ADD(DATE_ADD(...))" silently return zero rows (data loss in WHERE / JOIN / IN). Substituting the value-equal literal '2024-03-01' for the inner expression changes the outer result -- a referential-transparency violation, wrong regardless of the metadata type.
This is a regression: the result is correct ("2024-03-01", length 10) on MySQL 9.6.0.
Likely root cause: sql/item_timefunc.cc, Item_func_add_interval::eval_datetime(). For a STRING-typed result, val_str routes through eval_datetime() / my_TIME_to_str(). When args[0] is itself a nested add_interval, its inner value carries time_type = MYSQL_TIMESTAMP_DATETIME instead of MYSQL_TIMESTAMP_DATE, and the DATE->DATETIME normalization guard ("if (data_type()==MYSQL_TYPE_DATETIME && dt->time_type==MYSQL_TIMESTAMP_DATE)") does not cover the VARCHAR-result path, so the formatter emits the 19-char datetime. git blame points to commit 876fd7ffe0d (WL#16895: Refactor DATE handling in server).
How to repeat:
SELECT VERSION(); -- 9.7.0
-- Scalar: spurious time component appears only on 9.7.0
SELECT DATE_ADD(DATE_ADD('2024-01-31', INTERVAL 1 MONTH), INTERVAL 1 DAY) AS v,
LENGTH(DATE_ADD(DATE_ADD('2024-01-31', INTERVAL 1 MONTH), INTERVAL 1 DAY)) AS len;
-- 9.7.0 -> '2024-03-01 00:00:00', 19
-- 9.6.0 -> '2024-03-01', 10
-- Row loss in an ordinary WHERE filter
CREATE TABLE events(id INT, label VARCHAR(32));
INSERT INTO events VALUES (1, '2024-03-01');
SELECT id FROM events
WHERE label = DATE_ADD(DATE_ADD('2024-01-31', INTERVAL 1 MONTH), INTERVAL 1 DAY);
-- 9.7.0 -> 0 rows (row silently lost); 9.6.0 -> 1
-- Control: a single (non-nested) DATE_ADD is correct on both engines
SELECT DATE_ADD('2024-02-29', INTERVAL 1 DAY); -- '2024-03-01' (length 10)
Suggested fix:
In sql/item_timefunc.cc, Item_func_add_interval, make the STRING/VARCHAR-result rendering path emit date-only text (no 00:00:00) when the computed value is a DATE: have the nested add_interval propagate time_type = MYSQL_TIMESTAMP_DATE rather than MYSQL_TIMESTAMP_DATETIME, or extend the DATE->DATETIME normalization guard in eval_datetime() so it also governs the val_str path. Regressed in commit 876fd7ffe0d (WL#16895).
Description: MySQL 9.7.0: a nested DATE_ADD/DATE_SUB whose inner argument is itself a temporal function applied to a DATE string renders a spurious "00:00:00" time component. The result string becomes "2024-03-01 00:00:00" (length 19) instead of the date "2024-03-01" (length 10). A single (non-nested) DATE_ADD on the same string is unaffected. Impact: string comparison against the value then fails, so predicates like "WHERE col = DATE_ADD(DATE_ADD(...))" silently return zero rows (data loss in WHERE / JOIN / IN). Substituting the value-equal literal '2024-03-01' for the inner expression changes the outer result -- a referential-transparency violation, wrong regardless of the metadata type. This is a regression: the result is correct ("2024-03-01", length 10) on MySQL 9.6.0. Likely root cause: sql/item_timefunc.cc, Item_func_add_interval::eval_datetime(). For a STRING-typed result, val_str routes through eval_datetime() / my_TIME_to_str(). When args[0] is itself a nested add_interval, its inner value carries time_type = MYSQL_TIMESTAMP_DATETIME instead of MYSQL_TIMESTAMP_DATE, and the DATE->DATETIME normalization guard ("if (data_type()==MYSQL_TYPE_DATETIME && dt->time_type==MYSQL_TIMESTAMP_DATE)") does not cover the VARCHAR-result path, so the formatter emits the 19-char datetime. git blame points to commit 876fd7ffe0d (WL#16895: Refactor DATE handling in server). How to repeat: SELECT VERSION(); -- 9.7.0 -- Scalar: spurious time component appears only on 9.7.0 SELECT DATE_ADD(DATE_ADD('2024-01-31', INTERVAL 1 MONTH), INTERVAL 1 DAY) AS v, LENGTH(DATE_ADD(DATE_ADD('2024-01-31', INTERVAL 1 MONTH), INTERVAL 1 DAY)) AS len; -- 9.7.0 -> '2024-03-01 00:00:00', 19 -- 9.6.0 -> '2024-03-01', 10 -- Row loss in an ordinary WHERE filter CREATE TABLE events(id INT, label VARCHAR(32)); INSERT INTO events VALUES (1, '2024-03-01'); SELECT id FROM events WHERE label = DATE_ADD(DATE_ADD('2024-01-31', INTERVAL 1 MONTH), INTERVAL 1 DAY); -- 9.7.0 -> 0 rows (row silently lost); 9.6.0 -> 1 -- Control: a single (non-nested) DATE_ADD is correct on both engines SELECT DATE_ADD('2024-02-29', INTERVAL 1 DAY); -- '2024-03-01' (length 10) Suggested fix: In sql/item_timefunc.cc, Item_func_add_interval, make the STRING/VARCHAR-result rendering path emit date-only text (no 00:00:00) when the computed value is a DATE: have the nested add_interval propagate time_type = MYSQL_TIMESTAMP_DATE rather than MYSQL_TIMESTAMP_DATETIME, or extend the DATE->DATETIME normalization guard in eval_datetime() so it also governs the val_str path. Regressed in commit 876fd7ffe0d (WL#16895).