regression: planner return TableDual if InStatement contain duplicate values

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
drop table if exists PK_MULTI_COL_5177;
CREATE TABLE `PK_MULTI_COL_5177` (
  `COL1` binary(10) NOT NULL,
  `COL2` varbinary(10) NOT NULL,
  `COL3` smallint(45) NOT NULL,
  PRIMARY KEY (`COL1`(5),`COL2`,`COL3`) /*T![clustered_index] NONCLUSTERED */,
  UNIQUE KEY `UIDXM` (`COL1`(5),`COL2`),
  UNIQUE KEY `UIDX` (`COL2`),
  KEY `IDX3` (`COL3`),
  KEY `IDXM` (`COL3`,`COL2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
insert into PK_MULTI_COL_5177(col1, col2, col3) values(0x00000000000000000000, 0x002B200DF5BA03E59F82, 1);
select col1, col2 from PK_MULTI_COL_5177 where col1 = 0x00000000000000000000 and col2 in (0x002B200DF5BA03E59F82, 0x002B200DF5BA03E59F82, 0x002B200DF5BA03E59F82);
select col1, col2 from PK_MULTI_COL_5177 where col1 = 0x00000000000000000000 and col2 = 0x002B200DF5BA03E59F82;

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

mysql> select col1, col2 from PK_MULTI_COL_5177 where col1 = 0x00000000000000000000 and col2 in (0x002B200DF5BA03E59F82, 0x002B200DF5BA03E59F82, 0x002B200DF5BA03E59F82);
+------------------------+------------------------+
| col1                   | col2                   |
+------------------------+------------------------+
| 0x00000000000000000000 | 0x002B200DF5BA03E59F82 |
+------------------------+------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql> select col1, col2 from PK_MULTI_COL_5177 where col1 = 0x00000000000000000000 and col2 = 0x002B200DF5BA03E59F82;
+------------------------+------------------------+
| col1                   | col2                   |
+------------------------+------------------------+
| 0x00000000000000000000 | 0x002B200DF5BA03E59F82 |
+------------------------+------------------------+
1 row in set, 1 warning (0.00 sec)

3. What did you see instead (Required)

mysql> select col1, col2 from PK_MULTI_COL_5177 where col1 = 0x00000000000000000000 and col2 in (0x002B200DF5BA03E59F82, 0x002B200DF5BA03E59F82, 0x002B200DF5BA03E59F82);
Empty set (0.00 sec)

mysql> select col1, col2 from PK_MULTI_COL_5177 where col1 = 0x00000000000000000000 and col2 = 0x002B200DF5BA03E59F82;
+------------------------+------------------------+
| col1                   | col2                   |
+------------------------+------------------------+
| 0x00000000000000000000 | 0x002B200DF5BA03E59F82 |
+------------------------+------------------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

Edition: Community
Git Commit Hash: 974b5784adbbd47d14659916d47dd986effa7b4e
Git Branch: release-5.4
UTC Build Time: 2022-01-04 01:55:18
GoVersion: go1.16.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.