enum type subquery return error record

[vivid392845427]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

new_collations_enabled_on_first_bootstrap = true

CREATE TABLE `tbl_40` (
  `col_286` enum('Alice','Bob','Charlie','David') NOT NULL DEFAULT 'Alice',
  `col_287` enum('Alice','Bob','Charlie','David') NOT NULL DEFAULT 'David',
  `col_288` enum('Alice','Bob','Charlie','David') NOT NULL,
  `col_289` varbinary(328) DEFAULT NULL,
  `col_290` varbinary(93) DEFAULT NULL,
  `col_291` blob NOT NULL,
  `col_292` varbinary(99) DEFAULT 'OGscEXkep',
  `col_293` tinytext DEFAULT NULL,
  `col_294` binary(1) DEFAULT '\0',
  PRIMARY KEY (`col_288`,`col_287`)
);

CREATE TABLE `tbl_36` (
  `col_267` enum('Alice','Bob','Charlie','David') COLLATE utf8_general_ci NOT NULL,
  `col_268` blob NOT NULL,
  PRIMARY KEY (`col_268`(5)),
  UNIQUE KEY `idx_89` (`col_267`)
);

insert into tbl_36 values('Charlie','');    
insert into tbl_36 values('Alice'  ,'BGQ' );
insert into tbl_36 values('David'  ,'T'   );
insert into tbl_36 values('Bob'    ,'dwvn');

insert into tbl_40 values('Bob'    ,'Bob'    ,'Alice'  ,'dOb'         ,'LJdDGSrhQygwPfU'   ,'wiDFjUPawD'        ,'sohIsCwtziMMY'     ,''             ,'');
insert into tbl_40 values('Charlie','Charlie','Alice'  ,'rkdXBd'      ,'ANByPfzMWcUgdwotqX','WY'                ,'YvZEoUf'           ,'htMhzZp'      ,'');
insert into tbl_40 values('Alice'  ,'David'  ,'Bob'    ,'mH'          ,'hsdxXGRjM'         ,'EKOtzKqRLHud'      ,'OGscEXkep'         ,'BuXxttreasv'  ,'');
insert into tbl_40 values('Alice'  ,'Charlie','Charlie','yidEeezdtfdR','KoDfWT'            ,'xYrwaGWCRc'        ,'LVURmvnJD'         ,'tFUIrLopQjdm' ,'');
insert into tbl_40 values('Alice'  ,'David'  ,'Charlie','ZzgULlAlibKy','zIgIQ'             ,''                  ,'OGscEXkep'         ,'HXp'          ,'');
insert into tbl_40 values('Alice'  ,'Alice'  ,'David'  ,'q'           ,'bJoiBW'            ,''                  ,'ODWB'              ,'ezHQSlfIwYwmG','');
insert into tbl_40 values('David'  ,'Charlie','David'  ,'P'           ,'Rpilx'             ,'cTNBLbEitNHJoYnnXb','aknjlShSLCuaNHZjeb','PSQ'          ,'');

select tbl_40.col_287, tbl_40.col_288 from tbl_40 where col_288 in ( select col_268 from tbl_36 where (tbl_40.col_288, tbl_40.col_287) = ('Charlie', 'David') ); ---check
select tbl_40.col_287, tbl_40.col_288 from tbl_40 where col_288 in (select col_268 from tbl_36); ---check

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

mysql> select tbl_40.col_287, tbl_40.col_288 from tbl_40 where col_288 in ( select col_268 from tbl_36 where (tbl_40.col_288, tbl_40.col_287) = ('Charlie', 'David') );
Empty set (0.00 sec)

mysql> select tbl_40.col_287, tbl_40.col_288 from tbl_40 where col_288 in (select col_268 from tbl_36);
Empty set (0.00 sec)

3. What did you see instead (Required)

scene 1：
tbl_36 and tbl_40 use nonclustered index，select return record as follow，do not meet expect：
mysql> select tbl_40.col_287, tbl_40.col_288 from tbl_40 where col_288 in ( select col_268 from tbl_36 where (tbl_40.col_288, tbl_40.col_287) = ('Charlie', 'David') );
+---------+---------+
| col_287 | col_288 |
+---------+---------+
| David   | Charlie |
+---------+---------+
1 row in set (0.01 sec)

mysql> explain analyze select tbl_40.col_287, tbl_40.col_288 from tbl_40 where col_288 in ( select col_268 from tbl_36 where (tbl_40.col_288, tbl_40.col_287) = ('Charlie', 'David') );    
+-----------------------------+---------+---------+-----------+-----------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id                          | estRows | actRows | task      | access object                                 | execution info                                                                                                                                                                                                                                                        | operator info                                                                                                                                                           | memory    | disk    |
+-----------------------------+---------+---------+-----------+-----------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| HashJoin_21                 | 0.80    | 1       | root      |                                               | time:5.3ms, loops:2, build_hash_table:{total:1.46ms, fetch:1.45ms, build:9.32µs}, probe:{concurrency:5, total:25.7ms, max:5.17ms, probe:58.6µs, fetch:25.6ms}                                                                                                         | semi join, equal:[eq(Column#14, clustered_index_go_nc_1637824961.tbl_36.col_268)]                                                                                       | 16.2 KB   | 0 Bytes |
| ├─TableReader_25(Build)     | 4.00    | 4       | root      |                                               | time:1.36ms, loops:2, cop_task: {num: 1, max: 1.45ms, proc_keys: 4, rpc_num: 1, rpc_time: 1.35ms, copr_cache_hit_ratio: 0.00}                                                                                                                                         | data:TableFullScan_24                                                                                                                                                   | 244 Bytes | N/A     |
| │ └─TableFullScan_24        | 4.00    | 4       | cop[tikv] | table:tbl_36                                  | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 4, total_process_keys_size: 168, total_keys: 39, rocksdb: {delete_skipped_count: 0, key_skipped_count: 38, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}                           | keep order:false, stats:pseudo                                                                                                                                          | N/A       | N/A     |
| └─Projection_22(Probe)      | 1.00    | 1       | root      |                                               | time:5.08ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                 | clustered_index_go_nc_1637824961.tbl_40.col_287, clustered_index_go_nc_1637824961.tbl_40.col_288, to_binary(clustered_index_go_nc_1637824961.tbl_40.col_288)->Column#14 | 274 Bytes | N/A     |
|   └─Point_Get_23            | 1.00    | 1       | root      | table:tbl_40, index:PRIMARY(col_288, col_287) | time:5.04ms, loops:2, Get:{num_rpc:2, total_time:4.71ms}, scan_detail: {total_process_keys: 2, total_process_keys_size: 146, total_keys: 2, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} |                                                                                                                                                                         | N/A       | N/A     |
+-----------------------------+---------+---------+-----------+-----------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
5 rows in set (0.01 sec)

scene 2:
mysql> select tbl_40.col_287, tbl_40.col_288 from tbl_40 where col_288 in (select col_268 from tbl_36);
+---------+---------+
| col_287 | col_288 |
+---------+---------+
| Bob     | Alice   |
| Charlie | Alice   |
| David   | Bob     |
| Charlie | Charlie |
| David   | Charlie |
| Alice   | David   |
| Charlie | David   |
+---------+---------+
7 rows in set (0.01 sec)

mysql> explain analyze select tbl_40.col_287, tbl_40.col_288 from tbl_40 where col_288 in (select col_268 from tbl_36);
+-----------------------------+---------+---------+-----------+-----------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id                          | estRows | actRows | task      | access object                                 | execution info                                                                                                                                                                                                                              | operator info                                                                                                                                                           | memory    | disk    |
+-----------------------------+---------+---------+-----------+-----------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| HashJoin_20                 | 5.60    | 7       | root      |                                               | time:5.74ms, loops:2, build_hash_table:{total:5.31ms, fetch:5.3ms, build:12.6µs}, probe:{concurrency:5, total:27.5ms, max:5.54ms, probe:111.6µs, fetch:27.4ms}                                                                              | semi join, equal:[eq(Column#14, clustered_index_go_nc_1637824961.tbl_36.col_268)]                                                                                       | 16.2 KB   | 0 Bytes |
| ├─TableReader_27(Build)     | 4.00    | 4       | root      |                                               | time:5.17ms, loops:2, cop_task: {num: 1, max: 5.35ms, proc_keys: 4, rpc_num: 1, rpc_time: 5.27ms, copr_cache_hit_ratio: 0.00}                                                                                                               | data:TableFullScan_26                                                                                                                                                   | 248 Bytes | N/A     |
| │ └─TableFullScan_26        | 4.00    | 4       | cop[tikv] | table:tbl_36                                  | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 4, total_process_keys_size: 168, total_keys: 39, rocksdb: {delete_skipped_count: 0, key_skipped_count: 38, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | keep order:false, stats:pseudo                                                                                                                                          | N/A       | N/A     |
| └─Projection_21(Probe)      | 7.00    | 7       | root      |                                               | time:5.41ms, loops:2, Concurrency:OFF                                                                                                                                                                                                       | clustered_index_go_nc_1637824961.tbl_40.col_287, clustered_index_go_nc_1637824961.tbl_40.col_288, to_binary(clustered_index_go_nc_1637824961.tbl_40.col_288)->Column#14 | 1.23 KB   | N/A     |
|   └─IndexReader_25          | 7.00    | 7       | root      |                                               | time:5.35ms, loops:2, cop_task: {num: 1, max: 6.13ms, proc_keys: 7, rpc_num: 1, rpc_time: 6.04ms, copr_cache_hit_ratio: 0.00}                                                                                                               | index:IndexFullScan_24                                                                                                                                                  | 528 Bytes | N/A     |
|     └─IndexFullScan_24      | 7.00    | 7       | cop[tikv] | table:tbl_40, index:PRIMARY(col_288, col_287) | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 7, total_process_keys_size: 371, total_keys: 37, rocksdb: {delete_skipped_count: 0, key_skipped_count: 36, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | keep order:false, stats:pseudo                                                                                                                                          | N/A       | N/A     |
+-----------------------------+---------+---------+-----------+-----------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
6 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

Release Version: v5.4.0-alpha-223-ge65f548a4
Edition: Community
Git Commit Hash: e65f548a40acedd51caa67bed9ee44f51baf852e
Git Branch: master
UTC Build Time: 2021-11-24 11:56:59
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[sylzd]

/assign

[sylzd]

minor case:

CREATE TABLE `t2` (
  `c2` enum('Alice','Bob','Charlie','David') NOT NULL DEFAULT 'David'
  PRIMARY KEY (`c2`)
);
insert into t2 values ('Alice');

CREATE TABLE `t1` (
  `c2` blob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into t1 values ("");

select * from t2 where c2 in (select c2 from t1);

[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.