CREATE TABLE t1 (f1 int); 

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

CREATE TABLE t2 (f1 int, t1_f1 int); 

INSERT INTO t2 VALUES (674,1),(679,1),(684,1),(689,1),(694,1),(901,1),(608,1),(610,1),(299,1),(667,1),(51951034,2),(281,2),(116,2),(740,2),(974,2);

SET optimizer_switch = 'derived_merge=on'; 

SELECT  dt.tf1, (SELECT COUNT(dt.tf1) FROM t1 WHERE t1.f1 = dt.tf1 AND dt.tf1 = 1) AS val 

FROM (SELECT t1.f1 AS tf1, t2.f1 FROM t1 JOIN t2 ON t1.f1 = t2.t1_f1) AS dt 

GROUP BY dt.tf1; 

set optimizer_switch='derived_merge=OFF'; 

SELECT  dt.tf1, (SELECT COUNT(dt.tf1) FROM t1 WHERE t1.f1 = dt.tf1 AND dt.tf1 = 1) AS val 

FROM (SELECT t1.f1 AS tf1, t2.f1 FROM t1 JOIN t2 ON t1.f1 = t2.t1_f1) AS dt 

GROUP BY dt.tf1; 

MariaDB [test]> SET optimizer_switch = 'derived_merge=on';

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT  dt.tf1, (SELECT COUNT(dt.tf1) FROM t1 WHERE t1.f1 = dt.tf1 AND dt.tf1 = 1) AS val 

    -> FROM (SELECT t1.f1 AS tf1, t2.f1 FROM t1 JOIN t2 ON t1.f1 = t2.t1_f1) AS dt 

    -> GROUP BY dt.tf1; 

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

| tf1  | val  |

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

|    1 | NULL |

|    2 | NULL |

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

2 rows in set (0.001 sec)

MariaDB [test]> explain extended  

    -> SELECT  dt.tf1, (SELECT COUNT(dt.tf1) FROM t1 WHERE t1.f1 = dt.tf1 AND dt.tf1 = 1) AS val 

    -> FROM (SELECT t1.f1 AS tf1, t2.f1 FROM t1 JOIN t2 ON t1.f1 = t2.t1_f1) AS dt 

    -> GROUP BY dt.tf1; 

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

| 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 temporary; Using filesort                 |

|    1 | PRIMARY            | t2    | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | Using where; Using join buffer (flat, BNL join) |

|    2 | DEPENDENT SUBQUERY | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where                                     |

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

3 rows in set, 5 warnings (0.000 sec)

Note (Code 1276): Field or reference 'dt.tf1' of SELECT #2 was resolved in SELECT #1

Note (Code 1981): Aggregate function 'count()' of SELECT #2 belongs to SELECT #1

Note (Code 1276): Field or reference 'dt.tf1' of SELECT #2 was resolved in SELECT #1

Note (Code 1276): Field or reference 'dt.tf1' of SELECT #2 was resolved in SELECT #1

Note (Code 1003): /* select#1 */ select `test`.`t1`.`f1` AS `tf1`,<expr_cache><`test`.`t1`.`f1`,count(`test`.`t1`.`f1`),count(`test`.`t1`.`f1`)>((/* select#2 */ select count(`test`.`t1`.`f1`) from `test`.`t1` where `test`.`t1`.`f1` = 1 and `test`.`t1`.`f1` = 1)) AS `val` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`t1_f1` = `test`.`t1`.`f1` group by `test`.`t1`.`f1`

MariaDB [test]> set optimizer_switch='derived_merge=OFF'; 

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT  dt.tf1, (SELECT COUNT(dt.tf1) FROM t1 WHERE t1.f1 = dt.tf1 AND dt.tf1 = 1) AS val 

    -> FROM (SELECT t1.f1 AS tf1, t2.f1 FROM t1 JOIN t2 ON t1.f1 = t2.t1_f1) AS dt 

    -> GROUP BY dt.tf1; 

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

| tf1  | val  |

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

|    1 |   10 |

|    2 | NULL |

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

2 rows in set (0.000 sec)

MariaDB [test]> explain extended  

    -> SELECT  dt.tf1, (SELECT COUNT(dt.tf1) FROM t1 WHERE t1.f1 = dt.tf1 AND dt.tf1 = 1) AS val 

    -> FROM (SELECT t1.f1 AS tf1, t2.f1 FROM t1 JOIN t2 ON t1.f1 = t2.t1_f1) AS dt 

    -> GROUP BY dt.tf1; 

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

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

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

|    1 | PRIMARY            | <derived3> | ALL  | NULL          | NULL | NULL    | NULL |   30 |   100.00 | Using temporary; Using filesort                 |

|    3 | DERIVED            | t1         | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 |                                                 |

|    3 | DERIVED            | t2         | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | Using where; Using join buffer (flat, BNL join) |

|    2 | DEPENDENT SUBQUERY | t1         | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where                                     |

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

4 rows in set, 5 warnings (0.001 sec)

Note (Code 1276): Field or reference 'dt.tf1' of SELECT #2 was resolved in SELECT #1

Note (Code 1981): Aggregate function 'count()' of SELECT #2 belongs to SELECT #1

Note (Code 1276): Field or reference 'dt.tf1' of SELECT #2 was resolved in SELECT #1

Note (Code 1276): Field or reference 'dt.tf1' of SELECT #2 was resolved in SELECT #1

Note (Code 1003): /* select#1 */ select `dt`.`tf1` AS `tf1`,<expr_cache><`dt`.`tf1`,count(`dt`.`tf1`),count(`dt`.`tf1`)>((/* select#2 */ select count(`dt`.`tf1`) from `test`.`t1` where `test`.`t1`.`f1` = 1 and `dt`.`tf1` = 1)) AS `val` from (/* select#3 */ select `test`.`t1`.`f1` AS `tf1`,`test`.`t2`.`f1` AS `f1` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`t1_f1` = `test`.`t1`.`f1`) `dt` group by `dt`.`tf1`