MySQL Bugs: #120788: JSON_VALUE RETURNING DATETIME DEFAULT returns a garbage date

Bug #120788	JSON_VALUE RETURNING DATETIME DEFAULT returns a garbage date
Submitted:	26 Jun 11:11	Modified:	15 Jul 14:13
Reporter:	Yakir Gibraltar (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S2 (Serious)
Version:	9.7.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
On MySQL 9.7.0, JSON_VALUE(... RETURNING DATETIME DEFAULT <datetime> ON ERROR / ON EMPTY) used in a DATE context returns a constant garbage date (0083-15-31) instead of the date part of the supplied DEFAULT. 9.6.0 correctly returns the date part of the default.

Regression introduced by WL#16895 "Refactor DATE handling in server" (commit 876fd7ffe0db, 2026-02-17). Item_func_json_value::val_date() (sql/item_json_func.cc:4965-4966) routes:
  case ITEM_CAST_DATETIME: return extract_date_value(date);
But extract_date_value() (sql/item_json_func.cc:5177; its assert at :5178 permits only ITEM_CAST_DATE/ITEM_CAST_YEAR) reads the default from the date_default member, whereas for RETURNING DATETIME the evaluated DEFAULT is stored in the separate datetime_default member. Default_value (sql/item_json_func.cc:4218-4227) is a plain struct, not a union, so the read returns a default-constructed Date_val (0083-15-31). The correct sibling routing exists ~50 lines below at :5017-5018 (case ITEM_CAST_DATETIME: return extract_datetime_value(dt)). The pre-9.7 trunk val_date() was simply 'return val_datetime(date, flags);', which reads the correct member.

The output is the constant 0083-15-31 for every DEFAULT value tested, confirming a never-populated struct member is read. No warning or error is raised, so the garbage date silently changes WHERE/JOIN predicate results.

The buggy routing is still present at the current 9.7 branch HEAD, so this still ships in 9.7.1.

Thank you,
Yakir Gibraltar

How to repeat:
-- 9.7.0 returns 0083-15-31 (garbage); 9.6.0 returns 2023-07-19 (correct):
SELECT CAST(JSON_VALUE('"x"', '$' RETURNING DATETIME DEFAULT '2023-07-19 08:09:10' ON ERROR) AS DATE);

-- The ON EMPTY path and the DATE(JSON_VALUE(...)) form behave identically:
SELECT CAST(JSON_VALUE('{}', '$.x' RETURNING DATETIME DEFAULT '2023-07-19 08:09:10' ON EMPTY) AS DATE);

-- Independent oracle (referential transparency): the inner DEFAULT evaluates to the datetime
-- '2023-07-19 08:09:10', and casting THAT value to DATE yields 2023-07-19 on BOTH versions:
SELECT CAST(TIMESTAMP'2023-07-19 08:09:10' AS DATE);   -- 2023-07-19 on 9.7.0 and 9.6.0

-- The 9.7.0 output is the constant 0083-15-31 regardless of which DEFAULT datetime is supplied
-- (e.g. DEFAULT '2000-01-01 00:00:00' also yields 0083-15-31), proving an unread/wrong struct member.

Suggested fix:
In Item_func_json_value::val_date(), route case ITEM_CAST_DATETIME to extract_datetime_value() (then narrow to the date part), as the pre-9.7 code and the sibling at sql/item_json_func.cc:5017-5018 do -- not extract_date_value(), which only reads date_default and asserts on non-DATE/YEAR cast targets.

Thank you for the bug report.
Verified as described.

One small sibling case: the same wrong-member pattern also reproduces with RETURNING YEAR DEFAULT.

SELECT CAST(JSON_VALUE('"x"', '$' RETURNING YEAR DEFAULT 2023 ON ERROR) AS DATE);
-- 9.7.1 returns 0083-15-31

This appears to be the same Default_value member issue already verified here, since YEAR stores the default in integer_default while the DATE path reads date_default.

Thank you, Yakir Gibraltar

Posted by developer: Bug status updated to 'Patch approved'

Posted by developer: Bug status updated to 'Documenting'

Posted by developer:
 
Added the following note to the MySQL Server 9.7.2 and 26.7.0 release notes:

JSON_VALUE() with RETURNING DATETIME and a DEFAULT value
        could return an invalid constant date when the expression was
        used in a DATE context, silently affecting predicates and joins.
        The datetime value is now extracted before narrowing to a date.