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

        Issue Links

          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.