[#MDEV-5283] MIN/MAX optimizer doesn't take into account type conversions

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)

[MDEV-5283] MIN/MAX optimizer doesn't take into account type conversions Created: 2013-11-12 Updated: 2015-04-03
Status:	Open
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.0.4, 5.5, 10.0
Fix Version/s:	10.1