Repeated execution of Prepare with null parameter

[wan1y]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t1(col1 char(1), col2 varchar(255), PRIMARY KEY (`col1`) USING BTREE);
insert into t1 values("a","a");

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

mysql> prepare stmt from 'select t1.col1, t2.col2 from t1 as t1 inner join t1 as t2 on t1.col1 = t2.col1 where t2.col1 BETWEEN ? and ?;';
Query OK, 0 rows affected (0.04 sec)
Statement prepared

mysql> set @a=NULL, @b=NULL;
Query OK, 0 rows affected (0.04 sec)

mysql> execute stmt using@a,@b;
Empty set

mysql> set @a=NULL, @b=NULL;
Query OK, 0 rows affected (0.04 sec)

mysql> execute stmt using@a,@b;
Empty set

3. What did you see instead (Required)

mysql> prepare stmt from 'select t1.col1, t2.col2 from t1 as t1 inner join t1 as t2 on t1.col1 = t2.col1 where t2.col1 BETWEEN ? and ?;';
Query OK, 0 rows affected (0.04 sec)

mysql> set @a=NULL, @b=NULL;
Query OK, 0 rows affected (0.04 sec)

mysql> execute stmt using@a,@b;
Empty set

mysql> set @a=NULL, @b=NULL;
Query OK, 0 rows affected (0.04 sec)

mysql> execute stmt using@a,@b;
+------+------+
| col1 | col2 |
+------+------+
| a    | a    |
+------+------+
1 row in set (0.10 sec)

4. What is your TiDB version? (Required)

Release Version: v4.0.0-beta.2-2209-gfebac515b
Edition: Community
Git Commit Hash: febac515b1369643216e1efc971a262615dc4794
Git Branch: master
UTC Build Time: 2021-03-01 03:47:27
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[TszKitLo40]

/assign

[tiancaiamao]

For the first execute stmt using@a,@b;

mysql> explain for connection 3;
+--------------------------------------+---------+---------+-----------+-------------------------------+----------------------------------------------------------------------------------+-----------------------------------------------------------+--------+------+
| id                                   | estRows | actRows | task      | access object                 | execution info                                                                   | operator info                                             | memory | disk |
+--------------------------------------+---------+---------+-----------+-------------------------------+----------------------------------------------------------------------------------+-----------------------------------------------------------+--------+------+
| MergeJoin_9                          | 0.00    | 0       | root      |                               | time:429.2µs, loops:1                                                            | inner join, left key:test.t1.col1, right key:test.t1.col1 | N/A    | N/A  |
| ├─Projection_42(Build)               | 0.00    | 0       | root      |                               | time:398.7µs, loops:1, Concurrency:OFF                                           | test.t1.col1, test.t1.col2                                | N/A    | N/A  |
| │ └─IndexLookUp_41                   | 0.00    | 0       | root      |                               | time:394.4µs, loops:1,  table_task: {total_time: 1.7ms, num: 0, concurrency: 5}  |                                                           | N/A    | N/A  |
| │   ├─IndexFullScan_39(Build)        | 0.00    | 0       | cop[tikv] | table:t2, index:PRIMARY(col1) | time:98.8µs, loops:1                                                             | keep order:true, stats:pseudo                             | N/A    | N/A  |
| │   └─TableRowIDScan_40(Probe)       | 0.00    | 0       | cop[tikv] | table:t2                      |                                                                                  | keep order:false, stats:pseudo                            | N/A    | N/A  |
| └─IndexReader_38(Probe)              | 0.00    | 0       | root      |                               | time:17.7µs, loops:1                                                             | index:IndexFullScan_37                                    | N/A    | N/A  |
|   └─IndexFullScan_37                 | 0.00    | 0       | cop[tikv] | table:t1, index:PRIMARY(col1) |                                                                                  | keep order:true, stats:pseudo                             | N/A    | N/A  |
+--------------------------------------+---------+---------+-----------+-------------------------------+----------------------------------------------------------------------------------+-----------------------------------------------------------+--------+------+
7 rows in set (0.00 sec)

For the following on execute stmt using@a,@b;:

mysql> explain for connection 3;
+--------------------------------------+---------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+--------+------+
| id                                   | estRows | actRows | task      | access object                 | execution info                                                                                                                                                                                                                                                                                                                                        | operator info                                             | memory | disk |
+--------------------------------------+---------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+--------+------+
| MergeJoin_9                          | 0.00    | 1       | root      |                               | time:1.23ms, loops:2                                                                                                                                                                                                                                                                                                                                  | inner join, left key:test.t1.col1, right key:test.t1.col1 | N/A    | N/A  |
| ├─Projection_42(Build)               | 0.00    | 1       | root      |                               | time:1.13ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                 | test.t1.col1, test.t1.col2                                | N/A    | N/A  |
| │ └─IndexLookUp_41                   | 0.00    | 1       | root      |                               | time:1.12ms, loops:2, index_task: {total_time: 360.1µs, fetch_handle: 338.9µs, build: 7.21µs, wait: 14µs}, table_task: {total_time: 2.98ms, num: 1, concurrency: 5}                                                                                                                                                                                   |                                                           | N/A    | N/A  |
| │   ├─IndexFullScan_39(Build)        | 0.00    | 1       | cop[tikv] | table:t2, index:PRIMARY(col1) | time:323.9µs, loops:3, cop_task: {num: 1, max: 245.1µs, proc_keys: 0, rpc_num: 1, rpc_time: 169.9µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:10.1µs, loops:1}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}     | keep order:true, stats:pseudo                             | N/A    | N/A  |
| │   └─TableRowIDScan_40(Probe)       | 0.00    | 1       | cop[tikv] | table:t2                      | time:404.4µs, loops:2, cop_task: {num: 1, max: 226.2µs, proc_keys: 0, rpc_num: 1, rpc_time: 164.5µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:4.88µs, loops:1}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}     | keep order:false, stats:pseudo                            | N/A    | N/A  |
| └─IndexReader_38(Probe)              | 0.00    | 1       | root      |                               | time:29.7µs, loops:2, cop_task: {num: 1, max: 278.9µs, proc_keys: 0, rpc_num: 1, rpc_time: 183.1µs, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                       | index:IndexFullScan_37                                    | N/A    | N/A  |
|   └─IndexFullScan_37                 | 0.00    | 1       | cop[tikv] | table:t1, index:PRIMARY(col1) | tikv_task:{time:8.21µs, loops:1}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}                                                                                                                                       | keep order:true, stats:pseudo                             | N/A    | N/A  |
+--------------------------------------+---------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+--------+------+
7 rows in set (0.00 sec)

[TszKitLo40]

I want to try this issue but have some questions, can I get help from you on Slack @eurekaka ?

[XuHuaiyu]

It seems the bug disappears if we disable PreparePlanCache:

	PreparedPlanCache: PreparedPlanCache{
		Enabled:          true, // <= set to false
		Capacity:         100,
		MemoryGuardRatio: 0.1,
	},

[TszKitLo40]

It seems the bug disappears if we disable PreparePlanCache:

	PreparedPlanCache: PreparedPlanCache{
		Enabled:          true, // <= set to false
		Capacity:         100,
		MemoryGuardRatio: 0.1,
	},

Yes, this bug should be caused by PlanCache. And I found the AccessConditions of this two executions is different so that after rebuildRanges, the ranges will also different.

[XuHuaiyu]

PTAL @eurekaka

[eurekaka]

#23238 can fix this problem.

[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