MySQL Bugs: #17670: Eroneous empty record set returned using MAX and GROUP BY with unique key

Bug #17670	Eroneous empty record set returned using MAX and GROUP BY with unique key
Submitted:	23 Feb 2006 13:58	Modified:	25 May 2006 10:36
Reporter:	Peter Myles	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18, 5.0.19-bk	OS:	Linux (Linux, Windows)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
A select statement reutrns an unexpectedly empty result when;

the MAX() function is used

the table has a unique key refering to both fields returned in the query

>=  240 records (adding & removing dummy  records not referenced in the query changes between expected and unexpected results) 

How to repeat:
mysql> show create table futures_tab\G
*************************** 1. row ***************************
       Table: futures_tab
Create Table: CREATE TABLE `futures_tab` (
					  `ticker` double NOT NULL default '0',
					  `close` double default '0',
					  `j_num` double NOT NULL default '0',
					  PRIMARY KEY  (`ticker`,`j_num`)
					) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

With 239 records behaves normaly.

With 240 records present;

mysql>  SELECT ticker, MAX(jnum)  FROM futures_tab  WHERE  jnum < 2453775  AND ticker = 703913  GROUP BY ticker;
Empty set (0.00 sec)

mysql>  SELECT ticker, MIN(jnum)  FROM futures_tab  WHERE  jnum < 2453775  AND ticker = 703913  GROUP BY ticker;                                                
Empty set (0.00 sec)

mysql>  SELECT ticker, MAX(jnum)  FROM futures_tab  WHERE  jnum = 2453775  AND ticker = 703913  GROUP BY ticker;
+--------+-----------+
| ticker | MAX(jnum) |
+--------+-----------+
| 703913 |   2453774 |
+--------+-----------+
1 row in set (0.00 sec)

mysql>  SELECT ticker, COUNT(jnum)  FROM futures_tab  WHERE  jnum < 2453775  AND ticker = 703913  GROUP BY ticker;
+--------+-------------+
| ticker | COUNT(jnum) |
+--------+-------------+
| 703913 |         592 |
+--------+-------------+
1 row in set (0.00 sec)

mysql>  SELECT ticker, MAX(jnum), COUNT(jnum)  FROM futures_tab  WHERE  jnum < 2453775  AND ticker = 703913  GROUP BY ticker;
+--------+-----------+-------------+
| ticker | MAX(jnum) | COUNT(jnum) |
+--------+-----------+-------------+
| 703913 |   2453774 |         592 |
+--------+-----------+-------------+
1 row in set (0.00 sec)

Testcase to repeat: bug17670_2.sql;

Attachment: bug17670_2.sql (text/x-delimtext), 13.34 KiB.

repeated on 5.0.18 on Windows, and 5.0.19-bk on Linux, using my attached testcase.

This looks like expected behavior taking into account double fields.
You should modify your query according to manual:
http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

New query should look like:
SELECT c1, MAX(c2) FROM t2 WHERE c2<2453775 AND c1>703912.99 AND c1<703913.01 GROUP BY c1;