Wrong result with aggregate + varchar key

Bug #884175 reported by Philip Stoev on 2011-10-31
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
Critical
Igor Babaev

Bug Description

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';

Changed in maria:
importance: Undecided → Critical
assignee: nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin)
Elena Stepanova (elenst) wrote :

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

Changed in maria:
milestone: none → 5.1
Changed in maria:
milestone: 5.1 → 5.2
status: New → Incomplete
status: Incomplete → In Progress

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.

Changed in maria:
status: In Progress → Confirmed
sbester (sbester) wrote :

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.

Changed in maria:
assignee: Oleksandr "Sanja" Byelkin (sanja-byelkin) → Igor Babaev (igorb-seattle)
Changed in maria:
status: Confirmed → In Progress
Igor Babaev (igorb-seattle) wrote :
Download full text (3.2 KiB)

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...

Read more...

Changed in maria:
status: In Progress → Fix Committed
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.