inner join for enum type got wrong result

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE IDT_MULTI15874STROBJSTROBJ (COL1 ENUM('^YSQT0]V@9TFN>^WB6G?NG@S8>VYOM;BSC@<BCQ6','T\\NKZQQ=C1@IH9W>64=ZISGS?O[JDFBI5\M]QXJYQNSKU>NGAWLXS26LMTZ2YNN`XKIUGKY0IHDWV>E[BJJCABOKH1M^CB5E@DLS7Q88PWZTEAY]1ZQMN5NX[I<KBBK','PXWTHJ?R]P=`Y','OFJHCEKCQG\T:MXI7P3[YO4N0DF=2XJWJ4Z9Z;H\Q8TMUTZV8YLQAHWJ4BDZHR3A','@[ETQPEKKDD;9INXAQISU0O65J86AWQ2SZ8=ZZW6TKT4\GCF_O13\ZQW_S>FIYA983K:E4N77@FINM5HVGQ\CU\CVNF5WLOOOEORAM=_JLMVFURMUASTVDBE','NL3V:J9LM4U5KUCV<RIJ_RKMZ4;CXD_0:K`HCO=P1YNYTHX8KYZRQ?P\L01HLNSUC_R7:I5<V[HV0BIDEBZAPT73R7`DP43XXPLQCEI8>R;P','M5=T5FLQEZMPZAXH]4G:TSYYYVQ7O@\4S6C3N8WPFKSP;SRD6VW@94BBH8XCT','P]I52Y46F?\@RMOOF6;FWDTO`7FIT]\R:]ELHD[CNLDSHC7FPBYOOJXLZSBV^5C\^AAF6J5BCKE4V9==@H=4C]GMZXPNM','ECIQWH>?MK=ARGI0WVJNIBZFCFVJHFIUYJ:2?2WWZBNBWT\PFNQPLLBFP9R_','E<<T9UUF2?XM8TWS_','W[5E_U1J?YSOQISL1KD','M@V^`^8I','5UTEJUZIQ\ZJOJU_D6@V2DSVOIK@LUT^E?RTL>_Y9OT@SOPYR72VIJVMBWIVPF@TTBZ@8ZPBZL=LXZF`WM4V2?K>A\T','PZ@PR6XN28JL`B','ZOHBSCRMZPOI`IVTSEZ\AIDAF7DS@1TT20AP9','QLDIOY[Y:JZR@OL__I^@FBO=O_?WOOR:2BE:QJC','BI^TGJ_N<H:7OW8XXITM@FBWDNJ=KA`X:9@BUY4UHKSHFP`EAWR9_QS^HR2AI39MGVXWVD]RUI46SHU=GXAX;RT765X:CU7M4XOD^S9JFZI=HTTS?C0CT','M@HGGFM43C7','@M`IHSJQ8HBTGOS`=VW]QBMLVWN`SP;E>EEXYKV1POHTOJQPGCPVR=TYZMGWAB\UQR07J8U::W4','N`ZN4P@9T[JW;FR6=FA4WP@APNPG[XQVIK4]F]2>EC>JEIOXC``;;?OHP'),COL2 DECIMAL(20,0),COL3 DATETIME,KEY U_M_COL4(COL1,COL2),KEY U_M_COL5(COL3,COL2));

insert into IDT_MULTI15874STROBJSTROBJ (COL1,COL2,COL3) values("PXWTHJ\?R\]P=`Y",8444472640585607350,"6337\-09\-29\ 12:33:07");   
insert into IDT_MULTI15874STROBJSTROBJ (COL1,COL2,COL3) values("M@HGGFM43C7",3016403187896145205,"5771\-02\-19\ 09:49:44"); 
select t1.col1, t2.col1 from IDT_MULTI15874STROBJSTROBJ as t1 inner join IDT_MULTI15874STROBJSTROBJ as t2 on t1.col1 = t2.col1;
insert into IDT_MULTI15874STROBJSTROBJ (COL1,COL2,COL3) values("\^YSQT0\]V@9TFN>\^WB6G\?NG@S8>VYOM;BSC@<BCQ6",-4868633895821157191,"3164\-04\-24\ 08:06:35");
select t1.col1, t2.col1 from IDT_MULTI15874STROBJSTROBJ as t1 inner join IDT_MULTI15874STROBJSTROBJ as t2 on t1.col1 = t2.col1;

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

MySQL root@172.16.5.103:hchwang> insert into IDT_MULTI15874STROBJSTROBJ (COL1,COL2,COL3) values("PXWTHJ\?R\]P=`Y",8444472640585607350,"6337\-09\-29\ 12:33:07");
Query OK, 1 row affected
Time: 0.050s
MySQL > insert into IDT_MULTI15874STROBJSTROBJ (COL1,COL2,COL3) values("M@HGGFM43C7",3016403187896145205,"5771\-02\-19\ 09:49:44");
Query OK, 1 row affected
Time: 0.050s
MySQL > select t1.col1, t2.col1 from IDT_MULTI15874STROBJSTROBJ as t1 inner join IDT_MULTI15874STROBJSTROBJ as t2 on t1.col1 = t2.col1;
+---------------+---------------+
| col1          | col1          |
+---------------+---------------+
| PXWTHJ?R]P=`Y | PXWTHJ?R]P=`Y |
| M@HGGFM43C7   | M@HGGFM43C7   |
+---------------+---------------+

2 rows in set
Time: 0.050s
MySQL > insert into IDT_MULTI15874STROBJSTROBJ (COL1,COL2,COL3) values("\^YSQT0\]V@9TFN>\^WB6G\?NG@S8>VYOM;BSC@<BCQ6",-4868633895821157191,"3164\-04\-24\ 08:06:35");
Query OK, 1 row affected
Time: 0.050s
MySQL root@172.16.5.103:hchwang> select t1.col1, t2.col1 from IDT_MULTI15874STROBJSTROBJ as t1 inner join IDT_MULTI15874STROBJSTROBJ as t2 on t1.col1 = t2.col1;
+------------------------------------------+------------------------------------------+
| col1                                     | col1                                     |
+------------------------------------------+------------------------------------------+
| ^YSQT0]V@9TFN>^WB6G?NG@S8>VYOM;BSC@<BCQ6 | ^YSQT0]V@9TFN>^WB6G?NG@S8>VYOM;BSC@<BCQ6 |
| PXWTHJ?R]P=`Y                            | PXWTHJ?R]P=`Y                            |
| M@HGGFM43C7                              | M@HGGFM43C7                              |
+------------------------------------------+------------------------------------------+

3. What did you see instead (Required)

MySQL > insert into IDT_MULTI15874STROBJSTROBJ (COL1,COL2,COL3) values("PXWTHJ\?R\]P=`Y",8444472640585607350,"6337\-09\-29\ 12:33:07");
Query OK, 1 row affected
Time: 0.067s
MySQL > insert into IDT_MULTI15874STROBJSTROBJ (COL1,COL2,COL3) values("M@HGGFM43C7",3016403187896145205,"5771\-02\-19\ 09:49:44");
Query OK, 1 row affected
Time: 0.062s
MySQL > select t1.col1, t2.col1 from IDT_MULTI15874STROBJSTROBJ as t1 inner join IDT_MULTI15874STROBJSTROBJ as t2 on t1.col1 = t2.col1;
+---------------+---------------+
| col1          | col1          |
+---------------+---------------+
| PXWTHJ?R]P=`Y | PXWTHJ?R]P=`Y |
+---------------+---------------+

1 row in set
Time: 0.089s
MySQL > insert into IDT_MULTI15874STROBJSTROBJ (COL1,COL2,COL3) values("\^YSQT0\]V@9TFN>\^WB6G\?NG@S8>VYOM;BSC@<BCQ6",-4868633895821157191,"3164\-04\-24\ 08:06:35");
Query OK, 1 row affected
Time: 0.046s
MySQL > select t1.col1, t2.col1 from IDT_MULTI15874STROBJSTROBJ as t1 inner join IDT_MULTI15874STROBJSTROBJ as t2 on t1.col1 = t2.col1;
+------------------------------------------+------------------------------------------+
| col1                                     | col1                                     |
+------------------------------------------+------------------------------------------+
| ^YSQT0]V@9TFN>^WB6G?NG@S8>VYOM;BSC@<BCQ6 | ^YSQT0]V@9TFN>^WB6G?NG@S8>VYOM;BSC@<BCQ6 |
+------------------------------------------+------------------------------------------+

MySQL > explain select t1.col1, t2.col1 from IDT_MULTI15874STROBJSTROBJ as t1 inner join IDT_MULTI15874STROBJSTROBJ as t2 on t1.col1 = t2.col1;
+-------------------------+----------+-----------+--------------------------------------+-----------------------------------------------------------------------------------------------------------------+
| id                      | estRows  | task      | access object                        | operator info                                                                                                   |
+-------------------------+----------+-----------+--------------------------------------+-----------------------------------------------------------------------------------------------------------------+
| MergeJoin_8             | 12487.50 | root      |                                      | inner join, left key:hchwang.idt_multi15874strobjstrobj.col1, right key:hchwang.idt_multi15874strobjstrobj.col1 |
| ├─IndexReader_36(Build) | 9990.00  | root      |                                      | index:IndexFullScan_35                                                                                          |
| │ └─IndexFullScan_35    | 9990.00  | cop[tikv] | table:t2, index:U_M_COL4(COL1, COL2) | keep order:true, stats:pseudo                                                                                   |
| └─IndexReader_34(Probe) | 9990.00  | root      |                                      | index:IndexFullScan_33                                                                                          |
|   └─IndexFullScan_33    | 9990.00  | cop[tikv] | table:t1, index:U_M_COL4(COL1, COL2) | keep order:true, stats:pseudo                                                                                   |
+-------------------------+----------+-----------+--------------------------------------+-----------------------------------------------------------------------------------------------------------------+

5 rows in set

4. What is your TiDB version? (Required)

tidb_version() | Release Version: v4.0.0-beta.2-2832-gc6c8265e0-dirty
Edition: Community
Git Commit Hash: c6c8265e098b65339a85be2e35678b1c13f0f53a
Git Branch: master
UTC Build Time: 2021-05-10 14:58:41
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[aytrack]

duplicated with #24473

[ichn-hu]

duplicated with #24473

can we close this issue since it is duplicated?

[aytrack]

duplicated with #24473

can we close this issue since it is duplicated?

Yes, we can close this issue, and I'll test this again after the issue was fixed