is not null for enum type with other condition got wrong result #24576
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE UK_MC6287STROBJSTROBJ (COL1 ENUM('B74U9_^H4DMWOUDII]Y3EJM3YWILH7\KU9MTJK\D1?:1F]JZQURKPONFT[0@J@BJI9PR4P6LNLOGW<G6A]CGC<H_','XWPD','TD\I`P=]YXCJ9K[GX;FG0','H=QSUTJNTE=HQOY3HCYMNRQHOZBN:CIALW6[6<UX','=[RCDNAOM:OWSYL?TRM0T2[RS8PQBO28BMJ:QRA9IOWRROMMJ0','R94NP9<5HKE=OP_LR9[BW>YI396ZM]DTBAWIHIDSSLBR`HMG]A@>F[XOWX;LKVIMFGYUBOEXO:@I:Q?K:K[WNY;EA@5W@ZNIYBTYQ<RNJRL','K0@RWRWMY63O1GXDE5PG]6NCMG3JIH[PO@M<R^E30ZHYQUW@IHYZS4[QJ]T`]KSE@HPNKRMH`5L_VI:OP1\ZIWDHWR;KQ=V1_CZI?^OFA','[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJI\E7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7','\[R=8PGBT6UM7@@7FNX>H',';G6YNYOWZ0<Y==ZB_C<H1`IEAD','V4HOQPF[HIA>X^7O?QIFEFM0JL7X6AE_L>L7HHVS6UJ:CDOFAAJ3W=AOAO80FDGCNVICU','JJUMIW0E3M7HH9Q6VZGKNJGI2[I0M?T@A?YMZGLVB4[QOEHFK8SB\RKL`UQBD[:N=KVQ1XNNA1SCSNWUZYJIPE3T3?3ET[KJN^QM3BAA?HI4Z3X0E\:Y_AISH',';>Y8P]BPLXTZ:MYU<=>HKKVE3HFGODEEITE8\W>@AHM9:KQHW6NMUQRZL<3XBNRXTIUNFMZTT25MYBO:]EBB?=]QQK8ZL','J=M1;30>XE6QEA=LYGC?B3USTLCE3PEDC=4B:L\ME6DYJ@0QQ6BKKZBXYXXZ1OOEWR1T4CU;DA3A9OVQ;14MQNL\X','@49NTKNZJM9PZU3K`H[8GEC2Y\YQL2WLG?<APE3OZAQNWY9DLLI^BQC4=FE?IUS_^U>IODMB;A6PGKIZ\6W:GCMF4MR@6JLZMZY6JFTF?[DM?Z[RW=RJGGZH;E','ID]ZQJ4FPQXT7QHT^BCAVJHEYSF>G3H`FBA]?GATO\DC[8T[V8;G]G84\NC6TI?WAEL7X6L?YK7UJYP1JSY^`E^KED_KA>ZH=V2Q6FU17>W`R<RV;MMIET','I?MQVOLYCMVJUSH<U8I]T06H`QU^SP2MGHQTTUA[YG[ZSK`IGXWW;MNISQ9LVTAZCO:1PGJZVDTX@IYU4DJ@9M4[M]XTKT[]22UJ@VWRJ?Y0QVS?','EU0S=<[LPSK`U^FF?C_>HBAQHSROOL@XBW6DOP2LEHB?PF;K:MX3RRCAOCHZ','<2B3C>`YZI?Y[NTWURLBGN=1HMSVRCG]H6F;MF?HMP0`MM2U98FQX@HPBQEKSN@MSWM17B3OT','VAZAJNAHPYABLXR]S?WOWX^J:\E_7H3QX[PGFWK@EM?NREI`5>46MC5;OQLVDJU<GVTNQ\HTTREH]NQ7ED^L>Q4MCSL0MLWB2AVK'),COL2 ENUM('B74U9_^H4DMWOUDII]Y3EJM3YWILH7\KU9MTJK\D1?:1F]JZQURKPONFT[0@J@BJI9PR4P6LNLOGW<G6A]CGC<H_','XWPD','TD\I`P=]YXCJ9K[GX;FG0','H=QSUTJNTE=HQOY3HCYMNRQHOZBN:CIALW6[6<UX','=[RCDNAOM:OWSYL?TRM0T2[RS8PQBO28BMJ:QRA9IOWRROMMJ0','R94NP9<5HKE=OP_LR9[BW>YI396ZM]DTBAWIHIDSSLBR`HMG]A@>F[XOWX;LKVIMFGYUBOEXO:@I:Q?K:K[WNY;EA@5W@ZNIYBTYQ<RNJRL','K0@RWRWMY63O1GXDE5PG]6NCMG3JIH[PO@M<R^E30ZHYQUW@IHYZS4[QJ]T`]KSE@HPNKRMH`5L_VI:OP1\ZIWDHWR;KQ=V1_CZI?^OFA','[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJI\E7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7','\[R=8PGBT6UM7@@7FNX>H',';G6YNYOWZ0<Y==ZB_C<H1`IEAD','V4HOQPF[HIA>X^7O?QIFEFM0JL7X6AE_L>L7HHVS6UJ:CDOFAAJ3W=AOAO80FDGCNVICU','JJUMIW0E3M7HH9Q6VZGKNJGI2[I0M?T@A?YMZGLVB4[QOEHFK8SB\RKL`UQBD[:N=KVQ1XNNA1SCSNWUZYJIPE3T3?3ET[KJN^QM3BAA?HI4Z3X0E\:Y_AISH',';>Y8P]BPLXTZ:MYU<=>HKKVE3HFGODEEITE8\W>@AHM9:KQHW6NMUQRZL<3XBNRXTIUNFMZTT25MYBO:]EBB?=]QQK8ZL','J=M1;30>XE6QEA=LYGC?B3USTLCE3PEDC=4B:L\ME6DYJ@0QQ6BKKZBXYXXZ1OOEWR1T4CU;DA3A9OVQ;14MQNL\X','@49NTKNZJM9PZU3K`H[8GEC2Y\YQL2WLG?<APE3OZAQNWY9DLLI^BQC4=FE?IUS_^U>IODMB;A6PGKIZ\6W:GCMF4MR@6JLZMZY6JFTF?[DM?Z[RW=RJGGZH;E','ID]ZQJ4FPQXT7QHT^BCAVJHEYSF>G3H`FBA]?GATO\DC[8T[V8;G]G84\NC6TI?WAEL7X6L?YK7UJYP1JSY^`E^KED_KA>ZH=V2Q6FU17>W`R<RV;MMIET','I?MQVOLYCMVJUSH<U8I]T06H`QU^SP2MGHQTTUA[YG[ZSK`IGXWW;MNISQ9LVTAZCO:1PGJZVDTX@IYU4DJ@9M4[M]XTKT[]22UJ@VWRJ?Y0QVS?','EU0S=<[LPSK`U^FF?C_>HBAQHSROOL@XBW6DOP2LEHB?PF;K:MX3RRCAOCHZ','<2B3C>`YZI?Y[NTWURLBGN=1HMSVRCG]H6F;MF?HMP0`MM2U98FQX@HPBQEKSN@MSWM17B3OT','VAZAJNAHPYABLXR]S?WOWX^J:\E_7H3QX[PGFWK@EM?NREI`5>46MC5;OQLVDJU<GVTNQ\HTTREH]NQ7ED^L>Q4MCSL0MLWB2AVK'),COL3 ENUM('B74U9_^H4DMWOUDII]Y3EJM3YWILH7\KU9MTJK\D1?:1F]JZQURKPONFT[0@J@BJI9PR4P6LNLOGW<G6A]CGC<H_','XWPD','TD\I`P=]YXCJ9K[GX;FG0','H=QSUTJNTE=HQOY3HCYMNRQHOZBN:CIALW6[6<UX','=[RCDNAOM:OWSYL?TRM0T2[RS8PQBO28BMJ:QRA9IOWRROMMJ0','R94NP9<5HKE=OP_LR9[BW>YI396ZM]DTBAWIHIDSSLBR`HMG]A@>F[XOWX;LKVIMFGYUBOEXO:@I:Q?K:K[WNY;EA@5W@ZNIYBTYQ<RNJRL','K0@RWRWMY63O1GXDE5PG]6NCMG3JIH[PO@M<R^E30ZHYQUW@IHYZS4[QJ]T`]KSE@HPNKRMH`5L_VI:OP1\ZIWDHWR;KQ=V1_CZI?^OFA','[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJI\E7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7','\[R=8PGBT6UM7@@7FNX>H',';G6YNYOWZ0<Y==ZB_C<H1`IEAD','V4HOQPF[HIA>X^7O?QIFEFM0JL7X6AE_L>L7HHVS6UJ:CDOFAAJ3W=AOAO80FDGCNVICU','JJUMIW0E3M7HH9Q6VZGKNJGI2[I0M?T@A?YMZGLVB4[QOEHFK8SB\RKL`UQBD[:N=KVQ1XNNA1SCSNWUZYJIPE3T3?3ET[KJN^QM3BAA?HI4Z3X0E\:Y_AISH',';>Y8P]BPLXTZ:MYU<=>HKKVE3HFGODEEITE8\W>@AHM9:KQHW6NMUQRZL<3XBNRXTIUNFMZTT25MYBO:]EBB?=]QQK8ZL','J=M1;30>XE6QEA=LYGC?B3USTLCE3PEDC=4B:L\ME6DYJ@0QQ6BKKZBXYXXZ1OOEWR1T4CU;DA3A9OVQ;14MQNL\X','@49NTKNZJM9PZU3K`H[8GEC2Y\YQL2WLG?<APE3OZAQNWY9DLLI^BQC4=FE?IUS_^U>IODMB;A6PGKIZ\6W:GCMF4MR@6JLZMZY6JFTF?[DM?Z[RW=RJGGZH;E','ID]ZQJ4FPQXT7QHT^BCAVJHEYSF>G3H`FBA]?GATO\DC[8T[V8;G]G84\NC6TI?WAEL7X6L?YK7UJYP1JSY^`E^KED_KA>ZH=V2Q6FU17>W`R<RV;MMIET','I?MQVOLYCMVJUSH<U8I]T06H`QU^SP2MGHQTTUA[YG[ZSK`IGXWW;MNISQ9LVTAZCO:1PGJZVDTX@IYU4DJ@9M4[M]XTKT[]22UJ@VWRJ?Y0QVS?','EU0S=<[LPSK`U^FF?C_>HBAQHSROOL@XBW6DOP2LEHB?PF;K:MX3RRCAOCHZ','<2B3C>`YZI?Y[NTWURLBGN=1HMSVRCG]H6F;MF?HMP0`MM2U98FQX@HPBQEKSN@MSWM17B3OT','VAZAJNAHPYABLXR]S?WOWX^J:\E_7H3QX[PGFWK@EM?NREI`5>46MC5;OQLVDJU<GVTNQ\HTTREH]NQ7ED^L>Q4MCSL0MLWB2AVK'),UNIQUE KEY U_M_COL(COL1,COL2));
insert into UK_MC6287STROBJSTROBJ (COL1,COL2,COL3) values(16,8,15);
insert into UK_MC6287STROBJSTROBJ (COL1,COL2,COL3) values("XWPD",8,18);
select * from UK_MC6287STROBJSTROBJ where col2 between "[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJIE7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7" and "[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJIE7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7" and col1 is not null;2. What did you expect to see? (Required)
MySQL > select * from UK_MC6287STROBJSTROBJ where col2 between "[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJIE7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7" and "[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJIE7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7" and col1 is not null;
->
+----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+
| COL1 | COL2 | COL3 |
+----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+
| XWPD | [_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJIE7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7 | EU0S=<[LPSK`U^FF?C_>HBAQHSROOL@XBW6DOP2LEHB?PF;K:MX3RRCAOCHZ |
| ID]ZQJ4FPQXT7QHT^BCAVJHEYSF>G3H`FBA]?GATODC[8T[V8;G]G84NC6TI?WAEL7X6L?YK7UJYP1JSY^`E^KED_KA>ZH=V2Q6FU17>W`R<RV;MMIET | [_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJIE7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7 | @49NTKNZJM9PZU3K`H[8GEC2YYQL2WLG?<APE3OZAQNWY9DLLI^BQC4=FE?IUS_^U>IODMB;A6PGKIZ6W:GCMF4MR@6JLZMZY6JFTF?[DM?Z[RW=RJGGZH;E |
+----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+
2 rows in set
Time: 0.058s3. What did you see instead (Required)
MySQL > select * from UK_MC6287STROBJSTROBJ where col2 between "[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJIE7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7" and "[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJIE7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7" and col1 is not null;
+------+------+------+
| COL1 | COL2 | COL3 |
+------+------+------+
0 rows in set
Time: 0.068s
MySQL root@172.16.4.131:hchwang> explain select * from UK_MC6287STROBJSTROBJ where col2 between "[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJIE7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7" and "[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]
-> B:XRPXJIE7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7" and col1 is not null;
+---------------------------+---------+-----------+--------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+---------+-----------+--------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexLookUp_11 | 9.99 | root | | |
| ├─Selection_10(Build) | 9.99 | cop[tikv] | | eq(hchwang.uk_mc6287strobjstrobj.col2, "[_V3YU5ND4O;VBDG3GPJCL9EIKDE^VB_MSK8AXWN]B:XRPXJIE7740_;<YZFQFHJVG9@ZZF;XC^BEBCFV9;]XKFL6MPWJEC9Z;GFWEJEP6UY7") |
| │ └─IndexFullScan_8 | 9990.00 | cop[tikv] | table:UK_MC6287STROBJSTROBJ, index:U_M_COL(COL1, COL2) | keep order:false, stats:pseudo |
| └─TableRowIDScan_9(Probe) | 9.99 | cop[tikv] | table:UK_MC6287STROBJSTROBJ | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+--------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set
4. What is your TiDB version? (Required)
MySQL > select tidb_version()\G
***************************[ 1. row ]***************************
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
1 row in set
Time: 0.046s