Description:
mysql> -- Constant context (strict mode — correctly errors):
mysql> SELECT '10:00:00' < DATE'2024-01-01'; -- ERROR 1525 Incorrect DATE value: '10:00:00'
ERROR 1525 (HY000): Incorrect DATE value: '10:00:00'
mysql>
mysql> -- Column context (silent, wrong):
mysql> SELECT c, c < DATE'2024-01-01', c = DATE'2024-01-01' FROM v;
+------------+----------------------+----------------------+
| c | c < DATE'2024-01-01' | c = DATE'2024-01-01' |
+------------+----------------------+----------------------+
| 10:00:00 | 1 | 0 |
| abc | 1 | 0 |
| 2024-01-01 | 0 | 1 |
| 1 | 1 | 0 |
| 9999 | 1 | 0 |
+------------+----------------------+----------------------+
5 rows in set, 8 warnings (0.00 sec)
Sentinel semantics: a failed conversion is treated as "smaller than every DATE and not equal to anything" — violating three-valued logic (a failed conversion should yield NULL: NULL < x and NULL = x are both NULL). Note CAST('abc' AS DATE) correctly returns NULL — the conversion function itself is fine; only the comparator's conversion path is defective.
Impact: SELECT COUNT(*) FROM v WHERE c < DATE'2024-01-01' returns 4 — filtering a string column with a DATE constant silently selects every dirty/unconvertible row.
How to repeat:
CREATE TABLE v (c VARCHAR(20));
INSERT INTO v VALUES ('10:00:00'),('abc'),('2024-01-01'),('1'),('9999');
-- Constant context (strict mode — correctly errors):
SELECT '10:00:00' < DATE'2024-01-01'; -- ERROR 1525 Incorrect DATE value: '10:00:00'
-- Column context (silent, wrong):
SELECT c, c < DATE'2024-01-01', c = DATE'2024-01-01' FROM v;
-- '10:00:00' → (< = 1, = = 0) should be NULL or an error
-- 'abc' → (< = 1, = = 0) should be NULL or an error
-- '1','9999' → same
-- '2024-01-01' → (< = 0) ✓ (normal row)
Description: mysql> -- Constant context (strict mode — correctly errors): mysql> SELECT '10:00:00' < DATE'2024-01-01'; -- ERROR 1525 Incorrect DATE value: '10:00:00' ERROR 1525 (HY000): Incorrect DATE value: '10:00:00' mysql> mysql> -- Column context (silent, wrong): mysql> SELECT c, c < DATE'2024-01-01', c = DATE'2024-01-01' FROM v; +------------+----------------------+----------------------+ | c | c < DATE'2024-01-01' | c = DATE'2024-01-01' | +------------+----------------------+----------------------+ | 10:00:00 | 1 | 0 | | abc | 1 | 0 | | 2024-01-01 | 0 | 1 | | 1 | 1 | 0 | | 9999 | 1 | 0 | +------------+----------------------+----------------------+ 5 rows in set, 8 warnings (0.00 sec) Sentinel semantics: a failed conversion is treated as "smaller than every DATE and not equal to anything" — violating three-valued logic (a failed conversion should yield NULL: NULL < x and NULL = x are both NULL). Note CAST('abc' AS DATE) correctly returns NULL — the conversion function itself is fine; only the comparator's conversion path is defective. Impact: SELECT COUNT(*) FROM v WHERE c < DATE'2024-01-01' returns 4 — filtering a string column with a DATE constant silently selects every dirty/unconvertible row. How to repeat: CREATE TABLE v (c VARCHAR(20)); INSERT INTO v VALUES ('10:00:00'),('abc'),('2024-01-01'),('1'),('9999'); -- Constant context (strict mode — correctly errors): SELECT '10:00:00' < DATE'2024-01-01'; -- ERROR 1525 Incorrect DATE value: '10:00:00' -- Column context (silent, wrong): SELECT c, c < DATE'2024-01-01', c = DATE'2024-01-01' FROM v; -- '10:00:00' → (< = 1, = = 0) should be NULL or an error -- 'abc' → (< = 1, = = 0) should be NULL or an error -- '1','9999' → same -- '2024-01-01' → (< = 0) ✓ (normal row)