The result of a query with NOT IN subquery depends on the state of the optimizer switch

[Igor Babaev]

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)