Details
Description
The following two predicates are semantically equivalent in a WHERE clause:
SHA2(pad, 256) BETWEEN '0' AND 'f' |
and:
SHA2(pad, 256) BETWEEN '0' AND 'f' |
AND SHA2(pad, 256) IS NOT NULL |
For BETWEEN, if SHA2(pad, 256) returns NULL, the predicate evaluates to UNKNOWN, so the row is filtered out by WHERE. Therefore, the explicit SHA2(pad, 256) IS NOT NULL condition is redundant.
However, the optimizer keeps the redundant predicate and evaluates SHA2(pad, 256) again. This causes a measurable performance difference on large tables.
MariaDB 12.3.2: |
BETWEEN only median = 170.62 ms |
BETWEEN + IS NOT NULL median = 249.00 ms |
slowdown ≈ 1.46x |
How to repeat
|
|
DROP DATABASE IF EXISTS rift_between_sha2_perf;
|
CREATE DATABASE rift_between_sha2_perf;
|
USE rift_between_sha2_perf;
|
|
|
CREATE TABLE t (
|
id INT PRIMARY KEY,
|
pad VARCHAR(100) |
);
|
|
|
INSERT INTO t
|
WITH RECURSIVE
|
a(n) AS (
|
SELECT 0 |
UNION ALL
|
SELECT n + 1 FROM a WHERE n < 999 |
),
|
b(n) AS (
|
SELECT 0 |
UNION ALL
|
SELECT n + 1 FROM b WHERE n < 999 |
)
|
SELECT
|
a.n * 1000 + b.n + 1, |
REPEAT('x', 32) |
FROM a JOIN b;
|
|
|
EXPLAIN FORMAT=JSON
|
SELECT COUNT(*)
|
FROM t
|
WHERE SHA2(pad, 256) BETWEEN '0' AND 'f'; |
|
|
EXPLAIN FORMAT=JSON
|
SELECT COUNT(*)
|
FROM t
|
WHERE SHA2(pad, 256) BETWEEN '0' AND 'f' |
AND SHA2(pad, 256) IS NOT NULL; |
|
|
SELECT COUNT(*)
|
FROM t
|
WHERE SHA2(pad, 256) BETWEEN '0' AND 'f'; |
|
|
SELECT COUNT(*)
|
FROM t
|
WHERE SHA2(pad, 256) BETWEEN '0' AND 'f' |
AND SHA2(pad, 256) IS NOT NULL; |
Expected behavior
The optimizer should recognize that:
SHA2(pad, 256) IS NOT NULL |
is redundant when combined with:
SHA2(pad, 256) BETWEEN '0' AND 'f' |
in a WHERE clause.
The optimized condition should avoid evaluating SHA2(pad, 256) again.
Attachments
Issue Links
- relates to
-
MDEV-40433 Optimizer does not eliminate duplicate identical IN subquery predicates, causing repeated full scan
-
- Confirmed
-