wrong plan in prepare statement

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
drop table if exists IDT_26201;
CREATE TABLE `IDT_26201` (
  `COL1` bit(1) DEFAULT NULL,
  KEY `UK_COL1` (`COL1`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into IDT_26201(col1) values(0x01);
prepare stmt from 'select col1 from IDT_26201 where col1 > ?;';
set @a=0x01;
execute stmt using @a;

select col1 from IDT_26201 where col1 > 0x01;

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

release-4.0 & release-5.1 & master

mysql> execute stmt using @a;
Empty set (0.00 sec)

mysql> select col1 from IDT_26201 where col1 > 0x01;
Empty set (0.00 sec)

3. What did you see instead (Required)

release-5.0

mysql> execute stmt using @a;
+------------+
| col1       |
+------------+
| 0x01       |
+------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select col1 from IDT_26201 where col1 > 0x01;
Empty set (0.01 sec)

4. What is your TiDB version? (Required)

Release Version: v5.0.3-15-gd9bb7954f
Edition: Community
Git Commit Hash: d9bb7954ffe9fdcca9933f7f6a51a8e1dbf6a866
Git Branch: release-5.0
UTC Build Time: 2021-08-02 02:01:28
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[ChenPeng2013]

mysql> select connection_id(); +-----------------+
| connection_id() |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)

mysql> execute stmt using @A; +------------+
| col1 |
+------------+
| 0x01 |
+------------+
1 row in set, 2 warnings (0.00 sec)

mysql> explain for connection 5;
+-------------------------+----------+---------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+--------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------+----------+---------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+--------+------+
| Selection_5 | 8000.00 | 1 | root | | time:126.6µs, loops:2 | gt(test.idt_26201.col1, 0) | N/A | N/A |
| └─TableReader_7 | 10000.00 | 1 | root | | time:116µs, loops:3, cop_task: {num: 1, max: 71.2µs, proc_keys: 0, rpc_num: 1, rpc_time: 51.1µs, copr_cache_hit_ratio: 0.00} | data:TableFullScan_6 | N/A | N/A |
| └─TableFullScan_6 | 10000.00 | 1 | cop[tikv] | table:IDT_26201 | tikv_task:{time:2.27µ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 |
+-------------------------+----------+---------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+--------+------+
3 rows in set (0.00 sec)

[eurekaka]

The root cause of the problem is that, we store BinaryLiteral type user variables as strings internally, and before using them, we do not convert them back. #23241 fixed this problem on master branch, while the 5.0 cherry-pick #23373 has not been merged.

[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

[guo-shaoge]

#23373 merged and verified OK.