The result of index join in associated subqueries is wrong

[lilinghai]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1.txt
download and source the 1.txt

select t1. `col_float_key_signed`,t1. `col_smallint_undef_signed`  from table_20_binary_undef as t1 where t1. `col_float_key_signed` not in (select `col_float_key_signed` from table_75_binary_undef as t2 where t1. `col_smallint_undef_signed` = t2. `col_tinyint_key_signed`) ;

The plan is

+------------------------------------+---------+-----------+----------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                 | estRows | task      | access object                                                  | operator info                                                                                                                                                                                                                                                                                                                                                                             |
+------------------------------------+---------+-----------+----------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_8                       | 16.00   | root      |                                                                | rs2.table_20_binary_undef.col_float_key_signed, rs2.table_20_binary_undef.col_smallint_undef_signed                                                                                                                                                                                                                                                                                       |
| └─IndexJoin_12                     | 16.00   | root      |                                                                | anti semi join, inner:IndexLookUp_11, outer key:rs2.table_20_binary_undef.col_smallint_undef_signed, inner key:rs2.table_75_binary_undef.col_tinyint_key_signed, equal cond:eq(rs2.table_20_binary_undef.col_float_key_signed, rs2.table_75_binary_undef.col_float_key_signed), eq(rs2.table_20_binary_undef.col_smallint_undef_signed, rs2.table_75_binary_undef.col_tinyint_key_signed) |
|   ├─TableReader_22(Build)          | 20.00   | root      |                                                                | data:TableFullScan_21                                                                                                                                                                                                                                                                                                                                                                     |
|   │ └─TableFullScan_21             | 20.00   | cop[tikv] | table:t1                                                       | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                            |
|   └─IndexLookUp_11(Probe)          | 1.25    | root      |                                                                |                                                                                                                                                                                                                                                                                                                                                                                           |
|     ├─IndexRangeScan_9(Build)      | 1.25    | cop[tikv] | table:t2, index:col_tinyint_key_signed(col_tinyint_key_signed) | range: decided by [eq(rs2.table_75_binary_undef.col_tinyint_key_signed, rs2.table_20_binary_undef.col_smallint_undef_signed)], keep order:false, stats:pseudo                                                                                                                                                                                                                             |
|     └─TableRowIDScan_10(Probe)     | 1.25    | cop[tikv] | table:t2                                                       | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                            |
+------------------------------------+---------+-----------+----------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

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

+----------------------+---------------------------+
| col_float_key_signed | col_smallint_undef_signed |
+----------------------+---------------------------+
|                   -1 |                      NULL |
|               12.991 |                       -37 |
|                    1 |                        39 |
+----------------------+---------------------------+

3. What did you see instead (Required)

+----------------------+---------------------------+
| col_float_key_signed | col_smallint_undef_signed |
+----------------------+---------------------------+
|                  -87 |                         1 |
|                   -1 |                      NULL |
|                    1 |                        -9 |
|               12.991 |                       -37 |
|                    1 |                        39 |
|                 NULL |                        -1 |
|               -9.183 |                         1 |
|                  -15 |                        -9 |
+----------------------+---------------------------+

4. What is your TiDB version? (Required)

Release Version: v5.0.3
Edition: Community
Git Commit Hash: ad06648
Git Branch: heads/refs/tags/v5.0.3
UTC Build Time: 2021-06-25 10:00:40
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[XuHuaiyu]

plan with wrong result, there is no otherCond in IndexJoin_12

tidb> tidb> desc analyze select /*+nth_plan(1)*/  t1. `col_float_key_signed`,t1. `col_smallint_undef_signed`  from table_20_binary_undef as t1 where t1. `col_float_keed` not in (select `col_float_key_signed` from table_75_binary_undef as t2 where t1. `col_smallint_undef_signed` = t2. `col_tinyint_key_signed`) ;
+------------------------------------+----------+---------+-----------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                                 | estRows  | actRows | task      | access object                                                  | execution info                                                                                                                                                           | operator info                                                                                                                                                                                                                                                                                                                                                                                   | memory    | disk |
+------------------------------------+----------+---------+-----------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| Projection_8                       | 8000.00  | 8       | root      |                                                                | time:862.8µs, loops:2, Concurrency:5                                                                                                                                     | test.table_20_binary_undef.col_float_key_signed, test.table_20_binary_undef.col_smallint_undef_signed                                                                                                                                                                                                                                                                                           | 5.98 KB   | N/A  |
| └─IndexJoin_12                     | 8000.00  | 8       | root      |                                                                | time:831.7µs, loops:2, inner:{total:653.2µs, concurrency:5, task:1, construct:32µs, fetch:595.7µs, build:24.4µs}, probe:6.99µs                                           | anti semi join, inner:IndexLookUp_11, outer key:test.table_20_binary_undef.col_smallint_undef_signed, inner key:test.table_75_binary_undef.col_tinyint_key_signed, equal cond:eq(test.table_20_binary_undef.col_float_key_signed, test.table_75_binary_undef.col_float_key_signed), eq(test.table_20_binary_undef.col_smallint_undef_signed, test.table_75_binary_undef.col_tinyint_key_signed) | 25.8 KB   | N/A  |
|   ├─TableReader_22(Build)          | 10000.00 | 20      | root      |                                                                | time:220.7µs, loops:3, cop_task: {num: 1, max: 151.3µs, proc_keys: 0, rpc_num: 1, rpc_time: 101.4µs, copr_cache_hit_ratio: 0.00}                                         | data:TableFullScan_21                                                                                                                                                                                                                                                                                                                                                                           | 370 Bytes | N/A  |
|   │ └─TableFullScan_21             | 10000.00 | 20      | cop[tikv] | table:t1                                                       | tikv_task:{time:6.07µs, loops:20}                                                                                                                                        | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                  | N/A       | N/A  |
|   └─IndexLookUp_11(Probe)          | 1.25     | 65      | root      |                                                                | time:549.6µs, loops:2, index_task: {total_time: 189.3µs, fetch_handle: 186.3µs, build: 489ns, wait: 2.54µs}, table_task: {total_time: 1.6ms, num: 1, concurrency: 5}     |                                                                                                                                                                                                                                                                                                                                                                                                 | 10.8 KB   | N/A  |
|     ├─IndexRangeScan_9(Build)      | 1.25     | 65      | cop[tikv] | table:t2, index:col_tinyint_key_signed(col_tinyint_key_signed) | time:181.2µs, loops:3, cop_task: {num: 1, max: 144.9µs, proc_keys: 0, rpc_num: 1, rpc_time: 114.6µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:39µs, loops:65}        | range: decided by [eq(test.table_75_binary_undef.col_tinyint_key_signed, test.table_20_binary_undef.col_smallint_undef_signed)], keep order:false, stats:pseudo                                                                                                                                                                                                                                 | N/A       | N/A  |
|     └─TableRowIDScan_10(Probe)     | 1.25     | 65      | cop[tikv] | table:t2                                                       | time:180.6µs, loops:2, cop_task: {num: 1, max: 139.1µs, proc_keys: 0, rpc_num: 1, rpc_time: 107.5µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:13µs, loops:65}        | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                  | N/A       | N/A  |
+------------------------------------+----------+---------+-----------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+

[XuHuaiyu]

plan with correct result, there is an otherCond in HashJoin_20:

tidb> desc analyze select /*+nth_plan(3)*/  t1. `col_float_key_signed`,t1. `col_smallint_undef_signed`  from table_20_binary_undef as t1 where t1. `col_float_key_signed` not in (select `col_float_key_signed` from table_75_binary_undef as t2 where t1. `col_smallint_undef_signed` = t2. `col_tinyint_key_signed`) ;
+-------------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id                            | estRows  | actRows | task      | access object | execution info                                                                                                                                                           | operator info                                                                                                                                                                                                                                        | memory    | disk    |
+-------------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| Projection_8                  | 8000.00  | 3       | root      |               | time:352.3µs, loops:2, Concurrency:5                                                                                                                                     | test.table_20_binary_undef.col_float_key_signed, test.table_20_binary_undef.col_smallint_undef_signed                                                                                                                                                | 5.98 KB   | N/A     |
| └─HashJoin_20                 | 8000.00  | 3       | root      |               | time:329.3µs, loops:2, build_hash_table:{total:223.1µs, fetch:207.5µs, build:15.6µs}, probe:{concurrency:5, total:1.07ms, max:276.1µs, probe:58.2µs, fetch:1.02ms}       | anti semi join, equal:[eq(test.table_20_binary_undef.col_smallint_undef_signed, test.table_75_binary_undef.col_tinyint_key_signed)], other cond:eq(test.table_20_binary_undef.col_float_key_signed, test.table_75_binary_undef.col_float_key_signed) | 12.5 KB   | 0 Bytes |
|   ├─TableReader_24(Build)     | 10000.00 | 75      | root      |               | time:118µs, loops:2, cop_task: {num: 1, max: 166.9µs, proc_keys: 0, rpc_num: 1, rpc_time: 116.3µs, copr_cache_hit_ratio: 0.00}                                           | data:TableFullScan_23                                                                                                                                                                                                                                | 915 Bytes | N/A     |
|   │ └─TableFullScan_23        | 10000.00 | 75      | cop[tikv] | table:t2      | tikv_task:{time:17.9µs, loops:75}                                                                                                                                        | keep order:false, stats:pseudo                                                                                                                                                                                                                       | N/A       | N/A     |
|   └─TableReader_22(Probe)     | 10000.00 | 20      | root      |               | time:121.6µs, loops:2, cop_task: {num: 1, max: 165µs, proc_keys: 0, rpc_num: 1, rpc_time: 105.9µs, copr_cache_hit_ratio: 0.00}                                           | data:TableFullScan_21                                                                                                                                                                                                                                | 370 Bytes | N/A     |
|     └─TableFullScan_21        | 10000.00 | 20      | cop[tikv] | table:t1      | tikv_task:{time:6.07µs, loops:20}                                                                                                                                        | keep order:false, stats:pseudo                                                                                                                                                                                                                       | N/A       | N/A     |
+-------------------------------+----------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
6 rows in set (0.00 sec)

[qw4990]

The reason is that IndexJoin cannot deal with NULL correctly, and here is a minimal reproducible case:

create table t1 (a float default null, b smallint(6) DEFAULT NULL);
insert into t1 values (1, 1);

create table t2 (a float default null, b tinyint(4) DEFAULT NULL, key b (b));
insert into t2 values (null, 1);

select /*+nth_plan(1)*/ t1.a, t1.b from t1 where t1.a not in (select t2.a from t2 where t1.b=t2.b);
select /*+nth_plan(3)*/ t1.a, t1.b from t1 where t1.a not in (select t2.a from t2 where t1.b=t2.b);

[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