Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
None
-
None
-
None
Description
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 |
----------
| 979 |
----------
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 |
----------
| 984 |
----------
1 row in set (0.12 sec)