Description:
Hello MySQL team:
I found a problem about argument 'subquery_to_derived' of optimizer_switch may
affected unqualified_count optimization.
How to repeat:
Can be repeated in MySQL 8.4.0
create table t1(c1 int key);
set optimizer_switch = 'subquery_to_derived=off';
explain format=tree select count(0) from t1;
mysql> explain format=tree select count(0) from t1;
+----------------------+
| EXPLAIN |
+----------------------+
| -> Count rows in t1
|
+----------------------+
1 row in set (0.00 sec)
set optimizer_switch = 'subquery_to_derived=on';
explain format=tree select count(0) from t1;
+------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0) (cost=0.65 rows=1)
-> Covering index scan on t1 using idx (cost=0.45 rows=2)
|
+------------------------------------------------------------------------------------------------------------+
Suggested fix:
This is about code of next lines, if we set subquery_to_derived = on, `optimize_aggregated_query` could not be applied.
```cpp
if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_SUBQUERY_TO_DERIVED))
lex->add_statement_options(OPTION_NO_CONST_TABLES);
```
Maybe strategies can be further refined.
Description: Hello MySQL team: I found a problem about argument 'subquery_to_derived' of optimizer_switch may affected unqualified_count optimization. How to repeat: Can be repeated in MySQL 8.4.0 create table t1(c1 int key); set optimizer_switch = 'subquery_to_derived=off'; explain format=tree select count(0) from t1; mysql> explain format=tree select count(0) from t1; +----------------------+ | EXPLAIN | +----------------------+ | -> Count rows in t1 | +----------------------+ 1 row in set (0.00 sec) set optimizer_switch = 'subquery_to_derived=on'; explain format=tree select count(0) from t1; +------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------+ | -> Aggregate: count(0) (cost=0.65 rows=1) -> Covering index scan on t1 using idx (cost=0.45 rows=2) | +------------------------------------------------------------------------------------------------------------+ Suggested fix: This is about code of next lines, if we set subquery_to_derived = on, `optimize_aggregated_query` could not be applied. ```cpp if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_SUBQUERY_TO_DERIVED)) lex->add_statement_options(OPTION_NO_CONST_TABLES); ``` Maybe strategies can be further refined.