Optimizer does not consider the cost of other condition in Index Join

[SunRunAway]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table t(a int, b int, index (a));
Query OK, 0 rows affected (0.01 sec)

SQL is

select * from t t1 inner join t t2 on t1.a = t2.a and t1.b = t2.b;

If Index Join is used, currently t1.a = t2.a is put in the equality condition, and t1.b = t2.b is put in other conditions. If the selectivity of t1.a = t2.a is high, there will be quite a lot of rows that need to be filtered with t1.b = t2.b. This part of the cost will be very large.

mysql> explain select /*+ INL_HASH_JOIN(t1) */ * from t t1 inner join t t2 on t1.a = t2.a and t1.b = t2.b;
+-------------------------------+----------+-----------+----------------------+-------------------------------------------------------------------------------------------------------------+
| id                            | estRows  | task      | access object        | operator info                                                                                               |
+-------------------------------+----------+-----------+----------------------+-------------------------------------------------------------------------------------------------------------+
| IndexHashJoin_30              | 12475.01 | root      |                      | inner join, inner:IndexLookUp_27, outer key:test.t.a, inner key:test.t.a, other cond:eq(test.t.b, test.t.b) |
| ├─TableReader_41(Build)       | 9980.01  | root      |                      | data:Selection_40                                                                                           |
| │ └─Selection_40              | 9980.01  | cop[tikv] |                      | not(isnull(test.t.a)), not(isnull(test.t.b))                                                                |
| │   └─TableFullScan_39        | 10000.00 | cop[tikv] | table:t2             | keep order:false, stats:pseudo                                                                              |
| └─IndexLookUp_27(Probe)       | 1.25     | root      |                      |                                                                                                             |
|   ├─Selection_25(Build)       | 1.25     | cop[tikv] |                      | not(isnull(test.t.a))                                                                                       |
|   │ └─IndexRangeScan_23       | 1.25     | cop[tikv] | table:t1, index:a(a) | range: decided by [eq(test.t.a, test.t.a)], keep order:false, stats:pseudo                                  |
|   └─Selection_26(Probe)       | 1.25     | cop[tikv] |                      | not(isnull(test.t.b))                                                                                       |
|     └─TableRowIDScan_24       | 1.25     | cop[tikv] | table:t1             | keep order:false, stats:pseudo                                                                              |
+-------------------------------+----------+-----------+----------------------+-------------------------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)

If Hash Join is used, t1.a = t2.a and t1.b = t2.b will be put in equal condition. There is no such problem.

mysql> explain select * from t t1 inner join t t2 on t1.a = t2.a and t1.b = t2.b;
+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+
| id                           | estRows  | task      | access object | operator info                                                     |
+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+
| HashJoin_40                  | 12475.01 | root      |               | inner join, equal:[eq(test.t.a, test.t.a) eq(test.t.b, test.t.b)] |
| ├─TableReader_61(Build)      | 9980.01  | root      |               | data:Selection_60                                                 |
| │ └─Selection_60             | 9980.01  | cop[tikv] |               | not(isnull(test.t.a)), not(isnull(test.t.b))                      |
| │   └─TableFullScan_59       | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                    |
| └─TableReader_54(Probe)      | 9980.01  | root      |               | data:Selection_53                                                 |
|   └─Selection_53             | 9980.01  | cop[tikv] |               | not(isnull(test.t.a)), not(isnull(test.t.b))                      |
|     └─TableFullScan_52       | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                    |
+------------------------------+----------+-----------+---------------+-------------------------------------------------------------------+
7 rows in set (0.01 sec)

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

[SunRunAway]

One solution which need not change the cost model is

1. make all equal conditions in other condition into the join key of Index Join & Index Hash Join
2. drop Index Merge Join & Merge Join if there is any equal condition in other condition

/cc @lzmhhh123 @eurekaka @XuHuaiyu @qw4990 @zz-jason

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions