Bug #120294 Inconsistent fractional seconds precision (FSP) for TIME function results between VIEW and CTE

• Submitted: 19 Apr 17:16
• Modified: 20 Apr 8:37
• Reporter: Niu Xiaoxu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[19 Apr 17:16] Niu Xiaoxu

Description:
There is a query result format and metadata inference inconsistency between a VIEW and a CTE. When evaluating an expression involving time functions (TIME(ADDTIME(...))), the VIEW and the CTE produce results with different fractional seconds precision (FSP).

How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

DROP TABLE IF EXISTS t0;
DROP VIEW IF EXISTS v0;

CREATE TABLE t0 (
    c3 TIME
);

INSERT INTO t0 VALUES ('51:21:23');

CREATE VIEW v0 AS
SELECT TIME(ADDTIME(c3, '0:0:0')) AS vc_0 FROM t0;

SELECT * FROM v0; -- 51:21:23.000000

WITH cte AS (
    SELECT TIME(ADDTIME(c3, '0:0:0')) AS vc_0 FROM t0
)
SELECT * FROM cte; -- 51:21:23

VIEW RESULT:
+-----------------+
| vc_0            |
+-----------------+
| 51:21:23.000000 |
+-----------------+

CTE RESULT:
+----------+
| vc_0     |
+----------+
| 51:21:23 |
+----------+

[20 Apr 8:37] Roy Lyseng

Thank you for the bug report.
Verified as described.