Description:
mysql> CREATE TABLE t (c TIME);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t VALUES ('16:52:49'),('00:00:01'),('23:59:59');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> -- Constant context (full-value numeric comparison — correct):
mysql> SELECT (TIME'16:52:49') < TIMESTAMP'1999-12-31 23:59:59'; -- 0 (165249 vs 19991231235959)
+---------------------------------------------------+
| (TIME'16:52:49') < TIMESTAMP'1999-12-31 23:59:59' |
+---------------------------------------------------+
| 0 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- TIME column context (constant degraded to time-of-day — wrong):
mysql> SELECT c, c < TIMESTAMP'1999-12-31 23:59:59' FROM t;
+----------+------------------------------------+
| c | c < TIMESTAMP'1999-12-31 23:59:59' |
+----------+------------------------------------+
| 16:52:49 | 1 |
| 00:00:01 | 1 |
| 23:59:59 | 0 |
+----------+------------------------------------+
3 rows in set (0.00 sec)
mysql> -- '16:52:49' → 1 (should be 0: constant truncated to 23:59:59 before comparing)
mysql> -- '00:00:01' → 1
mysql> -- '23:59:59' → 0
Mechanism proof: CAST(TIME'16:52:49' AS DATETIME) = current day + time — if the column were DATETIME-ized, 2026 > 1999 would give 0; the observed 1 proves the constant side is truncated (the comparator reduces the DATETIME constant to its time part and compares against the TIME column).
Masking: constants whose time part is 00:00:00 (e.g. TIMESTAMP'2024-01-01 00:00:00') coincide in both contexts — the bug surfaces only when the constant's time-of-day part is late. CAST(... AS DATETIME) explicit constants split identically; both TIME(0) and TIME(3) trigger.
Impact: WHERE c < TIMESTAMP'1999-12-31 23:59:59' returns 2 rows (should be 0) — the user intent "any time earlier than end of 1999" degrades to "time-of-day earlier than 23:59:59", silently changing the semantics.
How to repeat:
CREATE TABLE t (c TIME);
INSERT INTO t VALUES ('16:52:49'),('00:00:01'),('23:59:59');
-- Constant context (full-value numeric comparison — correct):
SELECT (TIME'16:52:49') < TIMESTAMP'1999-12-31 23:59:59'; -- 0 (165249 vs 19991231235959)
-- TIME column context (constant degraded to time-of-day — wrong):
SELECT c, c < TIMESTAMP'1999-12-31 23:59:59' FROM t;
-- '16:52:49' → 1 (should be 0: constant truncated to 23:59:59 before comparing)
-- '00:00:01' → 1
-- '23:59:59' → 0
Description: mysql> CREATE TABLE t (c TIME); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t VALUES ('16:52:49'),('00:00:01'),('23:59:59'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> -- Constant context (full-value numeric comparison — correct): mysql> SELECT (TIME'16:52:49') < TIMESTAMP'1999-12-31 23:59:59'; -- 0 (165249 vs 19991231235959) +---------------------------------------------------+ | (TIME'16:52:49') < TIMESTAMP'1999-12-31 23:59:59' | +---------------------------------------------------+ | 0 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> -- TIME column context (constant degraded to time-of-day — wrong): mysql> SELECT c, c < TIMESTAMP'1999-12-31 23:59:59' FROM t; +----------+------------------------------------+ | c | c < TIMESTAMP'1999-12-31 23:59:59' | +----------+------------------------------------+ | 16:52:49 | 1 | | 00:00:01 | 1 | | 23:59:59 | 0 | +----------+------------------------------------+ 3 rows in set (0.00 sec) mysql> -- '16:52:49' → 1 (should be 0: constant truncated to 23:59:59 before comparing) mysql> -- '00:00:01' → 1 mysql> -- '23:59:59' → 0 Mechanism proof: CAST(TIME'16:52:49' AS DATETIME) = current day + time — if the column were DATETIME-ized, 2026 > 1999 would give 0; the observed 1 proves the constant side is truncated (the comparator reduces the DATETIME constant to its time part and compares against the TIME column). Masking: constants whose time part is 00:00:00 (e.g. TIMESTAMP'2024-01-01 00:00:00') coincide in both contexts — the bug surfaces only when the constant's time-of-day part is late. CAST(... AS DATETIME) explicit constants split identically; both TIME(0) and TIME(3) trigger. Impact: WHERE c < TIMESTAMP'1999-12-31 23:59:59' returns 2 rows (should be 0) — the user intent "any time earlier than end of 1999" degrades to "time-of-day earlier than 23:59:59", silently changing the semantics. How to repeat: CREATE TABLE t (c TIME); INSERT INTO t VALUES ('16:52:49'),('00:00:01'),('23:59:59'); -- Constant context (full-value numeric comparison — correct): SELECT (TIME'16:52:49') < TIMESTAMP'1999-12-31 23:59:59'; -- 0 (165249 vs 19991231235959) -- TIME column context (constant degraded to time-of-day — wrong): SELECT c, c < TIMESTAMP'1999-12-31 23:59:59' FROM t; -- '16:52:49' → 1 (should be 0: constant truncated to 23:59:59 before comparing) -- '00:00:01' → 1 -- '23:59:59' → 0