|
The following query from subselect_mat_cost.test
select count
from CountryLanguage
where (Language, Country) NOT IN
(SELECT City.Name, Country.Code
FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000));
returns different results with different settings of the optimizer switch:
MariaDB [world]> set optimizer_switch='materialization=off';
Query OK, 0 rows affected (0.00 sec)
MariaDB [world]> EXPLAIN
-> select count
-> from CountryLanguage
-> where (Language, Country) NOT IN
-> (SELECT City.Name, Country.Code
-> FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000));
---
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
---
| 1 |
PRIMARY |
CountryLanguage |
index |
NULL |
PRIMARY |
33 |
NULL |
984 |
Using where; Using index |
| 2 |
DEPENDENT SUBQUERY |
City |
ALL |
NULL |
NULL |
NULL |
NULL |
4079 |
Using where |
| 2 |
DEPENDENT SUBQUERY |
Country |
eq_ref |
PRIMARY |
PRIMARY |
3 |
world.City.Country |
1 |
Using where; Using index |
---
3 rows in set (0.00 sec)
MariaDB [world]>
MariaDB [world]> select count
-> from CountryLanguage
-> where (Language, Country) NOT IN
-> (SELECT City.Name, Country.Code
-> FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000));
----------
| count |
----------
----------
1 row in set (9.33 sec)
MariaDB [world]> set optimizer_switch='materialization=on';
Query OK, 0 rows affected (0.00 sec)
MariaDB [world]> EXPLAIN
-> select count
-> from CountryLanguage
-> where (Language, Country) NOT IN
-> (SELECT City.Name, Country.Code
-> FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000));
---
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
---
| 1 |
PRIMARY |
CountryLanguage |
index |
NULL |
PRIMARY |
33 |
NULL |
984 |
Using where; Using index |
| 2 |
SUBQUERY |
City |
ALL |
NULL |
NULL |
NULL |
NULL |
4079 |
|
| 2 |
SUBQUERY |
Country |
eq_ref |
PRIMARY |
PRIMARY |
3 |
world.City.Country |
1 |
Using where; Using index |
---
3 rows in set (0.01 sec)
MariaDB [world]>
MariaDB [world]> select count
-> from CountryLanguage
-> where (Language, Country) NOT IN
-> (SELECT City.Name, Country.Code
-> FROM City LEFT JOIN Country ON (Country = Code and City.Population < 10000));
----------
| count |
----------
----------
1 row in set (0.12 sec)
|
LPexportBug858038.xml