[MDEV-29970] after upgrading MariaDB 10.2 to 10.6 WHERE IN () doesn't use index for BIGINT Created: 2022-11-08  Updated: 2023-01-22  Resolved: 2023-01-22

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: johny Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None

Attachments: JPEG File 1.jpg     JPEG File 2.jpg     JPEG File 3.jpg    

 Description   

A table with more than 650,000,000 rows does not use an index after updating MariaDB from 10.2 to 10.6

CREATE TABLE `parts` (
  `mainid` int(10)    UNSIGNED NOT NULL,
  `pid`    bigint(20) UNSIGNED NOT NULL,
  `name`   varchar(40) COLLATE utf8mb3_unicode_ci NOT NULL,
  `dt`     date                NOT NULL
) 
 
ALTER TABLE `parts`
  ADD PRIMARY KEY (`mainid`),
  ADD KEY `pid` (`pid`);

Maria uses an index when my queries have WHERE pid IN ( n1 ), but it doesn't use an index when my queries have WHERE pid IN ( n1, n2, n3 ).

Same query in MariaDB 10.2 (1.jpg) and MariaDB 10.6 (2.jpg)

EXPLAIN
SELECT
    *
FROM
    parts
WHERE
    pid IN (
        6826931823469672941,
        2750571406036799415,
         156705013816687978
    );

Curiously, if I put the bigint literals in quotes it uses a better plan: (3.jpg)

EXPLAIN
SELECT
    *
FROM
    parts
WHERE
    pid IN (
        '6826931823469672941',
        '2750571406036799415',
         '156705013816687978'
    );

I tried to change eq_range_index_dive_limit to 0 (value in 10.2) from 200 (value in 10.6) in my.cnf, but it didn't help. Same with the maximum value of 4294967295.

Can I use single quotes for BIGINT? As I understand it, it's the same if I don't use them



 Comments   
Comment by Daniel Black [ 2022-11-08 ]

By basic example test on 10.6.12 resulted in a the use of the pid index. Can you obtain an optimizer trace and attach as text.

Like MDEV-28531 I think that the large values beyond max int should be dropped and result in impossible where.

Quoted numbers apply the rules for type conversion.

Generated at Thu Feb 08 10:12:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.