Details
Description
INSTALL SONAME 'ha_federatedx'; |
|
eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT); |
set optimizer_switch="materialization=off"; |
|
CREATE TABLE t1 (a INT); |
INSERT INTO t1 VALUES (3),(2),(3); |
|
CREATE TABLE t2 (pk INT PRIMARY KEY); |
INSERT INTO t2 VALUES (1),(2),(3),(4); |
|
CREATE TABLE t2_fed ENGINE=FEDERATED CONNECTION='s/t2'; |
|
SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 ); |
SET optimizer_switch='semijoin=off'; |
SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 ); |
|
# Cleanup
|
|
DROP TABLE t2_fed, t1, t2; |
DROP SERVER s; |
|
UNINSTALL SONAME 'ha_federatedx'; |
bb-11.0 78c07ed17 |
SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 ); |
pk
|
3
|
2
|
3
|
SET optimizer_switch='semijoin=off'; |
SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 ); |
pk
|
2
|
3
|
2 rows which are returned without semijoin is the expected result.
Plans with semijoin enabled:
bb-11.0 |
EXPLAIN EXTENDED SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 ); |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Start temporary |
1 PRIMARY t2_fed eq_ref PRIMARY PRIMARY 4 test.t1.a 1 33.33 End temporary |
Warnings:
|
Note 1003 select `test`.`t2_fed`.`pk` AS `pk` from `test`.`t2_fed` semi join (`test`.`t1`) where `test`.`t2_fed`.`pk` = `test`.`t1`.`a` |
baseline |
EXPLAIN EXTENDED SELECT * FROM t2_fed WHERE pk IN ( SELECT a FROM t1 ); |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 |
1 PRIMARY t2_fed eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 |
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 100.00 Using where |
Warnings:
|
Note 1003 select `test`.`t2_fed`.`pk` AS `pk` from `test`.`t2_fed` semi join (`test`.`t1`) where `test`.`t2_fed`.`pk` = `test`.`t1`.`a` |
Attachments
Issue Links
- is caused by
-
MDEV-26974 Improve selectivity and related costs in optimizer
- Closed
- relates to
-
MDEV-30569 Assertion `!(p->table->table->file->ha_table_flags() & (1ULL << 60))' failed in Duplicate_weedout_picker::check_qep
- Closed