MySQL Bugs: #120851: Wrong result / inconsistent comparison behavior for TIME() expression between VIEW/CTE and TEMPORARY TABLE

Bug #120851	Wrong result / inconsistent comparison behavior for TIME() expression between VIEW/CTE and TEMPORARY TABLE
Submitted:	3 Jul 5:24	Modified:	3 Jul 16:17
Reporter:	Xiaoyuan Xie	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	9.6.0,9.7.0	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
A TIME() expression behaves differently when used through a VIEW or CTE compared with a TEMPORARY TABLE created by CREATE TEMPORARY TABLE ... SELECT.

The expression:

TIME(c1)
where c1 is a DATE column with value '2024-01-01', produces the displayed value:

00:00:00
When queried through a VIEW or CTE, the predicate:

vc = '0:00:00'
returns false and no row is returned.

However, after the same expression is materialized into a TEMPORARY TABLE, the same predicate returns true and the row is returned.

This suggests that the VIEW/CTE form and the TEMPORARY TABLE form use different comparison semantics. The VIEW/CTE result appears consistent with string comparison:

'00:00:00' = '0:00:00'  -> false
while the TEMPORARY TABLE result appears consistent with TIME comparison:

00:00:00 = 0:00:00  -> true
Since TIME() returns a time value, I would expect the VIEW and CTE queries to use TIME comparison semantics as well. Therefore, the VIEW and CTE queries appear to return a wrong result.

How to repeat:
DROP VIEW IF EXISTS v0;
DROP TEMPORARY TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (
  c1 DATE
);

INSERT INTO t0 VALUES ('2024-01-01');

CREATE VIEW v0 AS
SELECT TIME(c1) AS vc
FROM t0;

SELECT vc
FROM v0
WHERE vc = '0:00:00';

WITH cte AS (
  SELECT TIME(c1) AS vc
  FROM t0
)
SELECT vc
FROM cte
WHERE vc = '0:00:00';

CREATE TEMPORARY TABLE tmp AS
SELECT TIME(c1) AS vc
FROM t0;

SELECT vc
FROM tmp
WHERE vc = '0:00:00';

VIEW:
Empty set

CTE:
Empty set

TEMPORARY TABLE:
+----------+
| vc       |
+----------+
| 00:00:00 |
+----------+

Hi Xiaoyuan Xie,

Thank you for the test case. Verified as described.

This is duplicate of bug#120848.
The only difference is that MAKETIME() is replaced with TIME().

Thank you Roy for checking.

I agree that Bug #120849 and Bug #120851 appear to expose the same underlying issue as Bug #120848: a TIME-valued expression in a VIEW/CTE is compared differently from the same expression materialized into a TEMPORARY TABLE.

One small note: Bug #120851 may be a useful additional reproduction because it does not rely on a TIME value greater than 24 hours. It uses `TIME(DATE)` producing `00:00:00` and compares it with `'0:00:00'`, so it seems to show that the issue is not limited to large TIME values, but to comparison semantics for TIME-valued expressions in VIEW/CTE contexts.

Thanks again for the clarification.