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

--source include/have_innodb.inc

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');

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;

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 100;

MariaDB [test]> 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;

+----+

| id |

+----+

|  1 |

+----+

1 row in set (0,001 sec)

MariaDB [test]> 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 100;

+----+

| id |

+----+

|  1 |

+----+

1 row in set (0,000 sec)

MariaDB [test]> explain format=json 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 100;

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| {

  "query_block": {

    "select_id": 1,

    "const_condition": "1",

    "duplicate_removal": {

      "temporary_table": {

        "table": {

          "table_name": "p",

          "access_type": "const",

          "possible_keys": ["PRIMARY"],

          "key": "PRIMARY",

          "key_length": "4",

          "used_key_parts": ["id"],

          "ref": ["const"],

          "rows": 1,

          "filtered": 100,

          "using_index": true

        },

        "table": {

          "table_name": "c",

          "access_type": "ref",

          "possible_keys": ["parent_id"],

          "key": "parent_id",

          "key_length": "4",

          "used_key_parts": ["parent_id"],

          "ref": ["const"],

          "rows": 1,

          "filtered": 100,

          "using_index": true

        }

      }

    }

  }

} |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0,001 sec)