query result error when using prefix index

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
drop table if exists IDT_20755;
CREATE TABLE `IDT_20755` (
  `COL1` varchar(20) DEFAULT NULL,
  `COL2` tinyint(16) DEFAULT NULL,
  `COL3` timestamp NULL DEFAULT NULL,
  KEY `u_m_col` (`COL1`(10),`COL2`,`COL3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into IDT_20755 values("牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆", 73, "2010-06-03 07:29:05");

select * from IDT_20755 where col1 in ("牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆", "物碃貞枕騫摨聫嚣蜻禼担堋黕詖蝒毎槒阆畒郒", "剮毵樍穋摻瀽鬦擀钟鷫產冖悄乮曙枱诠鑡轰砠") and col2 in (72, 39, 73) and col3 != "2024-10-19 08:55:32";
select * from IDT_20755 where col1 in ("牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆", "物碃貞枕騫摨聫嚣蜻禼担堋黕詖蝒毎槒阆畒郒", "剮毵樍穋摻瀽鬦擀钟鷫產冖悄乮曙枱诠鑡轰砠") and col2 in (72, 39, 73);

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

mysql> select * from IDT_20755 where col1 in ("牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆", "物碃貞枕騫摨聫嚣蜻禼担堋黕詖蝒毎槒阆畒郒", "剮毵樍穋摻瀽鬦擀钟鷫產冖悄乮曙枱诠鑡轰砠") and col2 in (72, 39, 73) and col3 != "2024-10-19 08:55:32";
+--------------------------------------------------------------+------+---------------------+
| COL1                                                         | COL2 | COL3                |
+--------------------------------------------------------------+------+---------------------+
| 牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆                     |   73 | 2010-06-03 07:29:05 |
+--------------------------------------------------------------+------+---------------------+
1 row in set (0.09 sec)

mysql> select * from IDT_20755 where col1 in ("牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆", "物碃貞枕騫摨聫嚣蜻禼担堋黕詖蝒毎槒阆畒郒", "剮毵樍穋摻瀽鬦擀钟鷫產冖悄乮曙枱诠鑡轰砠") and col2 in (72, 39, 73);
+--------------------------------------------------------------+------+---------------------+
| COL1                                                         | COL2 | COL3                |
+--------------------------------------------------------------+------+---------------------+
| 牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆                     |   73 | 2010-06-03 07:29:05 |
+--------------------------------------------------------------+------+---------------------+
1 row in set (0.05 sec)

3. What did you see instead (Required)

mysql> select * from IDT_20755 where col1 in ("牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆", "物碃貞枕騫摨聫嚣蜻禼担堋黕詖蝒毎槒阆畒郒", "剮毵樍穋摻瀽鬦擀钟鷫產冖悄乮曙枱诠鑡轰砠") and col2 in (72, 39, 73) and col3 != "2024-10-19 08:55:32";
+--------------------------------------------------------------+------+---------------------+
| COL1                                                         | COL2 | COL3                |
+--------------------------------------------------------------+------+---------------------+
| 牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆                     |   73 | 2010-06-03 07:29:05 |
| 牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆                     |   73 | 2010-06-03 07:29:05 |
+--------------------------------------------------------------+------+---------------------+
2 rows in set (0.00 sec)
mysql> select * from IDT_20755 where col1 in ("牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆", "物碃貞枕騫摨聫嚣蜻禼担堋黕詖蝒毎槒阆畒郒", "剮毵樍穋摻瀽鬦擀钟鷫產冖悄乮曙枱诠鑡轰砠") and col2 in (72, 39, 73);
+--------------------------------------------------------------+------+---------------------+
| COL1                                                         | COL2 | COL3                |
+--------------------------------------------------------------+------+---------------------+
| 牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆                     |   73 | 2010-06-03 07:29:05 |
+--------------------------------------------------------------+------+---------------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v5.0.3-1-g7aefdd3bb
Edition: Community
Git Commit Hash: 7aefdd3bbd59f9082b56754f48ab4de567af17bd
Git Branch: release-5.0
UTC Build Time: 2021-07-08 02:50:59
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[xuyifangreeneyes]

explain select * from IDT_20755 where col1 in ("牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆", "物碃貞枕騫摨聫嚣蜻禼担堋黕詖蝒毎槒阆畒郒") and col2 in (72, 73) and col3 != "2024-10-19 08:55:32";
+-------------------------------+---------+-----------+--------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                            | estRows | task      | access object                                    | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------------------------------+---------+-----------+--------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexLookUp_11                | 10.00   | root      |                                                  |                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| ├─IndexRangeScan_8(Build)     | 10.00   | cop[tikv] | table:IDT_20755, index:u_m_col(COL1, COL2, COL3) | range:["物碃貞枕騫摨聫嚣蜻禼" 72 -inf,"物碃貞枕騫摨聫嚣蜻禼" 73 2024-10-19 08:55:32), ("物碃貞枕騫摨聫嚣蜻禼" 72 2024-10-19 08:55:32,"物碃貞枕騫摨聫嚣蜻禼" 73 +inf], ["牾窓螎刳闌蜹瑦詬鍖湪" 72 -inf,"牾窓螎刳闌蜹瑦詬鍖湪" 73 2024-10-19 08:55:32), ("牾窓螎刳闌蜹瑦詬鍖湪" 72 2024-10-19 08:55:32,"牾窓螎刳闌蜹瑦詬鍖湪" 73 +inf], keep order:false, stats:pseudo                                                                                 |
| └─Selection_10(Probe)         | 10.00   | cop[tikv] |                                                  | in(test.idt_20755.col1, "牾窓螎刳闌蜹瑦詬鍖湪槢壿玟瞏膍敗特森撇縆", "物碃貞枕騫摨聫嚣蜻禼担堋黕詖蝒毎槒阆畒郒")                                                                                                                                                                                                                                                                                                                                      |
|   └─TableRowIDScan_9          | 10.00   | cop[tikv] | table:IDT_20755                                  | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------------------------------+---------+-----------+--------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The two query ranges ["物碃貞枕騫摨聫嚣蜻禼" 72 -inf,"物碃貞枕騫摨聫嚣蜻禼" 73 2024-10-19 08:55:32) and ("物碃貞枕騫摨聫嚣蜻禼" 72 2024-10-19 08:55:32,"物碃貞枕騫摨聫嚣蜻禼" 73 +inf] overlap with each other.

[xuyifangreeneyes]

/assign

[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

[xuyifangreeneyes]

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions
   release-5.0, release-5.1

6. Fixed versions