CREATE TABLE t1 (i int);

INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (c int);

INSERT INTO t2 VALUES (3),(2),(3);

SELECT * FROM t1 WHERE i IN (SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );

SET optimizer_switch='materialization=off';

SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );

MariaDB [test]>  SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );

+------+

| i    |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0,000 sec)

MariaDB [test]> explain extended SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );

+------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+----------+-----------------+

| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref       | rows | filtered | Extra           |

+------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+----------+-----------------+

|    1 | PRIMARY      | t1          | ALL    | NULL          | NULL         | NULL    | NULL      | 2    |   100.00 | Using where     |

|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 8       | test.t1.i | 1    |   100.00 | Using where     |

|    2 | MATERIALIZED | t2          | ALL    | NULL          | NULL         | NULL    | NULL      | 3    |   100.00 | Using temporary |

+------+--------------+-------------+--------+---------------+--------------+---------+-----------+------+----------+-----------------+

3 rows in set, 1 warning (0,000 sec)

Note (Code 1003): /* select#1 */ select `test`.`t1`.`i` AS `i` from  <materialize> (/* select#2 */ select count(0) over ( partition by `test`.`t2`.`c`) from `test`.`t2`) join `test`.`t1` where `test`.`t1`.`i` = `<subquery2>`.`COUNT(*) OVER (PARTITION BY c)`

MariaDB [test]> set optimizer_switch='materialization=off';

Query OK, 0 rows affected (0,000 sec)

MariaDB [test]> SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );

Empty set (0,000 sec)

MariaDB [test]> explain extended SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );

+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-----------------+

| id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |

+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-----------------+

|    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL | 2    |   100.00 | Using where     |

|    2 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL | 3    |   100.00 | Using temporary |

+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-----------------+

2 rows in set, 1 warning (0,001 sec)

Note (Code 1003): /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where <expr_cache><`test`.`t1`.`i`>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select count(0) over ( partition by `test`.`t2`.`c`) from `test`.`t2`)))