Details
Minor Description
We encountered an issue where the query optimizer doesn't end up applying the "impossible_range" optimization if a query contains out of range values enclosed by quotes. We first noticed this on 10.11.14, but reproduced it on 12.1.2 as well
For example:
MariaDB [test]> create table test1 (id int(10), PRIMARY KEY(id));
|
Query OK, 0 rows affected (0.005 sec)
|
|
|
MariaDB [test]> insert into test1 values(1);
|
Query OK, 1 row affected (0.002 sec)
|
|
|
MariaDB [test]> insert into test1 values(2);
|
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [test]> explain select * from test1 where id in ('802388088375689216','802387982614183936');
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | test1 | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> explain select * from test1 where id in (802388088375689216,802387982614183936);
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> explain select * from test1 where id = '802388088375689216' or id ='802387982614183936';
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | test1 | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> explain select * from test1 where id = 802388088375689216 or id =802387982614183936;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (0.000 sec)
|
This does not happen if the values are in range for the column, and only happens when the values exceed the max size for the column
MariaDB [test]> explain select * from test1 where id in ('1', '2');
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| 1 | SIMPLE | test1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> explain select * from test1 where id in ('2147483648','2147483649');
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | test1 | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.001 sec)
|
I attached optimizer_trace results for two of these queries, when the values are integers it applies the `"impossible_range": true` optimization, but not when they're provided as strings.