Description:
Comparing a TIME column against an integer constant of 10+ digits (>= 10^10) returns the opposite of the correct answer, while the same expression with a literal TIME operand is correct:
mysql>
mysql> SELECT TIME'16:52:49' < 10000000000; -- 1 (correct: TIME as number 165249 < 10^10)
+------------------------------+
| TIME'16:52:49' < 10000000000 |
+------------------------------+
| 1 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT c < 10000000000 FROM t; -- 0 (WRONG: same semantics, inverted result)
+-----------------+
| c < 10000000000 |
+-----------------+
| 0 |
+-----------------+
1 row in set, 1 warning (0.00 sec)
AI analyze:
Literals of 11+ digits make col_time < literal take the TIME-ified comparison path: the constant is converted to TIME, overflow is silently zeroed to 00:00:00 — equivalent to col_time < 0:
every POSITIVE TIME value compares "not less than" (inverted result)
NEGATIVE TIME values compare "less than" (accidentally matches numeric)
Literals of <= 10 digits take the numeric comparison path (correct). An explicitly typed RHS (CAST(... AS DECIMAL)) does not trigger the TIME-ified path. Companion defect: CAST(big-number AS TIME) overflow silently returns 0 without warning, contradicting the NULL returned for 10-digit numbers.
How to repeat:
CREATE TABLE t (c TIME);
INSERT INTO t VALUES ('16:52:49');
SELECT TIME'16:52:49' < 10000000000; -- 1 (correct: TIME as number 165249 < 10^10)
SELECT c < 10000000000 FROM t; -- 0 (WRONG: same semantics, inverted result)
Description: Comparing a TIME column against an integer constant of 10+ digits (>= 10^10) returns the opposite of the correct answer, while the same expression with a literal TIME operand is correct: mysql> mysql> SELECT TIME'16:52:49' < 10000000000; -- 1 (correct: TIME as number 165249 < 10^10) +------------------------------+ | TIME'16:52:49' < 10000000000 | +------------------------------+ | 1 | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT c < 10000000000 FROM t; -- 0 (WRONG: same semantics, inverted result) +-----------------+ | c < 10000000000 | +-----------------+ | 0 | +-----------------+ 1 row in set, 1 warning (0.00 sec) AI analyze: Literals of 11+ digits make col_time < literal take the TIME-ified comparison path: the constant is converted to TIME, overflow is silently zeroed to 00:00:00 — equivalent to col_time < 0: every POSITIVE TIME value compares "not less than" (inverted result) NEGATIVE TIME values compare "less than" (accidentally matches numeric) Literals of <= 10 digits take the numeric comparison path (correct). An explicitly typed RHS (CAST(... AS DECIMAL)) does not trigger the TIME-ified path. Companion defect: CAST(big-number AS TIME) overflow silently returns 0 without warning, contradicting the NULL returned for 10-digit numbers. How to repeat: CREATE TABLE t (c TIME); INSERT INTO t VALUES ('16:52:49'); SELECT TIME'16:52:49' < 10000000000; -- 1 (correct: TIME as number 165249 < 10^10) SELECT c < 10000000000 FROM t; -- 0 (WRONG: same semantics, inverted result)