"xxxx IS NULL" prevent the optimizer from choosing the best join order and IndexJoin

[shaoxiqian]

create table small_t1 (id int);
create table small_t2 (id int);
create table small_t3 (id int);
create table large_t (id int, k_id int, key(k_id));

insert into small_t1 values (1);
insert into small_t2 select * from small_t1;
insert into small_t3 select * from small_t1;
insert into large_t select * from (
  with recursive xx as (
    select 1 as id, 1 as k_id
    union all
    select id + 1 as id, k_id + 1 as k_id from xx where id < 1000
  ) select * from xx
) xx;

analyze table small_t1, small_t2, small_t3, large_t;

explain select 1 from small_t1 
  left join small_t2 on small_t1.id = small_t2.id 
  join large_t on large_t.k_id = small_t1.id
  left join small_t3 on small_t3.id = large_t.id
where small_t2.id is null;

+--------------------------------------+---------+-----------+----------------+---------------------------------------------------------------------------+
| id                                   | estRows | task      | access object  | operator info                                                             |
+--------------------------------------+---------+-----------+----------------+---------------------------------------------------------------------------+
| Projection_16                        | 0.80    | root      |                | 1->Column#10                                                              |
| └─HashJoin_17                        | 0.80    | root      |                | inner join, equal:[eq(loadshare.large_t.k_id, loadshare.small_t1.id)]     |
|   ├─Selection_30(Build)              | 0.80    | root      |                | isnull(loadshare.small_t2.id)                                             |
|   │ └─HashJoin_31                    | 1.00    | root      |                | left outer join, equal:[eq(loadshare.small_t1.id, loadshare.small_t2.id)] |
|   │   ├─TableReader_38(Build)        | 1.00    | root      |                | data:Selection_37                                                         |
|   │   │ └─Selection_37               | 1.00    | cop[tikv] |                | not(isnull(loadshare.small_t2.id))                                        |
|   │   │   └─TableFullScan_36         | 1.00    | cop[tikv] | table:small_t2 | keep order:false, stats:pseudo                                            |
|   │   └─TableReader_35(Probe)        | 1.00    | root      |                | data:Selection_34                                                         |
|   │     └─Selection_34               | 1.00    | cop[tikv] |                | not(isnull(loadshare.small_t1.id))                                        |
|   │       └─TableFullScan_33         | 1.00    | cop[tikv] | table:small_t1 | keep order:false, stats:pseudo                                            |
|   └─HashJoin_19(Probe)               | 1000.00 | root      |                | left outer join, equal:[eq(loadshare.large_t.id, loadshare.small_t3.id)]  |
|     ├─TableReader_29(Build)          | 1.00    | root      |                | data:Selection_28                                                         |
|     │ └─Selection_28                 | 1.00    | cop[tikv] |                | not(isnull(loadshare.small_t3.id))                                        |
|     │   └─TableFullScan_27           | 1.00    | cop[tikv] | table:small_t3 | keep order:false, stats:pseudo                                            |
|     └─TableReader_23(Probe)          | 1000.00 | root      |                | data:Selection_22                                                         |
|       └─Selection_22                 | 1000.00 | cop[tikv] |                | not(isnull(loadshare.large_t.k_id))                                       |
|         └─TableFullScan_21           | 1000.00 | cop[tikv] | table:large_t  | keep order:false                                                          |
+--------------------------------------+---------+-----------+----------------+---------------------------------------------------------------------------+

If there is no small_t2.id is null, the plan is:

mysql> explain select 1 from small_t1 
    ->   left join small_t2 on small_t1.id = small_t2.id 
    ->   join large_t on large_t.k_id = small_t1.id
    ->   left join small_t3 on small_t3.id = large_t.id;
+----------------------------------------+---------+-----------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                     | estRows | task      | access object                   | operator info                                                                                                                                                     |
+----------------------------------------+---------+-----------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_14                          | 1.00    | root      |                                 | 1->Column#10                                                                                                                                                      |
| └─HashJoin_16                          | 1.00    | root      |                                 | left outer join, equal:[eq(loadshare.large_t.id, loadshare.small_t3.id)]                                                                                          |
|   ├─IndexHashJoin_23(Build)            | 1.00    | root      |                                 | inner join, inner:IndexLookUp_20, outer key:loadshare.small_t1.id, inner key:loadshare.large_t.k_id, equal cond:eq(loadshare.small_t1.id, loadshare.large_t.k_id) |
|   │ ├─HashJoin_33(Build)               | 1.00    | root      |                                 | left outer join, equal:[eq(loadshare.small_t1.id, loadshare.small_t2.id)]                                                                                         |
|   │ │ ├─TableReader_40(Build)          | 1.00    | root      |                                 | data:Selection_39                                                                                                                                                 |
|   │ │ │ └─Selection_39                 | 1.00    | cop[tikv] |                                 | not(isnull(loadshare.small_t2.id))                                                                                                                                |
|   │ │ │   └─TableFullScan_38           | 1.00    | cop[tikv] | table:small_t2                  | keep order:false, stats:pseudo                                                                                                                                    |
|   │ │ └─TableReader_37(Probe)          | 1.00    | root      |                                 | data:Selection_36                                                                                                                                                 |
|   │ │   └─Selection_36                 | 1.00    | cop[tikv] |                                 | not(isnull(loadshare.small_t1.id))                                                                                                                                |
|   │ │     └─TableFullScan_35           | 1.00    | cop[tikv] | table:small_t1                  | keep order:false, stats:pseudo                                                                                                                                    |
|   │ └─IndexLookUp_20(Probe)            | 1.00    | root      |                                 |                                                                                                                                                                   |
|   │   ├─Selection_19(Build)            | 1.00    | cop[tikv] |                                 | not(isnull(loadshare.large_t.k_id))                                                                                                                               |
|   │   │ └─IndexRangeScan_17            | 1.00    | cop[tikv] | table:large_t, index:k_id(k_id) | range: decided by [eq(loadshare.large_t.k_id, loadshare.small_t1.id)], keep order:false                                                                           |
|   │   └─TableRowIDScan_18(Probe)       | 1.00    | cop[tikv] | table:large_t                   | keep order:false                                                                                                                                                  |
|   └─TableReader_49(Probe)              | 1.00    | root      |                                 | data:Selection_48                                                                                                                                                 |
|     └─Selection_48                     | 1.00    | cop[tikv] |                                 | not(isnull(loadshare.small_t3.id))                                                                                                                                |
|       └─TableFullScan_47               | 1.00    | cop[tikv] | table:small_t3                  | keep order:false, stats:pseudo                                                                                                                                    |
+----------------------------------------+---------+-----------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[qw4990]

Similar with #59972, the best join order should be t1, t2, large_t, t3. But since we can't push the small_t2.id is null to small_t2 since its the outer side of an outer join, then the Selection(small_t2.id is null) prevent the optimizer from joining large_t and small_t1 first before small_t3.
If we join large_t and small_t3 first, we only have to use HashJoin, since large_t doesn't have index on id column.

[qw4990]

An workaround, rewrite the SQL and put small_t1 and large_t together to let them in the same Join Group:

explain select 1 from small_t1 
  join large_t on large_t.k_id = small_t1.id
  left join small_t2 on small_t1.id = small_t2.id 
  left join small_t3 on small_t3.id = large_t.id
where small_t2.id is null;

[hawkingrei]

Actually, the user's case is more complex than this. It involves a scenario where select isnull(col1) is used on a left outer join, and col1 is from the outer side. If this is rewritten as an anti semi join, it can leverage the index join.

[qw4990]

cc @winoros