Query with impossible or constant subquery in WHERE or HAVING is not precomputed and thus not part of optimization
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Medium
|
Timour Katchaounov |
Bug Description
The following query
SELECT MAX( alias2.a ) AS field
FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
WHERE alias1.a = alias2.a OR alias1.a = 'y'
HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
works almost instantly on MariaDB 5.2, but takes quite long, depending on the amount of data in t1, on MariaDB 5.3.
bzr version-info
revision-id: <email address hidden>
date: 2012-02-29 23:28:16 -0800
build-date: 2012-03-02 14:57:35 +0400
revno: 3451
bzr version-info
revision-id: <email address hidden>
date: 2012-02-28 13:50:30 +0200
build-date: 2012-02-29 03:39:46 +0400
revno: 3116
branch-nick: maria-5.2
EXPLAIN in 5.3:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY alias3 index NULL a 19 NULL 133 100.00 Using index
1 PRIMARY alias2 index a a 19 NULL 133 100.00 Using index; Using join buffer (flat, BNL join)
1 PRIMARY alias1 index a a 19 NULL 133 100.00 Using where; Using index; Using join buffer (incremental, BNL join)
2 MATERIALIZED t1 index a a 19 NULL 133 100.00 Using index
Warnings:
Note 1003 select max(`test`
optimizer_switch in 5.3 (default):
index_merge=
join_cache_level=2 (default)
EXPLAIN in 5.2:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
2 SUBQUERY t1 index_subquery a a 19 const 1 100.00 Using index; Using where
Warnings:
Note 1003 select max(`test`
optimizer_switch in 5.2 (default):
index_merge=
Test case:
CREATE TABLE t1 ( a VARCHAR(16), KEY (a) );
INSERT INTO t1 VALUES
('Abilene'
('Alexandria'
('Anchorage'),('Ann Arbor')
('Arlington'
('Athens-Clarke County'
('Augusta-Richmond County'
('Austin'
('Baton Rouge')
('Berkeley'
('Boise City'),
('Brockton'
('Cambridge'
('Carson'
('Charleston'
('Chesapeake'
('Cincinnati'
('Clearwater'
('Columbia'
('Concord'),('Coral Springs'
('Corpus Christi'),('Costa Mesa'),('Dallas'),
('Daly City'),
('Des Moines'
('East Los Angeles'),('El Cajon'),('El Monte'),
('El Paso'),
('Escondido'
('Fall River')
('Fort Collins'),('Fort Lauderdale'),('Fort Wayne'),
('Fort Worth')
('Gainesville'
('Gilbert'
('Grand Prairie'),('Grand Rapids'),('Green Bay'),
('Greensboro'
('Henderson'
('Houston'
('Independence'
('Irvine'
('Jersey City'),
('Kansas City'),
('Lafayette'
;
SELECT MAX( alias2.a ) AS field
FROM t1 AS alias1, t1 AS alias2, t1 AS alias3
WHERE alias1.a = alias2.a OR alias1.a = 'y'
HAVING field>'B' AND ( 'Moscow' ) IN ( SELECT a FROM t1 );
# End of test case
Changed in maria: | |
importance: | Undecided → High |
Changed in maria: | |
assignee: | Sergey Petrunia (sergefp) → Timour Katchaounov (timour) |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
importance: | High → Medium |
milestone: | 5.3 → 5.5 |
summary: |
- Query with impossible HAVING takes 1 millisec on 5.2 and 8 sec on 5.3 + Query with impossible or constant subquery in WHERE or HAVING is not + precomputed and thus not part of optimization |
tags: | added: optimizer |
(comment based on the original, non-simplified testcase. The testcase posted here looks ok but I did not do a real check with it)
This bug demonstrates a problem with the optimizer, in particular with the choice between IN->EXISTS and Materialization strategies.
We have a query:
const1 IN (SELECT inner_expr FROM ... )
Why would the optimizer pick Materialization, when we have "const1" on the left side, and so will make only one lookup in the materialized table? It is obvious that IN->EXISTS will always be better than Materialization for such cases.