Description:
When the left-hand side operand of BETWEEN cannot be converted to the temporal type of the bounds (out-of-range numeric -> TIME/DATE), MySQL deterministically returns FALSE (0) instead of NULL. The value actually being compared lies OUTSIDE the entire legal temporal domain, which proves the server is comparing an uninitialized Time_val/Date_val (raw m_value, since Time_val() = default does not initialize it).
The conversion failure is acknowledged elsewhere (CAST returns NULL with a warning), and the equivalent >=/<= predicates do not exhibit this, so the NULL (UNKNOWN) semantics is silently violated. In strict mode the error is also swallowed (no ER 1525), while other paths (e.g. 'abc' = DATE'...') do raise ERROR 1525 in the same session.
Environment: self-built debug build of mysql-server trunk (commit 23e360a7fac, 2026-08-20), reports itself as 26.7.0-debug, Linux x86_64.
Related (not duplicates):
Bug #119727 (BETWEEN with NULL bound + mixed temporal column types returns 0; its community fix PR mysql-server#644 only handles all_temporal operands and explicitly does NOT cover a numeric LHS).
Bug #121149 (TIMEDIFF NOT BETWEEN after materialization; lenient string->TIME conversion semantics, different failure mode).
How to repeat:
-- Conversion failure is acknowledged by CAST:
SELECT CAST(99999999 AS TIME); -- NULL (+warning): HHMMSS 99999999 is invalid
SELECT CAST(1e300 AS DATE); -- NULL (+warning): out of range
-- BETWEEN returns FALSE (0) where NULL is expected:
SELECT 99999999 BETWEEN TIME'00:00:00' AND TIME'23:59:59'; -- expected NULL, got 0
SELECT 99999999 NOT BETWEEN TIME'00:00:00' AND TIME'23:59:59'; -- expected NULL, got 1
-- Phantom-value proof: result is 0 even over the FULL legal TIME domain,
-- i.e. the compared value is not any legal Time_val:
SELECT 99999999 BETWEEN TIME'-838:59:59' AND TIME'838:59:59'; -- expected NULL, got 0
SELECT 1e300 BETWEEN DATE'0000-01-01' AND DATE'9999-12-31'; -- expected NULL, got 0
-- Other LHS shapes (int / double / decimal) all affected:
SELECT 99999999999 BETWEEN DATE'2024-01-01' AND DATE'2030-01-01'; -- 0, expected NULL
SELECT CAST(1e300 AS DECIMAL(65,0)) BETWEEN DATE'2024-01-01' AND DATE'2030-01-01'; -- 0
-- Strict mode stays silent (compare: SELECT 'abc' = DATE'2024-01-01'; -> ERROR 1525):
SET sql_mode = DEFAULT;
SELECT 1e300 BETWEEN DATE'2024-01-01' AND DATE'2030-01-01'; -- silent 0
-- Row-wise execution (rules out constant folding):
CREATE TABLE m (id INT, x BIGINT UNSIGNED);
INSERT INTO m VALUES (1, 99999999), (2, 8385959); -- 8385959 = 838:59:59, control row
SELECT id, x BETWEEN TIME'-838:59:59' AND TIME'838:59:59' AS full_domain FROM m;
-- row 1: expected NULL, got 0 (bug); row 2: 1 (correct control)
-- Controls with successful conversion work fine:
SELECT 20240615 BETWEEN DATE'2024-01-01' AND DATE'2030-01-01'; -- 1 (correct)
SELECT 123045 BETWEEN TIME'00:00:00' AND TIME'23:59:59'; -- 1 (correct)
Suggested fix:
if (compare_as_times) {
Time_val time;
(void)args[0]->val_time(&time); // <-- return value (conversion failure) discarded
value = 0;
if (!args[0]->null_value) { // <-- not set on conversion failure
value = time.for_comparison(); // <-- reads uninitialized value
}
} else if (compare_as_dates) {
Date_val date;
(void)args[0]->val_date(&date, 0); // <-- same pattern
...
}
