Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-31303

Key not used when IN clause has both signed and unsigned values

    XMLWordPrintable

Details

    Description

      When using IN() operator, if the provided values are both within the 1st half and the 2nd half of the column size ( < 9223372036854775808 and >= 9223372036854775808 for unsigned bigint), the index is not used.

      This behavior has changed from the 10.1.* versions where the index was correctly used.

      SQL to reproduce:

      CREATE TABLE `a` (
        `id` bigint AUTO_INCREMENT PRIMARY KEY,
        `c1` bigint unsigned,
        KEY (`c1`)
      ) ENGINE=InnoDB;
       
      INSERT INTO `a` VALUES (1,9223382399205928659),(2,9223384207280813348),(3,9223385953115437234),(4,9223387250780556749),(5,9223387354282558788),(6,9223387603870501596),(7,9223389270813433667),(8,9223389903231468827),(9,9223390280789586779),(10,9223391591398222899),(11,9223391875473564350),(12,9223393152250049433),(13,9223393939696790223),(14,9223394417225350415),(15,9223397646397141015),(16,9223398025879291243),(17,9223399038671098072),(18,9223399534968874556),(19,9223400449518009285),(20,9223400860292643549),(21,9223400940692256924),(22,9223401073791948119),(23,9223402820804649616),(24,9223403470951992681),(25,9223405581879567267),(26,9223405754978563829),(27,9223405972966828221);
       
      MariaDB [test]> explain SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775807 );
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | a     | range | c1            | c1   | 9       | NULL |    2 | Using where; Using index |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
       
       
      MariaDB [test]> explain SELECT c1 FROM a WHERE c1 IN ( 1, 9223372036854775808 );
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | a     | index | c1            | c1   | 9       | NULL |   27 | Using where; Using index |
      +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
      

      Attachments

        Activity

          People

            cvicentiu Vicențiu Ciorbaru
            vnsavage Iliya Polihronov
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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