For some scenarios of aggregate queries, scalar subqueries can be eliminated

[King-Dylan]

Enhancement

For some scenarios of aggregate queries, scalar subqueries and the other functions can be eliminated,For example:

CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  KEY `idx_a` (`a`)
);

CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  KEY `idx_a` (`a`)
);
CREATE TABLE `t3` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  KEY `idx_a` (`a`)
);
mysql> explain select count(*) from (select a,ifnull(b,(select t2.c from t2,t3 where t2.a=t3.a and t1.a=t2.a)) t from t1) t;
+------------------------------------+-------------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------+
| id                                 | estRows     | task      | access object            | operator info                                                                                                   |
+------------------------------------+-------------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------+
| HashAgg_17                         | 1.00        | root      |                          | funcs:count(1)->Column#22                                                                                       |
| └─Apply_20                         | 10000.00    | root      |                          | CARTESIAN left outer join                                                                                       |
|   ├─IndexReader_24(Build)          | 10000.00    | root      |                          | index:IndexFullScan_23                                                                                          |
|   │ └─IndexFullScan_23             | 10000.00    | cop[tikv] | table:t1, index:idx_a(a) | keep order:false, stats:pseudo                                                                                  |
|   └─MaxOneRow_29(Probe)            | 10000.00    | root      |                          |                                                                                                                 |
|     └─IndexHashJoin_36             | 20000.00    | root      |                          | inner join, inner:IndexReader_33, outer key:test.t2.a, inner key:test.t3.a, equal cond:eq(test.t2.a, test.t3.a) |
|       ├─IndexReader_66(Build)      | 16000.00    | root      |                          | index:Selection_65                                                                                              |
|       │ └─Selection_65             | 16000.00    | cop[tikv] |                          | eq(test.t1.a, test.t2.a)                                                                                        |
|       │   └─IndexFullScan_64       | 16000000.00 | cop[tikv] | table:t2, index:idx_a(a) | keep order:false, stats:pseudo                                                                                  |
|       └─IndexReader_33(Probe)      | 20000.00    | root      |                          | index:Selection_32                                                                                              |
|         └─Selection_32             | 20000.00    | cop[tikv] |                          | eq(test.t1.a, test.t3.a), not(isnull(test.t3.a))                                                                |
|           └─IndexRangeScan_31      | 20020020.02 | cop[tikv] | table:t3, index:idx_a(a) | range: decided by [eq(test.t3.a, test.t2.a)], keep order:false, stats:pseudo                                    |
+------------------------------------+-------------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)

The more better plan is to eliminate the scalar subquery:

+----------------------------+----------+-----------+--------------------------+----------------------------------+
| id                         | estRows  | task      | access object            | operator info                    |
+----------------------------+----------+-----------+--------------------------+----------------------------------+
| HashAgg_15                 | 1.00     | root      |                          | funcs:count(Column#12)->Column#6 |
| └─IndexReader_16           | 1.00     | root      |                          | index:HashAgg_6                  |
|   └─HashAgg_6              | 1.00     | cop[tikv] |                          | funcs:count(1)->Column#12        |
|     └─IndexFullScan_12     | 10000.00 | cop[tikv] | table:t1, index:idx_a(a) | keep order:false, stats:pseudo   |
+----------------------------+----------+-----------+--------------------------+----------------------------------+
4 rows in set (0.00 sec)

[fixdb]

/assign @King-Dylan