Limit operator would incorrectly discard result set

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t31cdd702` (
  `col_48` date NOT NULL DEFAULT '2003-04-01',
  `col_49` time NOT NULL,
  `col_50` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_chinese_ci;

INSERT INTO `t31cdd702` VALUES('1976-03-13','02:40:13',''),('2007-07-19','02:40:13',''),('2000-03-19','02:40:13',''),('1970-04-16','02:40:13',''),('2024-06-23','02:40:13',''),('2022-01-22','02:40:13',''),('2017-04-12','02:40:13',''),('1998-08-10','02:40:13',''),('2025-12-28','02:40:13',''),('2001-01-14','02:40:13',''),('1997-12-10','02:40:13',''),('2014-01-15','02:40:13',''),('1992-04-21','07:55:36','&v'),('2024-05-15','02:40:13',''),('1978-08-28','02:40:13',''),('2030-01-07','02:40:13',''),('1981-10-26','02:40:13',''),('1988-02-10','02:40:13',''),('2016-02-14','02:40:13',''),('1996-11-21','09:54:04','on6VRKYtaI'),('2022-08-03','02:40:13',''),('2034-10-05','02:40:13',''),('1988-11-12','02:18:22','m'),('2028-04-17','02:40:13',''),('1993-08-30','07:32:34','f@C*'),('2002-11-22','02:40:13',''),('2016-11-13','02:40:13',''),('2018-08-02','02:40:13',''),('1973-07-30','02:40:13',''),('2014-12-28','02:40:13',''),('1978-05-07','02:40:13',''),('1992-05-07','08:05:41','9'),('1979-05-23','04:08:58','brm'),('1996-08-01','23:10:58','IyG643!'),('2034-05-31','04:48:28',''),('1978-08-10','20:38:16','_$8reH*!MLE43'),('1986-08-13','23:20:51','eHr%WCBu');

CREATE TABLE `tl45f49bec` (
  `col_21` time NOT NULL DEFAULT '19:03:14',
  `col_22` text COLLATE gbk_bin NOT NULL,
  `col_23` float NOT NULL,
  PRIMARY KEY (`col_22`(2),`col_21`,`col_23`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `idx_4` (`col_22`(1),`col_21`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin;

INSERT INTO `tl45f49bec` VALUES('05:26:53','',6436.3984),('05:33:06','',2418.3447),('05:56:03','!34',5327.29),('11:11:13','$uX7jK=*(prX#fm',8447.91),('05:35:17','*Jqx7z%a9~1Xw',7480.8096),('22:48:06','-',4563.9565),('03:48:30','1*t@',282.95325),('19:34:18','1~4i@f8X&exNs+CG0x',1238.2216),('13:22:26','3c9iE',1337.3021),('11:30:51','4xyKNd7+tKbh',130.22589),('22:35:40','56vCiz^%#hcS',8593.311),('07:56:40','5W0p%FB$cMOcC_-37k',7798.0645),('23:17:09','6A!03oVaCmLM',6335.1514),('04:29:52','7N',8081.281),('04:14:22','7rW*b',2108.5618),('02:11:20','87O8gvnLG5',5109.0845),('11:43:02','=0vROyDng',9798.294),('06:21:18','@+0~@GdUE%+hSJg*#',7182.4136),('03:08:56','B1y^-u_v+l',2024.7775),('11:36:31','E#o%-MWl',3556.0056),('17:40:46','E1!qy4Qvw6s',8514.763),('13:40:54','IwMfmh$lfz',2577.1978),('00:55:17','J&eq%cQP+cx',1946.7703),('23:26:11','JJ0',9597.079),('19:16:32','K0VO3g(_nx%HMX',3434.9307),('14:35:00','LEJ9!B',1137.5157),('01:26:40','Sfuqtm',5829.2686),('11:58:06','XpqXa^b*%b!&I4ZnS',5890.494),('21:06:51','^',6630.6665),('03:22:56','^a',9613.8545),('04:30:59','_bmnB!IeDpljq',6335.3916),('08:29:45','b)=RH&R',5911.286),('18:56:18','h+5l9',1037.6467),('22:44:14','sZuxMLWUU',5482.626),('03:51:42','x-7',9611.379);

SELECT SPACE(0) AS `r0` FROM (`t31cdd702`) JOIN `tl45f49bec` WHERE `tl45f49bec`.`col_22` BETWEEN 'LEJ9!B' AND 'TV#~!yw' LIMIT 24622829;

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

74 rows in the result set.

3. What did you see instead (Required)

mysql> SELECT SPACE(0) AS `r0` FROM (`t31cdd702`) JOIN `tl45f49bec` WHERE `tl45f49bec`.`col_22` BETWEEN 'LEJ9!B' AND 'TV#~!yw' LIMIT 24622829;
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

ac92026

Note: This bug is introduced on 7-15 because it's correct in the 7-14 build.

[winoros]

caused by #54609