Description:
mysql>
mysql> SELECT v, INTERVAL(v, '1', '5') FROM t;
+------+-----------------------+
| v | INTERVAL(v, '1', '5') |
+------+-----------------------+
| abc | 0 |
| 1 | 1 |
| 5 | 2 |
| zz | 0 |
+------+-----------------------+
4 rows in set, 2 warnings (0.00 sec)
mysql> -- 'abc' → 0 (numericized 'abc'→0 < 1; lexicographically 'abc' > '5' → should be 2)
mysql> -- 'zz' → 0 (numericized → 0; lexicographically 'zz' > '5' → should be 2)
mysql>
mysql> SELECT v, CASE WHEN v < '1' THEN 0 WHEN v < '5' THEN 1 ELSE 2 END FROM t;
+------+---------------------------------------------------------+
| v | CASE WHEN v < '1' THEN 0 WHEN v < '5' THEN 1 ELSE 2 END |
+------+---------------------------------------------------------+
| abc | 2 |
| 1 | 1 |
| 5 | 2 |
| zz | 2 |
+------+---------------------------------------------------------+
4 rows in set (0.00 sec)
WHERE impact (inverted range filtering): WHERE INTERVAL(v,'1','5') = 0 matches 2 rows ('abc'/'zz' judged "less than '1'") — the lexicographically largest strings land in the lowest bucket; range filtering returns the opposite of string semantics.
Attribution: INTERVAL's argument type aggregation numericizes (string→0) while the standalone comparator (v < '1') aggregates as strings — the mirror image of M18. Systematic inconsistency across the function family.
How to repeat:
CREATE TABLE t (v VARCHAR(20));
INSERT INTO t VALUES ('abc'), ('1'), ('5'), ('zz');
SELECT v, INTERVAL(v, '1', '5') FROM t;
-- 'abc' → 0 (numericized 'abc'→0 < 1; lexicographically 'abc' > '5' → should be 2)
-- 'zz' → 0 (numericized → 0; lexicographically 'zz' > '5' → should be 2)
SELECT v, CASE WHEN v < '1' THEN 0 WHEN v < '5' THEN 1 ELSE 2 END FROM t;
-- 'abc' → 2 ✓ (string order) 'zz' → 2 ✓
Description: mysql> mysql> SELECT v, INTERVAL(v, '1', '5') FROM t; +------+-----------------------+ | v | INTERVAL(v, '1', '5') | +------+-----------------------+ | abc | 0 | | 1 | 1 | | 5 | 2 | | zz | 0 | +------+-----------------------+ 4 rows in set, 2 warnings (0.00 sec) mysql> -- 'abc' → 0 (numericized 'abc'→0 < 1; lexicographically 'abc' > '5' → should be 2) mysql> -- 'zz' → 0 (numericized → 0; lexicographically 'zz' > '5' → should be 2) mysql> mysql> SELECT v, CASE WHEN v < '1' THEN 0 WHEN v < '5' THEN 1 ELSE 2 END FROM t; +------+---------------------------------------------------------+ | v | CASE WHEN v < '1' THEN 0 WHEN v < '5' THEN 1 ELSE 2 END | +------+---------------------------------------------------------+ | abc | 2 | | 1 | 1 | | 5 | 2 | | zz | 2 | +------+---------------------------------------------------------+ 4 rows in set (0.00 sec) WHERE impact (inverted range filtering): WHERE INTERVAL(v,'1','5') = 0 matches 2 rows ('abc'/'zz' judged "less than '1'") — the lexicographically largest strings land in the lowest bucket; range filtering returns the opposite of string semantics. Attribution: INTERVAL's argument type aggregation numericizes (string→0) while the standalone comparator (v < '1') aggregates as strings — the mirror image of M18. Systematic inconsistency across the function family. How to repeat: CREATE TABLE t (v VARCHAR(20)); INSERT INTO t VALUES ('abc'), ('1'), ('5'), ('zz'); SELECT v, INTERVAL(v, '1', '5') FROM t; -- 'abc' → 0 (numericized 'abc'→0 < 1; lexicographically 'abc' > '5' → should be 2) -- 'zz' → 0 (numericized → 0; lexicographically 'zz' > '5' → should be 2) SELECT v, CASE WHEN v < '1' THEN 0 WHEN v < '5' THEN 1 ELSE 2 END FROM t; -- 'abc' → 2 ✓ (string order) 'zz' → 2 ✓