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

Index on VARCHAR column not used to lookup INT constant

    Details

      Description

      I expect the index to be used in this case, but it isn't:

      MariaDB [test]> CREATE TABLE t (a VARCHAR(10), b VARCHAR(10), INDEX idx_a (a));
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [test]> INSERT INTO t VALUES ('1', '1'), ('2', '2'), ('3', '3'), ('4', '4'), ('5', '5'), ('1', '5');
      Query OK, 6 rows affected (0.01 sec)
      Records: 6  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> EXPLAIN SELECT * FROM t WHERE a = 3 \G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: t
               type: ALL
      possible_keys: idx_a
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 6
              Extra: Using where
      1 row in set (0.00 sec)
      

      Why do I expect that? Because an index is used if the query implies the opposite conversion (int to varchar):

      MariaDB [test]> CREATE TABLE t (a INT UNSIGNED, b INT UNSIGNED, INDEX idx_a (a));
      Query OK, 0 rows affected (0.01 sec)
      MariaDB [test]> INSERT INTO t VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (1, 5);
      Query OK, 6 rows affected (0.00 sec)
      Records: 6  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> EXPLAIN SELECT * FROM t WHERE a = '3' \G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: t
               type: ref
      possible_keys: idx_a
                key: idx_a
            key_len: 5
                ref: const
               rows: 1
              Extra: 
      1 row in set (0.00 sec)
      

      Bug found on 10.2, test ran on 10.0.

      I did not check MySQL.

        Attachments

          Activity

            People

            • Assignee:
              greenman Ian Gilfillan
              Reporter:
              f_razzoli Federico Razzoli
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: