Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
12.2.2
-
None
-
Not for Release Notes
Description
Two logically equivalent predicates, A and A AND A, return the same result but have significantly different execution times when A contains an expensive deterministic scalar expression.
In the test case below, the predicate REPEAT(c,n) <> 'y' is duplicated exactly. Since A AND A is logically equivalent to A, the duplicated predicate could be simplified or evaluated only once per row. However, MariaDB appears to evaluate the same deterministic expression twice.
In my test, both queries returned COUNT
= 1000, but the duplicated-predicate query was consistently about 2x slower.
How to repeat
DROP DATABASE IF EXISTS perf_scalar_dup;
|
CREATE DATABASE perf_scalar_dup;
|
USE perf_scalar_dup;
|
|
|
CREATE TABLE t(
|
c VARCHAR(10), |
n INT
|
);
|
|
|
CREATE TABLE d(n INT);
|
|
|
INSERT INTO d VALUES
|
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
|
|
INSERT INTO t
|
SELECT 'x', 50000 |
FROM d a JOIN d b JOIN d c;
|
|
|
SET profiling = 1; |
|
|
SELECT SQL_NO_CACHE COUNT(*)
|
FROM t
|
WHERE REPEAT(c,n) <> 'y'; |
|
|
SELECT SQL_NO_CACHE COUNT(*)
|
FROM t
|
WHERE REPEAT(c,n) <> 'y' |
AND REPEAT(c,n) <> 'y'; |
|
|
SHOW PROFILES;
|
Observed result
Query 1 result: COUNT(*) = 1000 |
Query 2 result: COUNT(*) = 1000 |
|
|
Repeated test durations:
|
|
|
Query 1: |
0.04721763 sec |
0.04868769 sec |
0.04792680 sec |
|
|
Query 2: |
0.09598557 sec |
0.09621345 sec |
0.09566383 sec |
Expected result
MariaDB should recognize the duplicated identical deterministic predicate and avoid evaluating the same expression twice, so both queries should have comparable execution time.
Attachments
Issue Links
- duplicates
-
MDEV-40433 Optimizer does not eliminate duplicate identical IN subquery predicates, causing repeated full scan
-
- Confirmed
-