MySQL Bugs: #5406: SELECT MAX(field) sometimes ignores WHERE clause

Bug #5406	SELECT MAX(field) sometimes ignores WHERE clause
Submitted:	4 Sep 2004 5:53	Modified:	8 Sep 2004 4:20
Reporter:	Dan Nelson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.4	OS:	Any (all)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
It looks like MySQL 4.1 tries to optimize away certain queries asking for "MAX(field) WHERE condition" where field is indexed, but it ends up deciding that it can return the cached MAX from the index even though the WHERE clause excludes that value.  Happens with all storage engines.

How to repeat:

SELECT VERSION();
 +-----------------------+
 | VERSION()             |
 +-----------------------+
 | 4.1.4-gamma-debug-log |
 +-----------------------+
CREATE TABLE foo (bar int primary key);
INSERT INTO foo VALUES (1),(2),(3),(4);
SELECT MAX(bar) FROM foo WHERE bar > 5;
 +----------+
 | MAX(bar) |
 +----------+
 |        4 |
 +----------+
SELECT * FROM foo WHERE bar > 5;
 Empty set (0.00 sec)

The bug was due to the fact that the function reckey_in_range was not called in all cases where it should be called.
Fixed in file sql/opt_sum.cc.
ChangeSet
  1.2006 04/09/07 19:46:09