Bug #119565 Temporary table causes incorrect conversion from TIME to JSON type

• Submitted: 17 Dec 2025 1:06
• Modified: 5 Feb 15:32
• Reporter: Alice Alice
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Any
• CPU Architecture: Any

[17 Dec 2025 1:06] Alice Alice

Description:
When a temporary table is used (triggered by SQL_BUFFER_RESULT or ORDER BY), the conversion from TIME type to JSON type produces incorrect results. Specifically: the TIME value -838:59:59.000000 is converted to JSON as "-630:59:59.000000" when processed through a temporary table, whereas without the temporary table it shows as "-06:59:59.000000" (although this value itself may also be problematic, the result with temporary table is clearly more incorrect).

How to repeat:
CREATE TABLE `bug` (
  `id` int NOT NULL DEFAULT '0',
  `c30` time(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO bug VALUES (1, '-838:59:59.000000');

-- Conversion with temporary table (incorrect result):
SELECT SQL_BUFFER_RESULT CAST(bug.c30 AS JSON) FROM bug;

-- Conversion without temporary table (expected result):
SELECT CAST(bug.c30 AS JSON) FROM bug;

[5 Feb 15:32] Roy Lyseng

Thank you for the bug report.
Verified as described.

Note however that this problem seems to be fixed in 9.5 and later.