Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 12.3.2
-
Ubuntu 22.04
Description
Hi, MariaDB developers, I found a missed optimization in MariaDB.
MariaDB detects impossible WHERE conditions when a column is compared to a constant using both equality and inequality (e.g., c0 > 0 AND c0 = 0), producing a zero-row result without table access. However, contradictory conditions that involve only inequalities (e.g., c0 > 0 AND c0 < 0, c0 >= 0 AND c0 < 0, c0 > 0 AND c0 <= 0) are not recognized as impossible. Instead, a full table scan with a filter is performed, which is wasteful for large tables.
CREATE TABLE t0(c0 INT8); |
INSERT INTO t0 SELECT seq FROM seq_1_to_1000000; |
|
|
-- positive cases
|
SELECT * FROM t0 WHERE t0.c0 > 0 AND t0.c0 = 0; -- Empty set (0.001 sec) |
SELECT * FROM t0 WHERE t0.c0 < 0 AND t0.c0 = 0; -- Empty set (0.001 sec) |
SELECT * FROM t0 WHERE t0.c0 <> 0 AND t0.c0 = 0; -- Empty set (0.001 sec) |
-- This plan uses Impossible WHERE
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+ |
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+ |
|
|
-- negative cases
|
SELECT * FROM t0 WHERE t0.c0 > 0 AND t0.c0 < 0; -- Empty set (0.442 sec) |
SELECT * FROM t0 WHERE t0.c0 >= 0 AND t0.c0 < 0; -- Empty set (0.442 sec) |
SELECT * FROM t0 WHERE t0.c0 > 0 AND t0.c0 <= 0; -- Empty set (0.442 sec) |
-- This performs a a full table scan
|
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+ |
| 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 998412 | Using where | |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+ |
Attachments
Issue Links
- relates to
-
MDEV-8787 Return Impossible WHERE instead of Full table scan on some admittedly false field=const expressions
-
- Open
-
-
-
- In Testing
-
-
-
- In Testing
-