Wrong result with aggregate + varchar key

[Philip Stoev]

The following query:

SELECT MAX(f2) FROM t1 where f2 = 'abc';

returns rows even though f2 is defined as VARCHAR(1) and does not contain the value 'abc'. If the column does not have an index, no rows are returned.

Explain: Select tables optimized away
 select max(`test`.`t1`.`f2`) AS `MAX(f2)` from `test`.`t1` where multiple equal('abc', `test`.`t1`.`f2`)

repeatable in maria-5.2, mysql-5.5

test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (f1 VARCHAR(1), f2 VARCHAR(1), KEY (f2));
INSERT INTO t1 VALUES ('a','a');

SELECT MAX(f1) FROM t1 where f1 = 'abc';
SELECT MAX(f2) FROM t1 where f2 = 'abc';

[Elena Stepanova (elenst)]

Still reproducible on 5.1, 5.2, 5.3, 5.5. Also reproducible on MySQL-5.1.60 and MySQL-5.5.19.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The problem does not depends on number of rows => it is min max optimization of aggregate functions.

This could be cause:

WHERE:(after remove) multiple equal('abc', `test`.`t1`.`f2`)

as far as it can't be true due to field length.

[sbester (sbester)]

It reminds me of bugs like this:

http://bugs.mysql.com/bug.php?id=45680
(wrong results when using index for lookup with implicitly casted values)

you're using varchar(1) but looking for a 3 char string..
if you increase the table varchar to longer value, or disable in the index, it returns expected.

[Igor Babaev (igorb-seattle)]

After adding another row into the table t1:
INSERT INTO t1 VALUES ('b', 'b');
similar problems with comparison predicates and with BETWEEN easily can be demonstrated:

MariaDB [test]> EXPLAIN
    -> SELECT MIN(f1) FROM t1 WHERE f1 >= 'abc';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT MIN(f1) FROM t1 WHERE f1 >= 'abc';
+---------+
| MIN(f1) |
+---------+
| b |
+---------+
1 row in set (0.00 sec)

MariaDB [test]>
MariaDB [test]> EXPLAIN
    -> SELECT MIN(f2) FROM t1 WHERE f2 >= 'abc';
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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 [test]> SELECT MIN(f2) FROM t1 WHERE f2 >= 'abc';
+---------+
| MIN(f2) |
+---------+
| a |
+---------+
1 row in set (0.00 sec)

MariaDB [test]>
MariaDB [test]> EXPLAIN
    -> SELECT MIN(f1) FROM t1 WHERE f1 BETWEEN 'abc' AND 'b' ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT MIN(f1) FROM t1 WHERE f1 BETWEEN 'abc' AND 'b' ;
+---------+
| MIN(f1) |
+---------+
| b |
+---------+
1 row in set (0.00 sec)

MariaDB [test]>
MariaDB [test]> EXPLAIN
    -> SELECT MIN(f2) FROM t1 WHERE f2 BETWEEN 'abc' AND 'b' ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No matching min/max row |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT MIN(f2) FR...