Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.5.2, 10.5.8, 10.5.13, 10.6.5, 10.5, 10.6, 10.7(EOL)
-
None
-
RHEL 8
Description
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
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
At the moment, it's planned to be fixed in the next release.
Plans can change, though.
Looking at 10.5...
The query with just LIMIT 0 uses a shortcut:
explain format=json SELECT DISTINCT p.id FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE p.id=1 LIMIT 0\G
|
*************************** 1. row ***************************
|
EXPLAIN: {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"message": "Zero limit"
|
}
|
}
|
}
|
But the query with LIMIT 0 OFFSET n doesn't :
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=1 LIMIT 0 offset 5\G
|
*************************** 1. row ***************************
|
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
|
}
|
}
|
}
|
}
|
}
|
The problem is caused by this piece in JOIN_TAB::remove_duplicates :
if (!field_count && !(join->select_options & OPTION_FOUND_ROWS) && !having) |
{ // only const items with no OPTION_FOUND_ROWS |
join->unit->lim.set_single_row(); // Only send first row |
DBUG_RETURN(false); |
}
|
Before 10.5, the code looked like so:
if (!field_count && !(join->select_options & OPTION_FOUND_ROWS) && !having)
|
{ // only const items with no OPTION_FOUND_ROWS
|
join->unit->select_limit_cnt= 1; // Only send first row
|
DBUG_RETURN(false);
|
}
|
That is, it didn't touch the offset value.
We tested this in the following versions but still this issue exists. Any idea why is it?
11.0.1-rc
10.11.3
10.7.3
10.6.7
10.5.20
10.5.15
I am terribly sorry, only the case with LIMIT 0 got fixed. I reopened the bug here - MDEV-31275
Thank you! Reproducible as described on 10.5-10.7:
MariaDB [test]> SELECT DISTINCT p.id FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE p.id=1 LIMIT 0;Empty set (0.000 sec)MariaDB [test]> SELECT DISTINCT p.id FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE p.id=1 LIMIT 0 offset 5;+----+| id |+----+| 1 |+----+1 row in set (0.000 sec)MariaDB [test]> select version();+----------------+| version() |+----------------+| 10.5.0-MariaDB |+----------------+1 row in set (0.000 sec)