Why you should take care of MySQL data types

A customer reported last month that MySQL does a full table scan (FTS) if a query was filtered by a INT value on a VARCHAR column. First I told him that this is not true any more because MySQL has fixed this behaviour long time ago. He showed me that I was wrong:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `data` (`data`)
) ENGINE=InnoDB;

EXPLAIN SELECT * FROM test WHERE data = 42\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ALL
possible_keys: data
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 522500
     filtered: 10.00
        Extra: Using where

EXPLAIN SELECT * FROM test WHERE data = '42'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: data
          key: data
      key_len: 67
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

When I executed the query I got some more interesting information:

SELECT * FROM test WHERE data = '42';
Empty set (0.00 sec)

SELECT * FROM test WHERE data = 42;
+--------+----------------------------------+---------------------+
| id     | data                             | ts                  |
+--------+----------------------------------+---------------------+
|   1096 | 42a5cb4a3e76857a3efe7af44ba9f4dd | 2016-05-25 10:26:59 |
...
| 718989 | 42a1921fb2df42126d85f9586532eda4 | 2016-05-25 10:27:12 |
+--------+----------------------------------+---------------------+
767 rows in set, 65535 warnings (0.26 sec)

Looking at the warnings we also find the reason: MySQL does the cast on the column and not on the value which is a bit odd IMHO:

show warnings;
| Warning | 1292 | Truncated incorrect DOUBLE value: '80f52706c2f9de40472ec29a7f70c992' |

A bit suspicious I looked at the warnings of the query execution plan again:

show warnings;
+---------+------+---------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                     |
+---------+------+---------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'data' due to type or collation conversion on field 'data'   |
| Warning | 1739 | Cannot use range access on index 'data' due to type or collation conversion on field 'data' |
+---------+------+---------------------------------------------------------------------------------------------+

I thought this was fixed, but it seems not. The following releases behave like this: MySQL 5.0.96, 5.1.73, 5.5.38, 5.6.25, 5.7.12 and MariaDB 5.5.41, 10.0.21 and 10.1.9

The other way around it seems to work in both cases:

SELECT * FROM test WHERE id = 42;
+----+----------------------------------+---------------------+
| id | data                             | ts                  |
+----+----------------------------------+---------------------+
| 42 | 81d74057d7be8f20563da404bb1b3ab0 | 2016-05-25 10:26:56 |
+----+----------------------------------+---------------------+

SELECT * FROM test WHERE id = '42';
+----+----------------------------------+---------------------+
| id | data                             | ts                  |
+----+----------------------------------+---------------------+
| 42 | 81d74057d7be8f20563da404bb1b3ab0 | 2016-05-25 10:26:56 |
+----+----------------------------------+---------------------+

EXPLAIN SELECT * FROM test WHERE id = 42\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL