planner: constant propagation supports sub-queries in update statements

[windtalker]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table t1(id int, value int);
Query OK, 0 rows affected (0.23 sec)

mysql> create table t2(id int, value int);
Query OK, 0 rows affected (0.21 sec)

mysql> explain update t1 set value = (select count(*) from t2 where t1.id = t2.id) where t1.id = 10;
+--------------------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------+
| id                                   | estRows  | task      | access object | operator info                                                                                |
+--------------------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------+
| Update_10                            | N/A      | root      |               | N/A                                                                                          |
| └─Projection_12                      | 10.00    | root      |               | test.t1.id, test.t1.value, test.t1._tidb_rowid, Column#7                                     |
|   └─Projection_13                    | 10.00    | root      |               | test.t1.id, test.t1.value, test.t1._tidb_rowid, ifnull(Column#7, 0)->Column#7                |
|     └─HashJoin_15                    | 10.00    | root      |               | left outer join, equal:[eq(test.t1.id, test.t2.id)]                                          |
|       ├─TableReader_19(Build)        | 10.00    | root      |               | data:Selection_18                                                                            |
|       │ └─Selection_18               | 10.00    | cop[tikv] |               | eq(test.t1.id, 10)                                                                           |
|       │   └─TableFullScan_17         | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                               |
|       └─HashAgg_25(Probe)            | 7992.00  | root      |               | group by:test.t2.id, funcs:count(Column#8)->Column#7, funcs:firstrow(test.t2.id)->test.t2.id |
|         └─TableReader_26             | 7992.00  | root      |               | data:HashAgg_20                                                                              |
|           └─HashAgg_20               | 7992.00  | cop[tikv] |               | group by:test.t2.id, funcs:count(1)->Column#8                                                |
|             └─Selection_24           | 9990.00  | cop[tikv] |               | not(isnull(test.t2.id))                                                                      |
|               └─TableFullScan_23     | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                               |
+--------------------------------------+----------+-----------+---------------+----------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)
mysql> explain select * from t1 where value = (select count(*) from t2 where t1.id = t2.id) and t1.id = 10;
+------------------------------------+----------+-----------+---------------+-----------------------------------------------+
| id                                 | estRows  | task      | access object | operator info                                 |
+------------------------------------+----------+-----------+---------------+-----------------------------------------------+
| Projection_13                      | 64.00    | root      |               | test.t1.id, test.t1.value                     |
| └─Selection_14                     | 64.00    | root      |               | eq(test.t1.value, ifnull(Column#7, 0))        |
|   └─HashJoin_15                    | 80.00    | root      |               | CARTESIAN left outer join                     |
|     ├─HashAgg_22(Build)            | 8.00     | root      |               | group by:test.t2.id, funcs:count(1)->Column#7 |
|     │ └─TableReader_29             | 10.00    | root      |               | data:Selection_28                             |
|     │   └─Selection_28             | 10.00    | cop[tikv] |               | eq(10, test.t2.id)                            |
|     │     └─TableFullScan_27       | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                |
|     └─TableReader_19(Probe)        | 10.00    | root      |               | data:Selection_18                             |
|       └─Selection_18               | 10.00    | cop[tikv] |               | eq(test.t1.id, 10)                            |
|         └─TableFullScan_17         | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                |
+------------------------------------+----------+-----------+---------------+-----------------------------------------------+

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

The constant id = 10 can be propagated to t2 side for both update sql and select sql

3. What did you see instead (Required)

The constant id = 10 is only propagated to t2 side for select sql

4. What is your TiDB version? (Required)

15a8459