OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN operation

We have recently moved from MariaDB 10.2 to 10.5.13 and we observed that the OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN operation. The following steps explains how to reproduce the issue.

Create two tables as parent and child with a foreign key and have 1 record in each.

CREATE TABLE `parent` (

  `id` int(7) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) DEFAULT NULL,

  primary key (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `parent` (`id`, `name`) VALUES

(1, 'Reed');

CREATE TABLE `child` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `parent_id` int(7) NOT NULL,

  `name` varchar(100) DEFAULT NULL,

  primary key (`id`),

  FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `child` (`id`, `parent_id`,`name`) VALUES

(1, 1,'John');

Run a select distinct record query left joining parent and child tables with the OFFSET as 0 and it will return the id of the only record we have in the parent table.

SELECT DISTINCT p.id FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE p.id IN (1) LIMIT 10 OFFSET 0;

Run the same query with the OFFSET as 1 and it will again return the id of the only record we have in the parent table. And when you continue to change the OFFSET to any higher value, it continues to return the id or the first record which is unintended.

SELECT DISTINCT p.id FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE p.id IN (1) LIMIT 10 OFFSET 1;

I have done some changes to the query to check some other combinations and when we change it as follow the issue seems to diapear which is bit confusing.

Chanding the DISTINCT p.id to DISTINCT *

SELECT DISTINCT * FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE p.id IN (1) LIMIT 10 OFFSET 1;

Adding another id for the WHERE IN() clause

SELECT DISTINCT p.id FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE p.id IN (1, 2) LIMIT 10 OFFSET 1;

This has become a blocker for our product so we tested it in MariaDB 10.4.22 version and identified that the issue is not there in 10.4.22 version