Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
None
-
None
-
None
Description
The following query
SELECT MAX(al1.a)
|
FROM t1 AS al1, t1 AS al2, t1 AS al3, t1 AS al4
|
WHERE al4.a = al3.a
|
AND (
|
EXISTS ( SELECT 1 FROM t1 )
|
OR al1.a > al2.a
|
)
|
takes several times longer on MDEV-193 tree revno 3407 comparing to maria/5.5 tree 3426.
Reproducible with the default optimizer_switch as well as all OFF values except for in_to_exists required to execute the query.
Reproducible with MyISAM (~3 times longer, 30 vs 10 sec on my machine), Aria and InnoDB (~6 times longer, 60 vs 10 sec).
EXPLAIN on MDEV-193 (with the default optimizer_switch):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY al1 index NULL a 5 NULL 90 100.00 Using index
|
1 PRIMARY al2 index NULL a 5 NULL 90 100.00 Using index; Using join buffer (flat, BNL join)
|
1 PRIMARY al3 index a a 5 NULL 90 100.00 Using where; Using index; Using join buffer (incremental, BNL join)
|
1 PRIMARY al4 ref a a 5 test.al3.a 10 100.00 Using index
|
2 SUBQUERY t1 index NULL a 5 NULL 90 100.00 Using index
|
Warnings:
|
Note 1003 select max(`test`.`al1`.`a`) AS `MAX(al1.a)` from `test`.`t1` `al1` join `test`.`t1` `al2` join `test`.`t1` `al3` join `test`.`t1` `al4` where (`test`.`al4`.`a` = `test`.`al3`.`a`)
|
EXPLAIN on maria/5.5 (with the default optimizer_switch):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 PRIMARY al3 index a a 5 NULL 90 100.00 Using where; Using index
|
1 PRIMARY al4 ref a a 5 test.al3.a 10 100.00 Using index
|
1 PRIMARY al1 index a a 5 NULL 90 100.00 Using index; Using join buffer (flat, BNL join)
|
1 PRIMARY al2 index a a 5 NULL 90 100.00 Using where; Using index; Using join buffer (incremental, BNL join)
|
2 SUBQUERY t1 index NULL a 5 NULL 90 100.00 Using index
|
Warnings:
|
Note 1003 select max(`test`.`al1`.`a`) AS `MAX(al1.a)` from `test`.`t1` `al1` join `test`.`t1` `al2` join `test`.`t1` `al3` join `test`.`t1` `al4` where ((`test`.`al4`.`a` = `test`.`al3`.`a`) and (exists(select 1 from `test`.`t1`) or (`test`.`al1`.`a` > `test`.`al2`.`a`)))
|
Test case:
SET optimizer_switch = 'in_to_exists=on';
|
|
CREATE TABLE t1 (a INT, KEY(a));
|
INSERT INTO t1 VALUES
|
(7),(5),(1),(204),(224),(9),(5),(0),(3);
|
|
INSERT INTO t1 SELECT al1.* FROM t1 al1, t1 al2;
|
|
SELECT MAX(al1.a)
|
FROM t1 AS al1, t1 AS al2, t1 AS al3, t1 AS al4
|
WHERE al4.a = al3.a
|
AND (
|
EXISTS ( SELECT 1 FROM t1 )
|
OR al1.a > al2.a
|
);
|
Attachments
Issue Links
- relates to
-
MDEV-193 LP:944706 - Query with impossible or constant subquery in WHERE or HAVING is not precomputed and thus not part of optimization
- Closed