Adding a high Limit Offset than expected rows to a Mysql Query causes it to hang

Question

I have a strange issue to a query, it basically causes the query to never return when a high limit is used. For instance:

This query below returns 320 000 rows in my database:

select * from shoppingTrip where startTripTime>'2022-06-23 00:00:00' and endTripTime<='2022-06-23 23:59:59' order by startTripTime ASC;

I want to use pagination with limits but for some reason it is not working in some cases. When I have limit 319800, 150 after order by it works but it doesn't work for next "page" when limit is limit 319950, 150 . it hangs for infinite What is the reason for that?

This is my table :

CREATE TABLE `shoppingtrip` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `RESCANNED` CHAR(1) NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
    `SWAPPED` CHAR(1) NOT NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
    `EXTOPTIMISTICLOCKVERSION` INT(11) NULL DEFAULT NULL,
    `auditEffectId` INT(11) NULL DEFAULT NULL,
    `receiptimageId` INT(11) NULL DEFAULT NULL,
    `rescanResult` TINYINT(1) NULL DEFAULT NULL,
    `endTripTime` DATETIME NULL DEFAULT NULL,
    `finalizedTime` DATETIME NULL DEFAULT NULL,
    `macAddress` VARCHAR(36) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `partialRescanCount` INT(11) NULL DEFAULT NULL,
    `posId` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `rescanQuantity` INT(11) NULL DEFAULT NULL,
    `rescanTotal` DECIMAL(9,3) NULL DEFAULT NULL,
    `sentToPosTime` DATETIME NULL DEFAULT NULL,
    `startTripTime` DATETIME NULL DEFAULT NULL,
    `totalAmount` DECIMAL(9,3) NULL DEFAULT NULL,
    `totalDiscount` DECIMAL(9,3) NULL DEFAULT NULL,
    `totalQuantity` INT(11) NULL DEFAULT NULL,
    `storeId` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `storeName` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `createdTime` DATETIME NULL DEFAULT NULL,
    `modifiedTime` DATETIME NULL DEFAULT NULL,
    `syncedTime` DATETIME NULL DEFAULT NULL,
    `customerId` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
    `customerIdentification` VARCHAR(255) NULL DEFAULT '' COLLATE 'utf8_general_ci',
    `maskedIdentification` VARCHAR(255) NULL DEFAULT '' COLLATE 'utf8_general_ci',
    `cancelled` CHAR(1) NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
    `partialRescanResult` TINYINT(1) NULL DEFAULT NULL,
    `verifiedFlag` CHAR(1) NULL DEFAULT 'N' COLLATE 'utf8_general_ci',
    PRIMARY KEY (`ID`) USING BTREE,
    UNIQUE INDEX `ix_shopping_trip` (`ID`) USING BTREE,
    INDEX `fk_shoppingTrip_receiptImage` (`receiptimageId`) USING BTREE,
    INDEX `ix_shoppingTrip_finaltime` (`finalizedTime`) USING BTREE,
    INDEX `ix_shoppingTrip_starttime` (`startTripTime`) USING BTREE,
    INDEX `ix_shoppingTrip_endtime` (`endTripTime`) USING BTREE,
    INDEX `ix_shoppingTrip_sentpostime` (`sentToPosTime`) USING BTREE,
    INDEX `ix_syncedTime_shoppingTrip` (`syncedTime`) USING BTREE,
    INDEX `ix_modifiedTime_shoppingTrip` (`modifiedTime`) USING BTREE,
    INDEX `ix_createdTime_shoppingTrip` (`createdTime`) USING BTREE,
    INDEX `fk_shoppingTrip_customer` (`customerId`) USING BTREE,
    INDEX `ix_shoppingTrip_customerIdentification` (`customerIdentification`) USING BTREE,
    INDEX `ix_posId_shoppingTrip` (`posId`) USING BTREE,
    CONSTRAINT `fk_shoppingTrip_customer` FOREIGN KEY (`customerId`) REFERENCES `selfscancustomer` (`customerId`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `fk_shoppingTrip_receiptImage` FOREIGN KEY (`receiptimageId`) REFERENCES `receiptimage` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=377086637
;

Associated EXPLAIN Result:

id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra
1;SIMPLE;shoppingtrip;range;ix_shoppingTrip_starttime,ix_shoppingTrip_endtime;ix_shoppingTrip_starttime;9;\\N;77040469;Using where

I tried to use pagination with sql query. I expect to fetch all rows. All rows are not fetched last page is missing. That query for last page hangs for ever.

I tried first with hibernate create api to do pagination by using setFirst and setMazResult but it is the same issue there as well. Then I tried manually with the query that hibernate created still same issue :(