Details
Description
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)
|
Attachments
Issue Links
- duplicates
-
MDEV-28285 Unexpected result when combining DISTINCT, subselect and LIMIT
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
bisect shows that this issue was fixed on the 10.5 branch by https://github.com/MariaDB/server/commit/de703a2b215e156ce018da5b3b5423aafd163999
I could not tell which change in that batch fixed the issue, and I also could not reproduce the issue on 10.4 after trying many levels.
The problem was fixed in commit 92d2ceac73aa175a01f520fd4b7a31ed338c1ef5 (task MDEV-28285)
I tried this on today's latest 10.5, and it works for me. The first query with OFFSET 0 returns the row as expected. The same query with OFFSET 1 or OFFSET 100 do not return rows (since there is only one row).
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.011 sec)MariaDB [test]> 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 1;Empty set (0.014 sec)MariaDB [test]> 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;Empty set (0.011 sec)