MDEV-27382 is still reproducible, test:
--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)
|
MDEV-28285 Unexpected result when combining DISTINCT, subselect and LIMIT
