Correlated subquery will block the join reorder and the correlated subquery should not be executed before joins

[winoros]

Enhancement

create table t(a int, b int);
create table t1(a int, b int);
create table t2(a int, b int);
create table t3(a int, b int);
explain select * from (select t.*, (select b from t1 where t1.a > t.a limit 1) as var from t, t4 where t.a=t4.a) t, t2, t3 where t.a=t2.a and t.a=t3.a;

mysql> explain select * from (select t.*, (select b from t1 where t1.a > t.a limit 1) as var from t, t4 where t.a=t4.a) t, t2, t3 where t.a=t2.a and t.a=t3.a;
+----------------------------------------+----------+-----------+---------------+---------------------------------------------------------------------------+
| id                                     | estRows  | task      | access object | operator info                                                             |
+----------------------------------------+----------+-----------+---------------+---------------------------------------------------------------------------+
| Projection_26                          | 19511.72 | root      |               | test.t.a, test.t.b, test.t1.b, test.t2.a, test.t2.b, test.t3.a, test.t3.b |
| └─HashJoin_27                          | 19511.72 | root      |               | inner join, equal:[eq(test.t.a, test.t3.a)]                               |
|   ├─TableReader_52(Build)              | 9990.00  | root      |               | data:Selection_51                                                         |
|   │ └─Selection_51                     | 9990.00  | cop[tikv] |               | not(isnull(test.t3.a))                                                    |
|   │   └─TableFullScan_50               | 10000.00 | cop[tikv] | table:t3      | keep order:false, stats:pseudo                                            |
|   └─HashJoin_30(Probe)                 | 15609.38 | root      |               | inner join, equal:[eq(test.t2.a, test.t.a)]                               |
|     ├─TableReader_33(Build)            | 9990.00  | root      |               | data:Selection_32                                                         |
|     │ └─Selection_32                   | 9990.00  | cop[tikv] |               | not(isnull(test.t2.a))                                                    |
|     │   └─TableFullScan_31             | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                            |
|     └─Apply_35(Probe)                  | 12487.50 | root      |               | CARTESIAN left outer join                                                 |
|       ├─HashJoin_36(Build)             | 12487.50 | root      |               | inner join, equal:[eq(test.t.a, test.t4.a)]                               |
|       │ ├─TableReader_43(Build)        | 9990.00  | root      |               | data:Selection_42                                                         |
|       │ │ └─Selection_42               | 9990.00  | cop[tikv] |               | not(isnull(test.t4.a))                                                    |
|       │ │   └─TableFullScan_41         | 10000.00 | cop[tikv] | table:t4      | keep order:false, stats:pseudo                                            |
|       │ └─TableReader_40(Probe)        | 9990.00  | root      |               | data:Selection_39                                                         |
|       │   └─Selection_39               | 9990.00  | cop[tikv] |               | not(isnull(test.t.a))                                                     |
|       │     └─TableFullScan_38         | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo                                            |
|       └─Limit_44(Probe)                | 12487.50 | root      |               | offset:0, count:1                                                         |
|         └─TableReader_49               | 12487.50 | root      |               | data:Limit_48                                                             |
|           └─Limit_48                   | 12487.50 | cop[tikv] |               | offset:0, count:1                                                         |
|             └─Selection_47             | 12487.50 | cop[tikv] |               | gt(test.t1.a, test.t.a)                                                   |
|               └─TableFullScan_46       | 15609.38 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                            |
+----------------------------------------+----------+-----------+---------------+---------------------------------------------------------------------------+
22 rows in set (0.00 sec)

The actual join group is (t, t2, t3, t4). But as you can see, the Apply is inserted and split the join group into two parts.

And this apply should be executed after the two join is executed instead of before the two join. Because this apply is left outer one, will not filter out any data. So it should be executed later.

[winoros]

The use case is that the derived table t might be a view.

[seiya-annie]

/found customer