Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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';
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with aggregate + varchar key
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.
Re: Wrong result with aggregate + varchar key
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.
Re: Wrong result with aggregate + varchar key
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) FROM t1 WHERE f2 BETWEEN 'abc' AND 'b' ;
---------
| MIN(f2) |
---------
| NULL |
---------
1 row in set (0.00 sec)
Launchpad bug id: 884175
Re: Wrong result with aggregate + varchar key
Still reproducible on 5.1, 5.2, 5.3, 5.5. Also reproducible on MySQL-5.1.60 and MySQL-5.5.19.