CREATE TABLE t1 (a int, b int, INDEX idx(a));

INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,3), (2,1);

CREATE TABLE t2 (a int, b int, INDEX idx(a));

INSERT INTO t2 VALUES (2,1), (6,4), (7,6), (9,4);

CREATE TABLE t3 (a int, b int);

INSERT INTO t3 VALUES (1,0), (1,1), (1,3);

SELECT * FROM t3

  WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2

                WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9

                      AND t3.b = t1.b

              GROUP BY t1.b);

MariaDB [test]> SELECT * FROM t3

    ->   WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2

    ->                 WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9

    ->                       AND t3.b = t1.b

    ->               GROUP BY t1.b);

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

| a    | b    |

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

|    1 |    0 |

|    1 |    3 |

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

MariaDB [test]> EXPLAIN

    -> SELECT * FROM t3

    ->   WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2

    ->                 WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9

    ->                       AND t3.b = t1.b

    ->               GROUP BY t1.b);

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

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

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

|  1 | PRIMARY            | t3    | ALL   | NULL          | NULL | NULL    | NULL      |    3 | Using where                                        |

|  2 | DEPENDENT SUBQUERY | t1    | range | idx           | idx  | 5       | NULL      |    2 | Using index condition; Using where; Using filesort |

|  2 | DEPENDENT SUBQUERY | t2    | ref   | idx           | idx  | 5       | test.t1.a |    2 | Using index condition                              |

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

3 rows in set (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT * FROM t3

    ->   WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2

    ->                 WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9

    ->                       AND t3.b = t1.b

    ->               GROUP BY t1.b);

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

| a    | b    |

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

|    1 |    0 |

|    1 |    1 |

|    1 |    3 |

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

3 rows in set (0.00 sec)