Wrong hash key constructed for hash join

[seiya-annie]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

tidb-a: Nontransactional-Delete
tidb-b: normal-delete
execute same dml sql, and check with
"select * from tbl_3 order by col_11,col_12,col_13,col_14,col_15"

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

results are same in tidb-a and tidb-b clusters

3. What did you see instead (Required)

[2023/11/29 01:14:15.948 +00:00] [INFO] [listener.go:96] [summary] [PASSED=0] [FAILED=1] [SKIPPED=0]
Error: result digests mismatch: 68fa1daaf82245b3bd96b6bc7f598180908aa080 != 9b010d9c9ce8b2b9ee071fd9813dbad657b8de7a "select * from tbl_3 o
rder by col_11,col_12,col_13,col_14,col_15"
+----------+---------+----------+---------------------+---------------------+
|  COL 11  | COL 12  |  COL 13  |       COL 14        |       COL 15        |
+----------+---------+----------+---------------------+---------------------+
|  1407620 | Charlie | 12:08:52 | 1975-01-20 00:00:00 | #g                  |
|  1479390 | Alice   | 12:40:08 | 1984-06-09 00:00:00 | LeoVONgN~iJz&inj    |
|  2483729 | Charlie | 06:43:13 | 1970-09-09 00:00:00 | w6o6WFYyL5          |
|  2584327 | Charlie | 13:27:40 | 2034-07-17 00:00:00 |                     |
|  2621671 | Bob     | 12:48:52 | 2014-04-16 00:00:00 | vG                  |
|  3222667 | Alice   | 18:15:37 | 2023-11-19 00:00:00 |                     |
|  3694249 | Alice   | 19:27:53 | 2032-03-03 00:00:00 | 1=(Dbd6X~           |
|  4135401 | Charlie | 11:30:34 | 2017-06-06 00:00:00 | 2FZmy9lanL8         |
|  4692415 | Bob     | 16:55:35 | 1987-01-06 00:00:00 | 8q$7EWaQ54L^b$L     |
|  4696775 | Charlie | 21:07:43 | 1984-07-31 00:00:00 | SKOW9I^sM$4xNk      |
|  5332195 | Bob     | 16:53:00 | 2002-08-27 00:00:00 | 7~                  |
|  6484463 | David   | 01:31:54 | 1986-02-05 00:00:00 | V                   |
|  7109734 | Alice   | 13:32:10 | 2005-06-07 00:00:00 | axaj!6&v+^ndnvgMA   |
|  7439047 | Charlie | 08:40:15 | 2030-10-31 00:00:00 | )iWtLS=)W-K         |
|  7871477 | Charlie | 15:18:48 | 2027-05-29 00:00:00 | b=y^@zIex           |
|  8096311 | David   | 16:37:23 | 1981-11-06 00:00:00 | )@6Xs5              |
|  8714024 | Bob     | 10:52:16 | 1992-12-05 00:00:00 | x5uJJv0q            |
|  8745941 | David   | 00:02:17 | 1986-04-23 00:00:00 | K7v1                |
|  8838143 | David   | 15:41:27 | 1993-02-23 00:00:00 | g0q~Z0b*PpMPKJxYbIE |
|  8851748 | Alice   | 03:24:14 | 2018-06-20 00:00:00 | %                   |
|  8963236 | Alice   | 00:18:31 | 2022-04-17 00:00:00 | v4DsE               |
|  9024936 | Alice   | 02:14:39 | 2004-07-06 00:00:00 | u9=JcZH*nTH21       |
|  9048951 | Alice   | 21:19:47 | 2018-09-22 00:00:00 | sJ!vs               |
|  9082223 | Alice   | 17:25:16 | 2035-11-08 00:00:00 | i                   |
|  9665624 | Bob     | 03:42:54 | 2033-09-11 00:00:00 | 8&fu9G0j~7fTy3Ft    |
| 10427825 | Charlie | 07:27:35 | 1986-12-25 00:00:00 | tWJ                 |
| 11872083 | Alice   | 16:28:00 | 2007-11-08 00:00:00 | 0+EGQrQNs           |
| 12569440 | Charlie | 15:24:08 | 2004-05-12 00:00:00 | E!W0#*g45!          |
| 12794792 | Charlie | 20:10:08 | 2034-08-07 00:00:00 | hvpXVQyuP           |
| 13070243 | Alice   | 11:04:26 | 2027-10-26 00:00:00 | +6i+3avaw=ai        |
| 13943500 | Charlie | 12:07:38 | 2020-08-01 00:00:00 | yNistRS8V           |
| 13975230 | David   | 13:48:05 | 1971-09-08 00:00:00 | JCLW%Qky(d$uieux8^X |
| 14204170 | David   | 18:10:06 | 2010-12-13 00:00:00 | 0%u                 |
| 14509869 | Alice   | 09:19:53 | 2011-10-20 00:00:00 | S4Kk!i)1RPz@*aE     |
| 15124716 | Charlie | 02:27:48 | 2013-01-24 00:00:00 |                     |
| 15372932 | Charlie | 03:35:25 | 1985-11-25 00:00:00 | Ss~Wu2ePvfpmJ=f     |
| 15528343 | Charlie | 03:55:08 | 1978-07-25 00:00:00 | t=7eD6uwhzFRJCR     |
| 16294639 | Alice   | 05:08:43 | 2006-09-23 00:00:00 | 3ss                 |
+----------+---------+----------+---------------------+---------------------+

+----------+---------+----------+---------------------+---------------------+
|  COL 11  | COL 12  |  COL 13  |       COL 14        |       COL 15        |
+----------+---------+----------+---------------------+---------------------+
|  1407620 | Charlie | 12:08:52 | 1975-01-20 00:00:00 | #g                  |
|  2483729 | Charlie | 06:43:13 | 1970-09-09 00:00:00 | w6o6WFYyL5          |
|  2584327 | Charlie | 13:27:40 | 2034-07-17 00:00:00 |                     |
|  2621671 | Bob     | 12:48:52 | 2014-04-16 00:00:00 | vG                  |
|  3222667 | Alice   | 18:15:37 | 2023-11-19 00:00:00 |                     |
|  3694249 | Alice   | 19:27:53 | 2032-03-03 00:00:00 | 1=(Dbd6X~           |
|  4692415 | Bob     | 16:55:35 | 1987-01-06 00:00:00 | 8q$7EWaQ54L^b$L     |
|  5332195 | Bob     | 16:53:00 | 2002-08-27 00:00:00 | 7~                  |
|  6484463 | David   | 01:31:54 | 1986-02-05 00:00:00 | V                   |
|  7109734 | Alice   | 13:32:10 | 2005-06-07 00:00:00 | axaj!6&v+^ndnvgMA   |
|  7439047 | Charlie | 08:40:15 | 2030-10-31 00:00:00 | )iWtLS=)W-K         |
|  7871477 | Charlie | 15:18:48 | 2027-05-29 00:00:00 | b=y^@zIex           |
|  8096311 | David   | 16:37:23 | 1981-11-06 00:00:00 | )@6Xs5              |
|  8714024 | Bob     | 10:52:16 | 1992-12-05 00:00:00 | x5uJJv0q            |
|  8745941 | David   | 00:02:17 | 1986-04-23 00:00:00 | K7v1                |
|  8838143 | David   | 15:41:27 | 1993-02-23 00:00:00 | g0q~Z0b*PpMPKJxYbIE |
|  8851748 | Alice   | 03:24:14 | 2018-06-20 00:00:00 | %                   |
|  8963236 | Alice   | 00:18:31 | 2022-04-17 00:00:00 | v4DsE               |
|  9024936 | Alice   | 02:14:39 | 2004-07-06 00:00:00 | u9=JcZH*nTH21       |
|  9048951 | Alice   | 21:19:47 | 2018-09-22 00:00:00 | sJ!vs               |
|  9082223 | Alice   | 17:25:16 | 2035-11-08 00:00:00 | i                   |
|  9665624 | Bob     | 03:42:54 | 2033-09-11 00:00:00 | 8&fu9G0j~7fTy3Ft    |
| 10427825 | Charlie | 07:27:35 | 1986-12-25 00:00:00 | tWJ                 |
| 11872083 | Alice   | 16:28:00 | 2007-11-08 00:00:00 | 0+EGQrQNs           |
| 12569440 | Charlie | 15:24:08 | 2004-05-12 00:00:00 | E!W0#*g45!          |
| 12794792 | Charlie | 20:10:08 | 2034-08-07 00:00:00 | hvpXVQyuP           |
| 13070243 | Alice   | 11:04:26 | 2027-10-26 00:00:00 | +6i+3avaw=ai        |
| 13943500 | Charlie | 12:07:38 | 2020-08-01 00:00:00 | yNistRS8V           |
| 13975230 | David   | 13:48:05 | 1971-09-08 00:00:00 | JCLW%Qky(d$uieux8^X |
| 14204170 | David   | 18:10:06 | 2010-12-13 00:00:00 | 0%u                 |
| 14509869 | Alice   | 09:19:53 | 2011-10-20 00:00:00 | S4Kk!i)1RPz@*aE     |
| 15124716 | Charlie | 02:27:48 | 2013-01-24 00:00:00 |                     |
| 15372932 | Charlie | 03:35:25 | 1985-11-25 00:00:00 | Ss~Wu2ePvfpmJ=f     |
| 15528343 | Charlie | 03:55:08 | 1978-07-25 00:00:00 | t=7eD6uwhzFRJCR     |
| 16294639 | Alice   | 05:08:43 | 2006-09-23 00:00:00 | 3ss                 |
+----------+---------+----------+---------------------+---------------------+
### 4. What is your TiDB version? (Required)

["Git Commit Hash"=877198dde22010e428be26aa0d7bdc5aab75d629]

v6.5.6 and master

[winoros]

We will assign the enum index to 0 when we evaluate the enum for the chunk.

But when we build the hash key for hash join, we use the enum index to get the string value.

It's a conflict and causes the wrong hash key result. I'm asking others for help.

[zanmato1984]

@seiya-annie Could you give a more specific reproduction step? The current description is too little.

[winoros]

@seiya-annie Could you give a more specific reproduction step? The current description is too little.

create table tbl_3 ( col_11 mediumint  unsigned not null default 8346281 ,col_12 enum ( 'Alice','Bob','Charlie','David' ) ,col_13 time   not null default '07:10:30.00' ,col_14 timestamp ,col_15 varbinary ( 194 )   not null default '-ZpCzjqdl4hsyo' , key idx_5 ( col_14 ,col_11 ,col_12 ) ,primary key  ( col_11 ,col_15 ) /*T![clustered_index] clustered */ ) charset utf8mb4 collate utf8mb4_bin partition by range ( col_11 ) ( partition p0 values less than (530262), partition p1 values less than (9415740), partition p2 values less than (11007444), partition p3 values less than (maxvalue) );

insert into tbl_3 values ( 8838143,'David','23:41:27.00','1993-02-23','g0q~Z0b*PpMPKJxYbIE' );
insert into tbl_3 values ( 9082223,'Alice','02:25:16.00','2035-11-08','i' );
insert into tbl_3 values ( 2483729,'Charlie','14:43:13.00','1970-09-10','w6o6WFYyL5' );
insert into tbl_3 values ( 4135401,'Charlie','19:30:34.00','2017-06-07','2FZmy9lanL8' );
insert into tbl_3 values ( 1479390,'Alice','20:40:08.00','1984-06-10','LeoVONgN~iJz&inj' );
insert into tbl_3 values ( 10427825,'Charlie','15:27:35.00','1986-12-25','tWJ' );
insert into tbl_3 values ( 12794792,'Charlie','04:10:08.00','2034-08-08','hvpXVQyuP' );
insert into tbl_3 values ( 4696775,'Charlie','05:07:43.00','1984-07-31','SKOW9I^sM$4xNk' );
insert into tbl_3 values ( 8963236,'Alice','08:18:31.00','2022-04-17','v4DsE' );
insert into tbl_3 values ( 9048951,'Alice','05:19:47.00','2018-09-22','sJ!vs' );


SELECT `col_14` FROM `test`.`tbl_3` WHERE ((`tbl_3`.`col_15` < 'dV') AND `tbl_3`.`col_12` IN (SELECT `col_12` FROM `test`.`tbl_3` WHERE NOT (ISNULL(`tbl_3`.`col_12`)))) ORDER BY IF(ISNULL(`col_14`),0,1),`col_14`;

The select should return 3 rows