[MDEV-27382] OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN operation Created: 2021-12-29  Updated: 2023-05-15  Resolved: 2022-01-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.2, 10.5.8, 10.5.13, 10.6.5, 10.5, 10.6, 10.7
Fix Version/s: 10.5.14, 10.6.6, 10.7.2

Type: Bug Priority: Blocker
Reporter: Thilanka Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None
Environment:

RHEL 8

Issue Links:
Relates
relates to MDEV-31275 OFFSET is ignored when it is combined... Closed

 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

 Comments   
Comment by Alice Sherepa [ 2021-12-29 ]

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)

Comment by Thilanka [ 2022-01-09 ]

Can I get a rough idea, when will this get prioritized to fix?

Comment by Sergei Golubchik [ 2022-01-10 ]

At the moment, it's planned to be fixed in the next release.
Plans can change, though.

Comment by Thilanka [ 2022-01-10 ]

Thanks for the update and really appreciate it.

Comment by Sergei Petrunia [ 2022-01-12 ]

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
 
        }
 
      }
 
    }
 
  }
 
}

Comment by Sergei Petrunia [ 2022-01-12 ]

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

Comment by Sergei Petrunia [ 2022-01-12 ]

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.

Comment by Sergei Petrunia [ 2022-01-13 ]

bb-10.5-mdev27382

Comment by Sergei Petrunia [ 2022-01-13 ]

Sanja, please review.

Comment by Sergei Petrunia [ 2022-01-19 ]

Second variant: bb-10.5-mdev27382-v2

Comment by Oleksandr Byelkin [ 2022-01-19 ]

2fd6cfe120b9f09a31e06db27acae993b4c3e55c ok to push

Comment by Thilanka [ 2023-05-15 ]

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

Comment by Alice Sherepa [ 2023-05-15 ]

I am terribly sorry, only the case with LIMIT 0 got fixed. I reopened the bug here - MDEV-31275

Comment by Thilanka [ 2023-05-15 ]

Thanks Alice

Generated at Thu Feb 08 09:52:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.