[MDEV-3560] LP:884175 - Wrong result with aggregate + varchar key Created: 2011-10-31  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug884175.xml    

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

 Comments   
Comment by Elena Stepanova [ 2012-01-12 ]

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.

Comment by Oleksandr Byelkin [ 2012-02-06 ]

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.

Comment by sbester [ 2012-02-22 ]

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.

Comment by Igor Babaev [ 2012-03-09 ]

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)

Comment by Rasmus Johansson (Inactive) [ 2012-04-03 ]

Launchpad bug id: 884175

Generated at Thu Feb 08 06:49:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.