Description:
MIN/MAX optimizer doesn't take into account type conversions. This means that queries may return different values depending on whether the table has an index.
I am attaching a testcase to demonstrate the problem. This particular testcase looks artificial and unrealistic (who stores INT values in a char column), but this is only to make the problem apparent. One can come up with cases with subtler differences (e.g. use different collations, datetime comparisons, etc) where the queries will look like they make sense but result will be incorrect.
How to repeat:
create table t2 ( a char(10) not null, key(a)) engine=innodb;
insert into t2 values ('foo-123');
insert into t2 values ('-1234');
insert into t2 values ('-99');
insert into t2 values ('-99999');
MySQL [test]> select max(a) from t2 where a < 432;
+--------+
| max(a) |
+--------+
| -99999 |
+--------+
1 row in set (0.00 sec)
MySQL [test]> select max(a) from t2 ignore index(a) where a < 432;
+---------+
| max(a) |
+---------+
| foo-123 |
+---------+
1 row in set, 4 warnings (0.00 sec)
MySQL [test]> 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.01 sec)
MySQL [test]> 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.00 sec)
MySQL [test]>
Suggested fix:
select max(...) is a deterministic function and so must always return the same result regardless of what indexes are present.
Description: MIN/MAX optimizer doesn't take into account type conversions. This means that queries may return different values depending on whether the table has an index. I am attaching a testcase to demonstrate the problem. This particular testcase looks artificial and unrealistic (who stores INT values in a char column), but this is only to make the problem apparent. One can come up with cases with subtler differences (e.g. use different collations, datetime comparisons, etc) where the queries will look like they make sense but result will be incorrect. How to repeat: create table t2 ( a char(10) not null, key(a)) engine=innodb; insert into t2 values ('foo-123'); insert into t2 values ('-1234'); insert into t2 values ('-99'); insert into t2 values ('-99999'); MySQL [test]> select max(a) from t2 where a < 432; +--------+ | max(a) | +--------+ | -99999 | +--------+ 1 row in set (0.00 sec) MySQL [test]> select max(a) from t2 ignore index(a) where a < 432; +---------+ | max(a) | +---------+ | foo-123 | +---------+ 1 row in set, 4 warnings (0.00 sec) MySQL [test]> 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.01 sec) MySQL [test]> 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.00 sec) MySQL [test]> Suggested fix: select max(...) is a deterministic function and so must always return the same result regardless of what indexes are present.