[MDEV-16875] Index on VARCHAR column not used to lookup INT constant Created: 2018-08-01  Updated: 2018-08-04  Resolved: 2018-08-04

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Optimizer
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: cast


 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.

 Comments   
Comment by Valerii Kravchuk [ 2018-08-01 ]

MySQL manual (https://dev.mysql.com/doc/refman/5.5/en/type-conversion.html) is clear about this case:

" For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:

SELECT * FROM tbl_name WHERE str_col=1;

The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'."

Comment by Elena Stepanova [ 2018-08-03 ]

greenman, could you please add a note about it in a suitable place in the KB?

Comment by Ian Gilfillan [ 2018-08-04 ]

Note and example documented at https://mariadb.com/kb/en/library/type-conversion/

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