subquery execution should be displayed in explain and explain analyze

[SunRunAway]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> explain select * from t where 0 = (select sleep(3));
+------------------------+---------+-----------+---------------+--------------------------------+
| id                     | estRows | task      | access object | operator info                  |
+------------------------+---------+-----------+---------------+--------------------------------+
| TableReader_12         | 1.00    | root      |               | data:TableFullScan_11          |
| └─TableFullScan_11     | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+------------------------+---------+-----------+---------------+--------------------------------+
2 rows in set (3.00 sec)

mysql> explain analyze select * from t where 0 = (select sleep(3));
+------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-----------+------+
| id                     | estRows | actRows | task      | access object | execution info                                                                                                                     | operator info                  | memory    | disk |
+------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-----------+------+
| TableReader_12         | 1.00    | 1       | root      |               | time:205.2µs, loops:2, cop_task: {num: 1, max: 115.7µs, proc_keys: 0, rpc_num: 1, rpc_time: 83.1µs, copr_cache_hit_ratio: 0.00}    | data:TableFullScan_11          | 150 Bytes | N/A  |
| └─TableFullScan_11     | 1.00    | 1       | cop[tikv] | table:t       | tikv_task:{time:1.8µs, loops:1}                                                                                                    | keep order:false, stats:pseudo | N/A       | N/A  |
+------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-----------+------+
2 rows in set (3.00 sec)

2. What did you expect to see? (Required)

1. explain:
   • The subquery execution plan should be displayed in the explain results
   • The explain query should not run for 3 seconds
   • The explain query should not really execute the subquery.
2. explain analyze:
   • The subquery execution plan should be displayed in the explain analyze results
   • The subquery execution time (3 seconds) should be recorded in the explain analyze results.

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

[Reminiscent]

/assign

[Reminiscent]

I think it's not a bug here. In MySQL8.0

mysql> explain select * from t where 0 = (select sleep(3));
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type          | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY              | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
|  2 | UNCACHEABLE SUBQUERY | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (3.01 sec)

mysql> explain analyze select * from t where 0 = (select sleep(3));
+-----------------------------------------------------------------------------------+
| EXPLAIN                                                                           |
+-----------------------------------------------------------------------------------+
| -> Table scan on t  (cost=0.35 rows=1) (actual time=0.053..0.053 rows=0 loops=1)
 |
+-----------------------------------------------------------------------------------+
1 row in set (3.00 sec)

Both explain and explain analyze query will execute the expression sleep(3). And the explain analyze query will not display the subquery plan. And I think it's not easy to optimize in TiDB. What's your opinion? @SunRunAway @winoros @qw4990

[qw4990]

I think it's not a bug here. In MySQL8.0

mysql> explain select * from t where 0 = (select sleep(3));
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type          | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY              | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
|  2 | UNCACHEABLE SUBQUERY | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+----------------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (3.01 sec)

mysql> explain analyze select * from t where 0 = (select sleep(3));
+-----------------------------------------------------------------------------------+
| EXPLAIN                                                                           |
+-----------------------------------------------------------------------------------+
| -> Table scan on t  (cost=0.35 rows=1) (actual time=0.053..0.053 rows=0 loops=1)
 |
+-----------------------------------------------------------------------------------+
1 row in set (3.00 sec)

Both explain and explain analyze query will execute the expression sleep(3). And the explain analyze query will not display the subquery plan. And I think it's not easy to optimize in TiDB. What's your opinion? @SunRunAway @winoros @qw4990

Agree, how about identifying this issue as an enhancement instead of a bug? @SunRunAway

[Jason-93]

It's same as MySQL Bug #67632
Maybe it would be a better choice not to execute constant expressions

[qw4990]

This is a known issue and it is not easy to resolve.
After discussing with @winoros , we decided to identify it as a feature-request/enhancement instead of a bug, and we will fix it in the next few months.

[francis0407]

/assign

[francis0407]

Hi all, here is my implementation plan for this issue:

• The physical plan of the uncorrelated scalar subquery will be stored in StmtCtx.
• The subquery will not be executed if it is inside an explain statement. But it will be executed in explain analyze.
• When using explain only, the scalar subquery result will be replaced by a special ScalarFunction which will never be evaluated.

The problem of this implementation is that the result of explain and explain analyze are not consistent.

Some examples:

Only one subquery:

mysql> explain select t.a from t where t.a = (select sum(b) from t t2);
+--------------------------------+---------+-----------+---------------+---------------------------------------------------------------+
| id                             | estRows | task      | access object | operator info                                                 |
+--------------------------------+---------+-----------+---------------+---------------------------------------------------------------+
| Selection_27                   | 0.80    | root      |               | eq(cast(test.t.a, decimal(20,0) BINARY), scalarsubquery0-0()) |
| └─TableReader_29               | 1.00    | root      |               | data:TableFullScan_28                                         |
|   └─TableFullScan_28           | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo                                |
| ScalarSubquery_0               | 1.00    | root      |               | scalarsubquery0-0()                                           |
| └─MaxOneRow_7                  | 1.00    | root      |               |                                                               |
|   └─StreamAgg_13               | 1.00    | root      |               | funcs:sum(Column#10)->Column#7                                |
|     └─Projection_24            | 1.00    | root      |               | cast(test.t.b, decimal(32,0) BINARY)->Column#10               |
|       └─TableReader_23         | 1.00    | root      |               | data:TableFullScan_22                                         |
|         └─TableFullScan_22     | 1.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                |
+--------------------------------+---------+-----------+---------------+---------------------------------------------------------------+
9 rows in set (0.00 sec)

mysql> explain analyze select t.a from t where t.a = (select sum(b) from t t2);
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| id                             | estRows | actRows | task      | access object | execution info                                                                                                                      | operator info                                   | memory    | disk |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| TableReader_29                 | 0.00    | 1       | root      |               | time:620.7µs, loops:2, cop_task: {num: 1, max: 453.5µs, proc_keys: 0, rpc_num: 1, rpc_time: 383.7µs, copr_cache_hit_ratio: 0.00}    | data:Selection_28                               | 167 Bytes | N/A  |
| └─Selection_28                 | 0.00    | 1       | cop[tikv] |               | tikv_task:{time:12.1µs, loops:1}                                                                                                    | eq(test.t.a, 1)                                 | N/A       | N/A  |
|   └─TableFullScan_27           | 1.00    | 1       | cop[tikv] | table:t       | tikv_task:{time:2.49µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
| ScalarSubquery_0               | 1.00    | 1       | root      |               | time:372.4µs, loops:1                                                                                                               | 1                                               | N/A       | N/A  |
| └─MaxOneRow_7                  | 1.00    | 1       | root      |               | time:372.4µs, loops:1                                                                                                               |                                                 | N/A       | N/A  |
|   └─StreamAgg_13               | 1.00    | 1       | root      |               | time:368.6µs, loops:2                                                                                                               | funcs:sum(Column#10)->Column#7                  | 1.44 KB   | N/A  |
|     └─Projection_24            | 1.00    | 1       | root      |               | time:359.8µs, loops:2, Concurrency:OFF                                                                                              | cast(test.t.b, decimal(32,0) BINARY)->Column#10 | 372 Bytes | N/A  |
|       └─TableReader_23         | 1.00    | 1       | root      |               | time:321.4µs, loops:2, cop_task: {num: 1, max: 167.5µs, proc_keys: 0, rpc_num: 1, rpc_time: 142.9µs, copr_cache_hit_ratio: 0.00}    | data:TableFullScan_22                           | 158 Bytes | N/A  |
|         └─TableFullScan_22     | 1.00    | 1       | cop[tikv] | table:t2      | tikv_task:{time:9.28µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
9 rows in set (0.00 sec)

Two subqueries:

mysql> explain select t.a from t where t.b = (select sum(b) from t t2) and t.a = (select avg(a) from t t2);
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
| id                             | estRows | task      | access object | operator info                                                                                                                |
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
| Projection_48                  | 0.80    | root      |               | test.t.a                                                                                                                     |
| └─Selection_49                 | 0.80    | root      |               | eq(cast(test.t.a, decimal(20,0) BINARY), scalarsubquery1-0()), eq(cast(test.t.b, decimal(20,0) BINARY), scalarsubquery0-0()) |
|   └─TableReader_51             | 1.00    | root      |               | data:TableFullScan_50                                                                                                        |
|     └─TableFullScan_50         | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                                                               |
| ScalarSubquery_0               | 1.00    | root      |               | scalarsubquery0-0()                                                                                                          |
| └─MaxOneRow_7                  | 1.00    | root      |               |                                                                                                                              |
|   └─StreamAgg_13               | 1.00    | root      |               | funcs:sum(Column#10)->Column#7                                                                                               |
|     └─Projection_24            | 1.00    | root      |               | cast(test.t.b, decimal(32,0) BINARY)->Column#10                                                                              |
|       └─TableReader_23         | 1.00    | root      |               | data:TableFullScan_22                                                                                                        |
|         └─TableFullScan_22     | 1.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                               |
| ScalarSubquery_1                | 1.00    | root      |               | scalarsubquery1-0()                                                                                                          |
| └─MaxOneRow_29                 | 1.00    | root      |               |                                                                                                                              |
|   └─StreamAgg_35               | 1.00    | root      |               | funcs:avg(Column#19)->Column#14                                                                                              |
|     └─Projection_46            | 1.00    | root      |               | cast(test.t.a, decimal(15,4) BINARY)->Column#19                                                                              |
|       └─TableReader_45         | 1.00    | root      |               | data:TableFullScan_44                                                                                                        |
|         └─TableFullScan_44     | 1.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                               |
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)

mysql> explain analyze select t.a from t where t.b = (select sum(b) from t t2) and t.a = (select avg(a) from t t2);
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| id                             | estRows | actRows | task      | access object | execution info                                                                                                                      | operator info                                   | memory    | disk |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| Projection_48                  | 0.00    | 1       | root      |               | time:495.4µs, loops:2, Concurrency:OFF                                                                                              | test.t.a                                        | 744 Bytes | N/A  |
| └─TableReader_51               | 0.00    | 1       | root      |               | time:489.9µs, loops:2, cop_task: {num: 1, max: 370.6µs, proc_keys: 0, rpc_num: 1, rpc_time: 334.9µs, copr_cache_hit_ratio: 0.00}    | data:Selection_50                               | 169 Bytes | N/A  |
|   └─Selection_50               | 0.00    | 1       | cop[tikv] |               | tikv_task:{time:13.2µs, loops:1}                                                                                                    | eq(test.t.a, 1), eq(test.t.b, 1)                | N/A       | N/A  |
|     └─TableFullScan_49         | 1.00    | 1       | cop[tikv] | table:t       | tikv_task:{time:4.33µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
| ScalarSubquery_0               | 1.00    | 1       | root      |               | time:444.9µs, loops:1                                                                                                               | 1                                               | N/A       | N/A  |
| └─MaxOneRow_7                  | 1.00    | 1       | root      |               | time:444.9µs, loops:1                                                                                                               |                                                 | N/A       | N/A  |
|   └─StreamAgg_13               | 1.00    | 1       | root      |               | time:433.4µs, loops:2                                                                                                               | funcs:sum(Column#10)->Column#7                  | 1.44 KB   | N/A  |
|     └─Projection_24            | 1.00    | 1       | root      |               | time:422.7µs, loops:2, Concurrency:OFF                                                                                              | cast(test.t.b, decimal(32,0) BINARY)->Column#10 | 372 Bytes | N/A  |
|       └─TableReader_23         | 1.00    | 1       | root      |               | time:398.1µs, loops:2, cop_task: {num: 1, max: 196.1µs, proc_keys: 0, rpc_num: 1, rpc_time: 157.3µs, copr_cache_hit_ratio: 0.00}    | data:TableFullScan_22                           | 158 Bytes | N/A  |
|         └─TableFullScan_22     | 1.00    | 1       | cop[tikv] | table:t2      | tikv_task:{time:3.69µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
| ScalarSubquery_1               | 1.00    | 1       | root      |               | time:275.6µs, loops:1                                                                                                               | 1.0000                                          | N/A       | N/A  |
| └─MaxOneRow_29                 | 1.00    | 1       | root      |               | time:275.6µs, loops:1                                                                                                               |                                                 | N/A       | N/A  |
|   └─StreamAgg_35               | 1.00    | 1       | root      |               | time:272.4µs, loops:2                                                                                                               | funcs:avg(Column#19)->Column#14                 | 1.44 KB   | N/A  |
|     └─Projection_46            | 1.00    | 1       | root      |               | time:259.8µs, loops:2, Concurrency:OFF                                                                                              | cast(test.t.a, decimal(15,4) BINARY)->Column#19 | 372 Bytes | N/A  |
|       └─TableReader_45         | 1.00    | 1       | root      |               | time:244µs, loops:2, cop_task: {num: 1, max: 128.7µs, proc_keys: 0, rpc_num: 1, rpc_time: 106.6µs, copr_cache_hit_ratio: 0.00}      | data:TableFullScan_44                           | 158 Bytes | N/A  |
|         └─TableFullScan_44     | 1.00    | 1       | cop[tikv] | table:t2      | tikv_task:{time:2.26µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
16 rows in set (0.01 sec)

Subquery with multiple columns:

mysql> explain select t.a from t where (t.a, t.b) = (select sum(a), sum(b) from t t2);
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
| id                             | estRows | task      | access object | operator info                                                                                                                |
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
| Projection_26                  | 0.80    | root      |               | test.t.a                                                                                                                     |
| └─Selection_27                 | 0.80    | root      |               | eq(cast(test.t.a, decimal(20,0) BINARY), scalarsubquery0-0()), eq(cast(test.t.b, decimal(20,0) BINARY), scalarsubquery0-1()) |
|   └─TableReader_29             | 1.00    | root      |               | data:TableFullScan_28                                                                                                        |
|     └─TableFullScan_28         | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                                                               |
| ScalarSubquery_0               | 1.00    | root      |               | row(scalarsubquery0-0(), scalarsubquery0-1())                                                                                |
| └─MaxOneRow_7                  | 1.00    | root      |               |                                                                                                                              |
|   └─StreamAgg_13               | 1.00    | root      |               | funcs:sum(Column#13)->Column#7, funcs:sum(Column#14)->Column#8                                                               |
|     └─Projection_24            | 1.00    | root      |               | cast(test.t.a, decimal(32,0) BINARY)->Column#13, cast(test.t.b, decimal(32,0) BINARY)->Column#14                             |
|       └─TableReader_23         | 1.00    | root      |               | data:TableFullScan_22                                                                                                        |
|         └─TableFullScan_22     | 1.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                               |
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

mysql> explain analyze select t.a from t where (t.a, t.b) = (select sum(a), sum(b) from t t2);
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+-----------+------+
| id                             | estRows | actRows | task      | access object | execution info                                                                                                                      | operator info                                                                                    | memory    | disk |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+-----------+------+
| Projection_26                  | 0.00    | 1       | root      |               | time:603.4µs, loops:2, Concurrency:OFF                                                                                              | test.t.a                                                                                         | 744 Bytes | N/A  |
| └─TableReader_29               | 0.00    | 1       | root      |               | time:596.9µs, loops:2, cop_task: {num: 1, max: 463.3µs, proc_keys: 0, rpc_num: 1, rpc_time: 426.7µs, copr_cache_hit_ratio: 0.00}    | data:Selection_28                                                                                | 169 Bytes | N/A  |
|   └─Selection_28               | 0.00    | 1       | cop[tikv] |               | tikv_task:{time:10.7µs, loops:1}                                                                                                    | eq(test.t.a, 1), eq(test.t.b, 1)                                                                 | N/A       | N/A  |
|     └─TableFullScan_27         | 1.00    | 1       | cop[tikv] | table:t       | tikv_task:{time:3.12µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                                                                   | N/A       | N/A  |
| ScalarSubquery_0               | 1.00    | 1       | root      |               | time:372.8µs, loops:1                                                                                                               | row(1, 1)                                                                                        | N/A       | N/A  |
| └─MaxOneRow_7                  | 1.00    | 1       | root      |               | time:372.8µs, loops:1                                                                                                               |                                                                                                  | N/A       | N/A  |
|   └─StreamAgg_13               | 1.00    | 1       | root      |               | time:354.3µs, loops:2                                                                                                               | funcs:sum(Column#13)->Column#7, funcs:sum(Column#14)->Column#8                                   | 2.88 KB   | N/A  |
|     └─Projection_24            | 1.00    | 1       | root      |               | time:344.3µs, loops:2, Concurrency:OFF                                                                                              | cast(test.t.a, decimal(32,0) BINARY)->Column#13, cast(test.t.b, decimal(32,0) BINARY)->Column#14 | 744 Bytes | N/A  |
|       └─TableReader_23         | 1.00    | 1       | root      |               | time:291.9µs, loops:2, cop_task: {num: 1, max: 151.2µs, proc_keys: 0, rpc_num: 1, rpc_time: 124.3µs, copr_cache_hit_ratio: 0.00}    | data:TableFullScan_22                                                                            | 160 Bytes | N/A  |
|         └─TableFullScan_22     | 1.00    | 1       | cop[tikv] | table:t2      | tikv_task:{time:3.25µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                                                                   | N/A       | N/A  |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+-----------+------+
10 rows in set (0.00 sec)

Any suggestion? cc @SunRunAway @qw4990 @winoros

[winoros]

Hi all, here is my implementation plan for this issue:

• The physical plan of the uncorrelated scalar subquery will be stored in StmtCtx.
• The subquery will not be executed if it is inside an explain statement. But it will be executed in explain analyze.
• When using explain only, the scalar subquery result will be replaced by a special ScalarFunction which will never be evaluated.

The problem of this implementation is that the result of explain and explain analyze are not consistent.

Some examples:

Only one subquery:

mysql> explain select t.a from t where t.a = (select sum(b) from t t2);
+--------------------------------+---------+-----------+---------------+---------------------------------------------------------------+
| id                             | estRows | task      | access object | operator info                                                 |
+--------------------------------+---------+-----------+---------------+---------------------------------------------------------------+
| Selection_27                   | 0.80    | root      |               | eq(cast(test.t.a, decimal(20,0) BINARY), scalarsubquery0-0()) |
| └─TableReader_29               | 1.00    | root      |               | data:TableFullScan_28                                         |
|   └─TableFullScan_28           | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo                                |
| ScalarSubquery_0               | 1.00    | root      |               | scalarsubquery0-0()                                           |
| └─MaxOneRow_7                  | 1.00    | root      |               |                                                               |
|   └─StreamAgg_13               | 1.00    | root      |               | funcs:sum(Column#10)->Column#7                                |
|     └─Projection_24            | 1.00    | root      |               | cast(test.t.b, decimal(32,0) BINARY)->Column#10               |
|       └─TableReader_23         | 1.00    | root      |               | data:TableFullScan_22                                         |
|         └─TableFullScan_22     | 1.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                |
+--------------------------------+---------+-----------+---------------+---------------------------------------------------------------+
9 rows in set (0.00 sec)

mysql> explain analyze select t.a from t where t.a = (select sum(b) from t t2);
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| id                             | estRows | actRows | task      | access object | execution info                                                                                                                      | operator info                                   | memory    | disk |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| TableReader_29                 | 0.00    | 1       | root      |               | time:620.7µs, loops:2, cop_task: {num: 1, max: 453.5µs, proc_keys: 0, rpc_num: 1, rpc_time: 383.7µs, copr_cache_hit_ratio: 0.00}    | data:Selection_28                               | 167 Bytes | N/A  |
| └─Selection_28                 | 0.00    | 1       | cop[tikv] |               | tikv_task:{time:12.1µs, loops:1}                                                                                                    | eq(test.t.a, 1)                                 | N/A       | N/A  |
|   └─TableFullScan_27           | 1.00    | 1       | cop[tikv] | table:t       | tikv_task:{time:2.49µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
| ScalarSubquery_0               | 1.00    | 1       | root      |               | time:372.4µs, loops:1                                                                                                               | 1                                               | N/A       | N/A  |
| └─MaxOneRow_7                  | 1.00    | 1       | root      |               | time:372.4µs, loops:1                                                                                                               |                                                 | N/A       | N/A  |
|   └─StreamAgg_13               | 1.00    | 1       | root      |               | time:368.6µs, loops:2                                                                                                               | funcs:sum(Column#10)->Column#7                  | 1.44 KB   | N/A  |
|     └─Projection_24            | 1.00    | 1       | root      |               | time:359.8µs, loops:2, Concurrency:OFF                                                                                              | cast(test.t.b, decimal(32,0) BINARY)->Column#10 | 372 Bytes | N/A  |
|       └─TableReader_23         | 1.00    | 1       | root      |               | time:321.4µs, loops:2, cop_task: {num: 1, max: 167.5µs, proc_keys: 0, rpc_num: 1, rpc_time: 142.9µs, copr_cache_hit_ratio: 0.00}    | data:TableFullScan_22                           | 158 Bytes | N/A  |
|         └─TableFullScan_22     | 1.00    | 1       | cop[tikv] | table:t2      | tikv_task:{time:9.28µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
9 rows in set (0.00 sec)

Two subqueries:

mysql> explain select t.a from t where t.b = (select sum(b) from t t2) and t.a = (select avg(a) from t t2);
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
| id                             | estRows | task      | access object | operator info                                                                                                                |
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
| Projection_48                  | 0.80    | root      |               | test.t.a                                                                                                                     |
| └─Selection_49                 | 0.80    | root      |               | eq(cast(test.t.a, decimal(20,0) BINARY), scalarsubquery1-0()), eq(cast(test.t.b, decimal(20,0) BINARY), scalarsubquery0-0()) |
|   └─TableReader_51             | 1.00    | root      |               | data:TableFullScan_50                                                                                                        |
|     └─TableFullScan_50         | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                                                               |
| ScalarSubquery_0               | 1.00    | root      |               | scalarsubquery0-0()                                                                                                          |
| └─MaxOneRow_7                  | 1.00    | root      |               |                                                                                                                              |
|   └─StreamAgg_13               | 1.00    | root      |               | funcs:sum(Column#10)->Column#7                                                                                               |
|     └─Projection_24            | 1.00    | root      |               | cast(test.t.b, decimal(32,0) BINARY)->Column#10                                                                              |
|       └─TableReader_23         | 1.00    | root      |               | data:TableFullScan_22                                                                                                        |
|         └─TableFullScan_22     | 1.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                               |
| ScalarSubquery_1                | 1.00    | root      |               | scalarsubquery1-0()                                                                                                          |
| └─MaxOneRow_29                 | 1.00    | root      |               |                                                                                                                              |
|   └─StreamAgg_35               | 1.00    | root      |               | funcs:avg(Column#19)->Column#14                                                                                              |
|     └─Projection_46            | 1.00    | root      |               | cast(test.t.a, decimal(15,4) BINARY)->Column#19                                                                              |
|       └─TableReader_45         | 1.00    | root      |               | data:TableFullScan_44                                                                                                        |
|         └─TableFullScan_44     | 1.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                               |
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)

mysql> explain analyze select t.a from t where t.b = (select sum(b) from t t2) and t.a = (select avg(a) from t t2);
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| id                             | estRows | actRows | task      | access object | execution info                                                                                                                      | operator info                                   | memory    | disk |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
| Projection_48                  | 0.00    | 1       | root      |               | time:495.4µs, loops:2, Concurrency:OFF                                                                                              | test.t.a                                        | 744 Bytes | N/A  |
| └─TableReader_51               | 0.00    | 1       | root      |               | time:489.9µs, loops:2, cop_task: {num: 1, max: 370.6µs, proc_keys: 0, rpc_num: 1, rpc_time: 334.9µs, copr_cache_hit_ratio: 0.00}    | data:Selection_50                               | 169 Bytes | N/A  |
|   └─Selection_50               | 0.00    | 1       | cop[tikv] |               | tikv_task:{time:13.2µs, loops:1}                                                                                                    | eq(test.t.a, 1), eq(test.t.b, 1)                | N/A       | N/A  |
|     └─TableFullScan_49         | 1.00    | 1       | cop[tikv] | table:t       | tikv_task:{time:4.33µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
| ScalarSubquery_0               | 1.00    | 1       | root      |               | time:444.9µs, loops:1                                                                                                               | 1                                               | N/A       | N/A  |
| └─MaxOneRow_7                  | 1.00    | 1       | root      |               | time:444.9µs, loops:1                                                                                                               |                                                 | N/A       | N/A  |
|   └─StreamAgg_13               | 1.00    | 1       | root      |               | time:433.4µs, loops:2                                                                                                               | funcs:sum(Column#10)->Column#7                  | 1.44 KB   | N/A  |
|     └─Projection_24            | 1.00    | 1       | root      |               | time:422.7µs, loops:2, Concurrency:OFF                                                                                              | cast(test.t.b, decimal(32,0) BINARY)->Column#10 | 372 Bytes | N/A  |
|       └─TableReader_23         | 1.00    | 1       | root      |               | time:398.1µs, loops:2, cop_task: {num: 1, max: 196.1µs, proc_keys: 0, rpc_num: 1, rpc_time: 157.3µs, copr_cache_hit_ratio: 0.00}    | data:TableFullScan_22                           | 158 Bytes | N/A  |
|         └─TableFullScan_22     | 1.00    | 1       | cop[tikv] | table:t2      | tikv_task:{time:3.69µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
| ScalarSubquery_1               | 1.00    | 1       | root      |               | time:275.6µs, loops:1                                                                                                               | 1.0000                                          | N/A       | N/A  |
| └─MaxOneRow_29                 | 1.00    | 1       | root      |               | time:275.6µs, loops:1                                                                                                               |                                                 | N/A       | N/A  |
|   └─StreamAgg_35               | 1.00    | 1       | root      |               | time:272.4µs, loops:2                                                                                                               | funcs:avg(Column#19)->Column#14                 | 1.44 KB   | N/A  |
|     └─Projection_46            | 1.00    | 1       | root      |               | time:259.8µs, loops:2, Concurrency:OFF                                                                                              | cast(test.t.a, decimal(15,4) BINARY)->Column#19 | 372 Bytes | N/A  |
|       └─TableReader_45         | 1.00    | 1       | root      |               | time:244µs, loops:2, cop_task: {num: 1, max: 128.7µs, proc_keys: 0, rpc_num: 1, rpc_time: 106.6µs, copr_cache_hit_ratio: 0.00}      | data:TableFullScan_44                           | 158 Bytes | N/A  |
|         └─TableFullScan_44     | 1.00    | 1       | cop[tikv] | table:t2      | tikv_task:{time:2.26µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                  | N/A       | N/A  |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+-----------+------+
16 rows in set (0.01 sec)

Subquery with multiple columns:

mysql> explain select t.a from t where (t.a, t.b) = (select sum(a), sum(b) from t t2);
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
| id                             | estRows | task      | access object | operator info                                                                                                                |
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
| Projection_26                  | 0.80    | root      |               | test.t.a                                                                                                                     |
| └─Selection_27                 | 0.80    | root      |               | eq(cast(test.t.a, decimal(20,0) BINARY), scalarsubquery0-0()), eq(cast(test.t.b, decimal(20,0) BINARY), scalarsubquery0-1()) |
|   └─TableReader_29             | 1.00    | root      |               | data:TableFullScan_28                                                                                                        |
|     └─TableFullScan_28         | 1.00    | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                                                               |
| ScalarSubquery_0               | 1.00    | root      |               | row(scalarsubquery0-0(), scalarsubquery0-1())                                                                                |
| └─MaxOneRow_7                  | 1.00    | root      |               |                                                                                                                              |
|   └─StreamAgg_13               | 1.00    | root      |               | funcs:sum(Column#13)->Column#7, funcs:sum(Column#14)->Column#8                                                               |
|     └─Projection_24            | 1.00    | root      |               | cast(test.t.a, decimal(32,0) BINARY)->Column#13, cast(test.t.b, decimal(32,0) BINARY)->Column#14                             |
|       └─TableReader_23         | 1.00    | root      |               | data:TableFullScan_22                                                                                                        |
|         └─TableFullScan_22     | 1.00    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                               |
+--------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

mysql> explain analyze select t.a from t where (t.a, t.b) = (select sum(a), sum(b) from t t2);
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+-----------+------+
| id                             | estRows | actRows | task      | access object | execution info                                                                                                                      | operator info                                                                                    | memory    | disk |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+-----------+------+
| Projection_26                  | 0.00    | 1       | root      |               | time:603.4µs, loops:2, Concurrency:OFF                                                                                              | test.t.a                                                                                         | 744 Bytes | N/A  |
| └─TableReader_29               | 0.00    | 1       | root      |               | time:596.9µs, loops:2, cop_task: {num: 1, max: 463.3µs, proc_keys: 0, rpc_num: 1, rpc_time: 426.7µs, copr_cache_hit_ratio: 0.00}    | data:Selection_28                                                                                | 169 Bytes | N/A  |
|   └─Selection_28               | 0.00    | 1       | cop[tikv] |               | tikv_task:{time:10.7µs, loops:1}                                                                                                    | eq(test.t.a, 1), eq(test.t.b, 1)                                                                 | N/A       | N/A  |
|     └─TableFullScan_27         | 1.00    | 1       | cop[tikv] | table:t       | tikv_task:{time:3.12µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                                                                   | N/A       | N/A  |
| ScalarSubquery_0               | 1.00    | 1       | root      |               | time:372.8µs, loops:1                                                                                                               | row(1, 1)                                                                                        | N/A       | N/A  |
| └─MaxOneRow_7                  | 1.00    | 1       | root      |               | time:372.8µs, loops:1                                                                                                               |                                                                                                  | N/A       | N/A  |
|   └─StreamAgg_13               | 1.00    | 1       | root      |               | time:354.3µs, loops:2                                                                                                               | funcs:sum(Column#13)->Column#7, funcs:sum(Column#14)->Column#8                                   | 2.88 KB   | N/A  |
|     └─Projection_24            | 1.00    | 1       | root      |               | time:344.3µs, loops:2, Concurrency:OFF                                                                                              | cast(test.t.a, decimal(32,0) BINARY)->Column#13, cast(test.t.b, decimal(32,0) BINARY)->Column#14 | 744 Bytes | N/A  |
|       └─TableReader_23         | 1.00    | 1       | root      |               | time:291.9µs, loops:2, cop_task: {num: 1, max: 151.2µs, proc_keys: 0, rpc_num: 1, rpc_time: 124.3µs, copr_cache_hit_ratio: 0.00}    | data:TableFullScan_22                                                                            | 160 Bytes | N/A  |
|         └─TableFullScan_22     | 1.00    | 1       | cop[tikv] | table:t2      | tikv_task:{time:3.25µs, loops:1}                                                                                                    | keep order:false, stats:pseudo                                                                   | N/A       | N/A  |
+--------------------------------+---------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------+-----------+------+
10 rows in set (0.00 sec)

Any suggestion? cc @SunRunAway @qw4990 @winoros

@francis0407 I've pushed a new pull #45252 .
This pull reserves the later codebase for #11414
Instead of using a simple scalar function to represent the scalar function. We implement a full functional expression for it.
So later, we can reuse most of the code when implementing the correlated version in #11414.