diff options
author | Igor Babaev <igor@askmonty.org> | 2017-11-10 13:58:00 -0800 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2017-11-10 14:01:29 -0800 |
commit | dcbf2823c7d64380f06372d77d1522e97fb8f066 (patch) | |
tree | 601e8b1ae2212eb4d93d8f6c7a66cdd185008fab /mysql-test/r/order_by.result | |
parent | 589b0b365589d5a0a355a7155ece83b95d6e8510 (diff) | |
download | mariadb-git-dcbf2823c7d64380f06372d77d1522e97fb8f066.tar.gz |
Fixed MDEV-13994 Bad join results with orderby_uses_equalities=on.
This patch effectively blocks the optimization that uses multiple
equalities for ORDER BY to remove tmp table in the case when
the first table happens to be the result of materialization of
a semi-join nest. Currently there is no code at the execution level
that would support the optimization in this case.
Diffstat (limited to 'mysql-test/r/order_by.result')
-rw-r--r-- | mysql-test/r/order_by.result | 48 |
1 files changed, 48 insertions, 0 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index f43e6ce18af..5a9f2fae1e0 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -3159,3 +3159,51 @@ pk 2 3 DROP TABLE t1; +# +# MDEV-13994: Bad join results with orderby_uses_equalities=on +# +CREATE TABLE books ( +id int(16) NOT NULL AUTO_INCREMENT, +library_id int(16) NOT NULL DEFAULT 0, +wings_id int(12) NOT NULL DEFAULT 0, +scheduled_for_removal int(1) DEFAULT 0, +PRIMARY KEY (id), +KEY library_idx (library_id) +) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; +INSERT INTO books VALUES (32625,8663,707,0),(32624,8663,505,1); +CREATE TABLE wings ( +id int(11) NOT NULL AUTO_INCREMENT, +department_id int(11) DEFAULT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; +INSERT INTO wings VALUES (505,11745),(707,11768); +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='orderby_uses_equalities=off'; +SELECT wings.id as wing_id, wings.department_id FROM wings +WHERE wings.id IN ( SELECT books.wings_id FROM books +WHERE books.library_id = 8663 AND +books.scheduled_for_removal=0 ) +ORDER BY wings.id; +wing_id department_id +707 11768 +SET optimizer_switch='orderby_uses_equalities=on'; +SELECT wings.id as wing_id, wings.department_id FROM wings +WHERE wings.id IN ( SELECT books.wings_id FROM books +WHERE books.library_id = 8663 AND +books.scheduled_for_removal=0 ) +ORDER BY wings.id; +wing_id department_id +707 11768 +explain extended SELECT wings.id as wing_id, wings.department_id FROM wings +WHERE wings.id IN ( SELECT books.wings_id FROM books +WHERE books.library_id = 8663 AND +books.scheduled_for_removal=0 ) +ORDER BY wings.id; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00 Using temporary; Using filesort +1 PRIMARY wings eq_ref PRIMARY PRIMARY 4 test.books.wings_id 1 100.00 +2 MATERIALIZED books ref library_idx library_idx 4 const 1 100.00 Using where +Warnings: +Note 1003 select `test`.`wings`.`id` AS `wing_id`,`test`.`wings`.`department_id` AS `department_id` from `test`.`wings` semi join (`test`.`books`) where `test`.`books`.`library_id` = 8663 and `test`.`books`.`scheduled_for_removal` = 0 and `test`.`wings`.`id` = `test`.`books`.`wings_id` order by `test`.`wings`.`id` +set optimizer_switch= @save_optimizer_switch; +DROP TABLE books, wings; |