Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.3.2, 13.0.1
-
Ubuntu 22.04
Description
Hi, MariaDB developers. I wanna recommend an optimization about ABS().
When querying with ABS(t0.c0) < 10, MariaDB performs a full table scan even though the equivalent condition t0.c0 > -10 AND t0.c0 < 10 can be executed as a range scan. The optimizer does not transform the ABS() predicate into a range condition, preventing efficient index usage.
CREATE TABLE t0(c0 INT PRIMARY KEY); |
INSERT INTO t0 SELECT seq FROM seq_1_to_1000000; |
|
|
-- positive case
|
EXPLAIN SELECT * FROM t0 WHERE t0.c0 > -10 AND t0.c0 < 10; |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
| 1 | SIMPLE | t0 | range | PRIMARY | PRIMARY | 4 | NULL | 9 | Using where | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
|
|
-- negative case
|
EXPLAIN SELECT * FROM t0 WHERE ABS(t0.c0) < 10; |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+ |
| 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 999000 | Using where | |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+ |