Description:
mysql> SELECT c >= TIMESTAMP'1999-01-01 00:00:00' FROM t; -- 1 (one-sided holds)
+-------------------------------------+
| c >= TIMESTAMP'1999-01-01 00:00:00' |
+-------------------------------------+
| 1 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT c <= TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 1 (one-sided holds)
+-------------------------------------+
| c <= TIMESTAMP'1999-12-31 23:59:59' |
+-------------------------------------+
| 1 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT c >= TIMESTAMP'1999-01-01 00:00:00'
-> AND c <= TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 1 (decomposition holds)
+--------------------------------------------------------------------------------+
| c >= TIMESTAMP'1999-01-01 00:00:00'
AND c <= TIMESTAMP'1999-12-31 23:59:59' |
+--------------------------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT c BETWEEN TIMESTAMP'1999-01-01 00:00:00'
-> AND TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 0 ← BETWEEN self-contradicts!
+--------------------------------------------------------------------------------+
| c BETWEEN TIMESTAMP'1999-01-01 00:00:00'
AND TIMESTAMP'1999-12-31 23:59:59' |
+--------------------------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
WHERE impact: WHERE c BETWEEN ... returns 0 rows while WHERE c >= ... AND c <= ... returns 1 row — the same filter yields two different row counts.
Boundary: breaks for TIMESTAMP ranges (DATE ranges coincide: BETWEEN=0, decomposition=0); NOT BETWEEN breaks symmetrically (1 vs 0).
Mechanism: Item_func_between's temporal path and Arg_comparator (standalone comparisons) make different type decisions for "TIME column × TIMESTAMP constant" — the standalone comparator truncates the constant to its time part (M10's behavior), while BETWEEN's implementation yields a third result (the range is judged false outright).
How to repeat:
CREATE TABLE t (c TIME);
INSERT INTO t VALUES ('16:52:49');
SELECT c >= TIMESTAMP'1999-01-01 00:00:00' FROM t; -- 1 (one-sided holds)
SELECT c <= TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 1 (one-sided holds)
SELECT c >= TIMESTAMP'1999-01-01 00:00:00'
AND c <= TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 1 (decomposition holds)
SELECT c BETWEEN TIMESTAMP'1999-01-01 00:00:00'
AND TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 0 ← BETWEEN self-contradicts!
Description: mysql> SELECT c >= TIMESTAMP'1999-01-01 00:00:00' FROM t; -- 1 (one-sided holds) +-------------------------------------+ | c >= TIMESTAMP'1999-01-01 00:00:00' | +-------------------------------------+ | 1 | +-------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT c <= TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 1 (one-sided holds) +-------------------------------------+ | c <= TIMESTAMP'1999-12-31 23:59:59' | +-------------------------------------+ | 1 | +-------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT c >= TIMESTAMP'1999-01-01 00:00:00' -> AND c <= TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 1 (decomposition holds) +--------------------------------------------------------------------------------+ | c >= TIMESTAMP'1999-01-01 00:00:00' AND c <= TIMESTAMP'1999-12-31 23:59:59' | +--------------------------------------------------------------------------------+ | 1 | +--------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT c BETWEEN TIMESTAMP'1999-01-01 00:00:00' -> AND TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 0 ← BETWEEN self-contradicts! +--------------------------------------------------------------------------------+ | c BETWEEN TIMESTAMP'1999-01-01 00:00:00' AND TIMESTAMP'1999-12-31 23:59:59' | +--------------------------------------------------------------------------------+ | 0 | +--------------------------------------------------------------------------------+ 1 row in set (0.00 sec) WHERE impact: WHERE c BETWEEN ... returns 0 rows while WHERE c >= ... AND c <= ... returns 1 row — the same filter yields two different row counts. Boundary: breaks for TIMESTAMP ranges (DATE ranges coincide: BETWEEN=0, decomposition=0); NOT BETWEEN breaks symmetrically (1 vs 0). Mechanism: Item_func_between's temporal path and Arg_comparator (standalone comparisons) make different type decisions for "TIME column × TIMESTAMP constant" — the standalone comparator truncates the constant to its time part (M10's behavior), while BETWEEN's implementation yields a third result (the range is judged false outright). How to repeat: CREATE TABLE t (c TIME); INSERT INTO t VALUES ('16:52:49'); SELECT c >= TIMESTAMP'1999-01-01 00:00:00' FROM t; -- 1 (one-sided holds) SELECT c <= TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 1 (one-sided holds) SELECT c >= TIMESTAMP'1999-01-01 00:00:00' AND c <= TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 1 (decomposition holds) SELECT c BETWEEN TIMESTAMP'1999-01-01 00:00:00' AND TIMESTAMP'1999-12-31 23:59:59' FROM t; -- 0 ← BETWEEN self-contradicts!