Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.4, 5.5(EOL), 10.0(EOL)
Description
MIN/MAX optimizer doesn't take into account type conversions. This may cause query results to be different depending on whether there is an index.
Testcase (not minimal, it may be possible to simplify further):
create table t2 ( a char(10) not null, key(a)) engine=innodb;
|
|
|
insert into t2 values ('foo-123');
|
insert into t2 values ('bar-123');
|
insert into t2 values ('baz-123');
|
insert into t2 values ('abc-123');
|
insert into t2 values ('-1234');
|
insert into t2 values ('-99');
|
insert into t2 values ('-99999');
|
|
|
select max(a) from t2 where a < 432;
|
select max(a) from t2 ignore index(a) where a < 432;
|
MariaDB [j3]> select max(a) from t2 where a < 432;
|
+--------+
|
| max(a) |
|
+--------+
|
| -99999 |
|
+--------+
|
1 row in set (0.00 sec)
|
MariaDB [j3]> select max(a) from t2 ignore index(a) where a < 432;
|
+---------+
|
| max(a) |
|
+---------+
|
| foo-123 |
|
+---------+
|
1 row in set, 4 warnings (0.01 sec)
|
EXPLAINs:
MariaDB [j3]> explain select max(a) from t2 where a < 432;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [j3]> explain select max(a) from t2 ignore index(a) where a < 432;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.01 sec)
|
Filed in the upstream as http://bugs.mysql.com/bug.php?id=70886
Setting priority to MINOR as this problem exists for a long time and didn't affect anybody.