Bug #97476 Range optimizer skips rows
Submitted: 4 Nov 2019 17:06 Modified: 19 Jan 20:27
Reporter: Ilya Raudsepp Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.16, 8.0.17, 8.0.18 OS:Mac OS X (Catalina 10.15)
Assigned to: CPU Architecture:x86
Tags: regression

[4 Nov 2019 17:06] Ilya Raudsepp
Description:
We run query to select most recent items from the table Test, with definition

create table Test
(
    id int auto_increment primary key,
    platform_id int not null,
    item_id int not null,
    status enum('NewAuto', 'NewManually', 'Approved', 'Rejected', 'OnProd', 'AutoApproved') default 'NewAuto' not null,
    snap_id int default 0 not null,
    type enum('Default', 'NonDefault') default 'Default' not null,
    created_at timestamp default '0000-00-00 00:00:00' not null,
    updated_at timestamp default '0000-00-00 00:00:00' not null,
    constraint UNIQUE  uniq_idx (item_id, platform_id, type, created_at),
    index idx (item_id, platform_id, type, status, created_at)
)
collate=utf8mb4_general_ci;

INSERT INTO Test (platform_id, item_id, created_at, status, type, snap_id)
VALUES
    (1, 3, '2019-05-17 14:43:27', 'NewAuto', 'Default', 0),
    (1, 2, '2019-05-17 14:43:27', 'NewAuto', 'Default', 0),
    (2, 3, '2019-05-17 14:43:28', 'NewAuto', 'Default', 0),
    (2, 2, '2019-05-17 14:43:28', 'NewAuto', 'Default', 0),
    (2, 111, '2019-05-17 14:43:29', 'NewAuto', 'Default', 0),
    (2, 2, '2019-05-17 14:43:30', 'NewAuto', 'Default', 0),
    (1, 2, '2019-05-17 14:43:31', 'NewAuto', 'Default', 0),
    (1, 111, '2019-05-17 14:43:31', 'NewAuto', 'Default', 0);

And then run three different query, first one is original without any optimizer hints.
Original query contains more fields in outer select, to simplify I removed them (was SELECT t.id, t.platform_id, t.item_id, t.created_at, t.status, t.type, t.snap_id)

SELECT t.id
FROM Test t
JOIN (
    SELECT item_id, MAX(created_at) AS created_at
    FROM Test t
    WHERE (platform_id = 2) AND (item_id IN (3,2,111)) AND (type = 'Default')
    GROUP BY item_id
) t2 ON
  t.item_id = t2.item_id
  AND t.created_at = t2.created_at
  AND t.type = 'Default'
WHERE t.platform_id = 2;

+----+
|    id |
+----+
|     6 |
|     3 |
+----+

--------------------------------

Disable uniq index for GROUP BY in subselect

SELECT t.id
FROM Test t
JOIN (
    SELECT item_id, MAX(created_at) AS created_at
    FROM Test t
    IGNORE INDEX FOR GROUP BY (uniq_idx)
    WHERE (platform_id = 2) AND (item_id IN (3,2,111)) AND (type = 'Default')
    GROUP BY item_id
) t2 ON
  t.item_id = t2.item_id
  AND t.created_at = t2.created_at
  AND t.type = 'Default'
WHERE t.platform_id = 2;

+----+
|    id |
+----+
|     6 |
|     3 |
|     5 |
+----+

--------------------------------

Use optimiser hint for disabling range optimizer for uniq index

SELECT t.id
FROM Test t
JOIN (
    SELECT /*+ NO_RANGE_OPTIMIZATION(t uniq_idx) */ item_id, MAX(created_at) AS created_at
    FROM Test t
    WHERE (platform_id = 2) AND (item_id IN (3,2,111)) AND (type = 'Default')
    GROUP BY item_id
) t2 ON
  t.item_id = t2.item_id
  AND t.created_at = t2.created_at
  AND t.type = 'Default'
WHERE t.platform_id = 2;

+----+
|    id |
+----+
|     6 |
|     3 |
|     5 |
+----+

In the first select one row is missing with id 5.

How to repeat:
Create table and run queries above
[5 Nov 2019 7:02] Umesh Shastry
Hello Илья Bkmz,

Thank you for the report and test case.

regards,
Umesh
[14 Nov 2019 13:51] Ilya Raudsepp
fix description
[19 Jan 20:27] Erlend Dahl
Fixed in 8.0.19 by

Author: Sergey Glukhov <sergey.glukhov@oracle.com>
Date:   Thu Sep 19 14:20:52 2019 +0400
 
    WL#13066 Improve predicate handling in loose index scan for group by.