|
The problem appeared on 5.3 tree with the following revision (long ago):
revno: 3491 [merge]
|
revision-id: psergey@askmonty.org-20120404083746-oq412y6dys2yepw8
|
committer: Sergey Petrunya <psergey@askmonty.org>
|
timestamp: Wed 2012-04-04 12:37:46 +0400
|
message:
|
Merge
|
------------------------------------------------------------
|
revno: 3488.1.2
|
revision-id: psergey@askmonty.org-20120404082636-pscsfp2vak8h0ekc
|
committer: Sergey Petrunya <psergey@askmonty.org>
|
timestamp: Wed 2012-04-04 12:26:36 +0400
|
message:
|
BUG#913030: better comments and function names.
|
------------------------------------------------------------
|
revno: 3488.1.1 [merge]
|
revision-id: psergey@askmonty.org-20120402174731-hzq5kw3strd0e8rv
|
committer: Sergey Petrunya <psergey@askmonty.org>
|
timestamp: Mon 2012-04-02 21:47:31 +0400
|
message:
|
Merge
|
------------------------------------------------------------
|
revno: 3479.1.1
|
revision-id: psergey@askmonty.org-20120402174154-8y0lzcwc0qycoj3n
|
committer: Sergey Petrunya <psergey@askmonty.org>
|
timestamp: Mon 2012-04-02 21:41:54 +0400
|
message:
|
BUG#913030: Optimizer chooses a suboptimal excution plan for Q18 from DBT-3
|
- When doing join optimization, pre-sort the tables so that they mimic the execution
|
order we've had with 'semijoin=off'.
|
- That way, we will not get regressions when there are two query plans (the old and the
|
new) that have indentical costs but different execution times (because of factors that
|
the optimizer was not able to take into account).
|
Test case:
SET optimizer_switch = 'materialization=on,semijoin=on';
|
SET join_cache_level = 2;
|
|
CREATE TABLE t1 ( c1 VARCHAR(2), c2 VARCHAR(2), INDEX(c1) ) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES
|
('JP','OM'),('VA','JP'),('CA','ML'),('ML','EG'),('DK','CA'),
|
('DK','QA'),('YE','PL'),('TR','ZW'),('DK','SK'),('SK','DK'),
|
('RO','ML'),('ML','BG'),('BG','ZW'),('ZW','GE'),('GE','JP'),
|
('PL','EG'),('QA','YE'),('WF','DK'),('DK','JP'),('EG','OM');
|
|
CREATE TABLE t2 ( c3 VARCHAR(2), c4 VARCHAR(2) ) ENGINE=MyISAM;
|
INSERT INTO t2 VALUES ('CA','ML'),('IN','HU'),('HU','IN');
|
|
SELECT * FROM t1 AS alias1, t1 AS alias2
|
WHERE ( alias2.c2, alias1.c1 ) IN ( SELECT c4, c3 FROM t2 ) AND alias1.c1 IN ( SELECT c2 FROM t1 );
|
Actual result:
c1 c2 c1 c2
|
JP OM CA ML
|
CA ML CA ML
|
ML EG CA ML
|
DK CA CA ML
|
DK QA CA ML
|
YE PL CA ML
|
DK SK CA ML
|
SK DK CA ML
|
ML BG CA ML
|
BG ZW CA ML
|
ZW GE CA ML
|
GE JP CA ML
|
PL EG CA ML
|
QA YE CA ML
|
DK JP CA ML
|
EG OM CA ML
|
JP OM RO ML
|
CA ML RO ML
|
ML EG RO ML
|
DK CA RO ML
|
DK QA RO ML
|
YE PL RO ML
|
DK SK RO ML
|
SK DK RO ML
|
ML BG RO ML
|
BG ZW RO ML
|
ZW GE RO ML
|
GE JP RO ML
|
PL EG RO ML
|
QA YE RO ML
|
DK JP RO ML
|
EG OM RO ML
|
Expected result:
c1 c2 c1 c2
|
CA ML CA ML
|
CA ML RO ML
|
|