Bug #97296 EXPLAIN ANALYZE does not execute the subqueries in the select list

Description:
EXPLAIN ANALYZE does not compute expressions in the select list. If these expressions are expensive (e.g. subqueries), then EXPLAIN ANALYZE output will not be an accurate description of what happens when one runs the corresponding select.

How to repeat:
create table ten(a int primary key);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table one_k(a int primary key);
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;

create table one_m (a int);
insert into one_m select A.a+1000*B.a from one_k A, one_k B;

# Run a query with an expensive correlated subquery in the select list:

mysql> select a, (select count(*) from one_m where one_m.a< ten.a) from ten;
+------+---------------------------------------------------+
| a    | (select count(*) from one_m where one_m.a< ten.a) |
+------+---------------------------------------------------+
|    0 |                                                 0 |
|    1 |                                                 1 |
|    2 |                                                 2 |
|    3 |                                                 3 |
|    4 |                                                 4 |
|    5 |                                                 5 |
|    6 |                                                 6 |
|    7 |                                                 7 |
|    8 |                                                 8 |
|    9 |                                                 9 |
+------+---------------------------------------------------+
10 rows in set (1 min 54.57 sec)

mysql> explain analyze select a, (select count(*) from one_m where one_m.a< ten.a) from ten\G
*************************** 1. row ***************************
EXPLAIN: -> Index scan on ten using PRIMARY  (cost=1.25 rows=10) (actual time=0.274..0.424 rows=10 loops=1)
-> Select #2 (subquery in projection; dependent)
    -> Aggregate: count(0)  (never executed)
        -> Filter: (one_m.a < ten.a)  (cost=33787.30 rows=332906) (never executed)
            -> Table scan on one_m  (cost=33787.30 rows=998819) (never executed)

1 row in set, 1 warning (0.01 sec)

EXPLAIN ANALYZE runs instantly and shows that the subquery is never executed.

Hello Sergei,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

Documented fix as follows in the MySQL 8.0.19 changelog:

    EXPLAIN ANALYZE did not execute subqueries in the SELECT list,
    and thus did not take them into account in its calculations of
    time or cost.

Closed.