[MDEV-31275] OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN operation - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Duplicate
Affects Version/s: 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
Fix Version/s: 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5
Component/s: Optimizer
Labels:
None

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

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

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa created issue - 2023-05-15 10:10

Alice Sherepa made changes - 2023-05-15 10:11

Field	Original Value	New Value
Assignee		Sergei Petrunia [ psergey ]

Alice Sherepa made changes - 2023-05-15 10:12

Link

This issue relates to ~~MDEV-27382~~ [ ~~MDEV-27382~~ ]

Alice Sherepa made changes - 2023-05-15 10:13

Priority

Major [ 3 ]

Critical [ 2 ]

Alice Sherepa made changes - 2023-05-15 10:13

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2023-05-15 10:15

Description

~~MDEV-27382~~ is still reproducible, test:
{code:sql}
--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;
{code}
{noformat}
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)
{noformat}

~~MDEV-27382~~ is still reproducible, test:
{code:sql}
--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;
{code}
{noformat}
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)
{noformat}

Sergei Petrunia made changes - 2023-11-09 11:24

Assignee

Sergei Petrunia [ psergey ]

Jason [ JIRAUSER54259 ]

Jason (Inactive) added a comment - 2023-11-17 21:59

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)

Jason (Inactive) added a comment - 2023-11-17 21:59 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)

Julien Fritsch made changes - 2023-11-28 10:24

Fix Version/s

10.9 [ 26905 ]

Julien Fritsch made changes - 2023-11-28 10:28

Fix Version/s

10.10 [ 27530 ]

Jason (Inactive) added a comment - 2023-11-30 17:52

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.

Jason (Inactive) added a comment - 2023-11-30 17:52 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.

Sergei Petrunia made changes - 2023-12-10 13:10

Assignee

Jason [ JIRAUSER54259 ]

Lena Startseva [ JIRAUSER50478 ]

Lena Startseva added a comment - 2023-12-21 15:07

The problem was fixed in commit 92d2ceac73aa175a01f520fd4b7a31ed338c1ef5 (task ~~MDEV-28285~~)

Lena Startseva added a comment - 2023-12-21 15:07 The problem was fixed in commit 92d2ceac73aa175a01f520fd4b7a31ed338c1ef5 (task MDEV-28285 )

Sergei Petrunia added a comment - 2023-12-22 09:23

Thanks. This makes sense.

Sergei Petrunia added a comment - 2023-12-22 09:23 Thanks. This makes sense.

Sergei Petrunia made changes - 2023-12-22 09:23

Assignee

Lena Startseva [ JIRAUSER50478 ]

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2023-12-22 10:10

Link

This issue duplicates ~~MDEV-28285~~ [ ~~MDEV-28285~~ ]

Sergei Petrunia made changes - 2023-12-22 10:12

Fix Version/s		10.11.5 [ 29019 ]
Fix Version/s		10.10.6 [ 29017 ]
Fix Version/s		10.9.8 [ 29015 ]
Fix Version/s		10.6.15 [ 29013 ]
Fix Version/s		10.5.22 [ 29011 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.11 [ 27614 ]
Resolution		Duplicate [ 3 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

People

Assignee:: Sergei Petrunia

Reporter:: Alice Sherepa

Votes:: 10 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2023-05-15 10:10

Updated:: 2023-12-22 10:12

Resolved:: 2023-12-22 10:12

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration