Semi-join materialization strategy

Semi-join Materialization is a special kind of subquery materialization that's used for Semi-join subqueries.

The idea

select * from Country where Country.code IN (select City.Country from City where City.Population > 7*1000*1000)

TODO: SJM-Scan, SJM-lookup, non-merged cases.

Example

MariaDB [world]> explain select * from Country where Country.code IN (select City.Country from City where  City.Population > 7*1000*1000);
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
| id | select_type  | table       | type   | possible_keys      | key        | key_len | ref                | rows | Extra                 |
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
|  1 | PRIMARY      | <subquery2> | ALL    | distinct_key       | NULL       | NULL    | NULL               |   15 |                       |
|  1 | PRIMARY      | Country     | eq_ref | PRIMARY            | PRIMARY    | 3       | world.City.Country |    1 |                       |
|  2 | MATERIALIZED | City        | range  | Population,Country | Population | 4       | NULL               |   15 | Using index condition |
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
3 rows in set (0.01 sec)


MariaDB [world]> explain select * from Country where Country.code IN (select City.Country from City where  City.Population > 7*1000*1000)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <subquery2>
         type: ALL
possible_keys: distinct_key
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: Country
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: world.City.Country
         rows: 1
        Extra: 
*************************** 3. row ***************************
           id: 2
  select_type: MATERIALIZED
        table: City
         type: range
possible_keys: Population,Country
          key: Population
      key_len: 4
          ref: NULL
         rows: 15
        Extra: Using index condition
3 rows in set (0.00 sec)