MySQL Bugs: #55810: incorrect max aggregate calculated

Bug #55810	incorrect max aggregate calculated
Submitted:	6 Aug 2010 21:07	Modified:	7 Aug 2010 12:52
Reporter:	sergei z	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.5.5-m3	OS:	Windows (Vista x64 and Server 2008 x64)
Assigned to:		CPU Architecture:	Any

Description:
I've asked this on a blog http://forums.mysql.com/read.php?10,379670,379670#msg-379670 and it seems nobody could disprove it.

Run code below. The first query (with WHERE) is the one I'd use in production. However, it gives the wrong answer -- it seems take the row last inserted into invoice table, instead of calculating max over restricted by where set.

I ran these same queries on SQL Server 2008 Express and sqlite 3.7.0. Both gave correct answers. A poster on the forum said he checked it on 5.1.32-community and ran correctly. What could be the problem?

How to repeat:
drop temporary table if exists invoice, line; 
create temporary table invoice(id int not null primary key, date date not null); 
create temporary table line(id int not null primary key, invoiceid int not null, productid int not null); 

insert into invoice values(1, '2010-01-31'),(2, '2010-03-31'),(3,'2010-05-31'),(4,'2010-04-30'); 
insert into line values(1,1,6660),(2,2,6660),(3,3,6660),(4,4,6660); 

select line.productid, max(invoice.date) 
from line join invoice on line.invoiceid=invoice.id 
where line.productid=6660 
group by line.productid; 

select line.productid, max(invoice.date) 
from line join invoice on line.invoiceid=invoice.id 
group by line.productid 
having line.productid=6660;

Thank you for the bug report. Could you please print here the output you got for both queries. Thanks in advance.

===========================================================
RESULT FROM MySQL 5.5.5-m3
===========================================================

+-----------+-------------------+
| productid | max(invoice.date) |
+-----------+-------------------+
|      6660 | 2010-04-30        | -- WRONG!!!
+-----------+-------------------+
1 row in set (0.00 sec)

+-----------+-------------------+
| productid | max(invoice.date) |
+-----------+-------------------+
|      6660 | 2010-05-31        |
+-----------+-------------------+
1 row in set (0.00 sec)

===========================================================
RESULT FROM MSSQL 2008 Express
===========================================================
productid
----------- ----------------
       6660       2010-05-31

(1 rows affected)
productid
----------- ----------------
       6660       2010-05-31

(1 rows affected)

===========================================================
RESULT FROM sqlite 3.7.0
===========================================================
sqlite> select line.productid, max(invoice.date)
   ...> from line join invoice on line.invoiceid=invoice.id
   ...> where line.productid=6660
   ...> group by line.productid;
6660|2010-05-31

sqlite> select line.productid, max(invoice.date)
   ...> from line join invoice on line.invoiceid=invoice.id
   ...> group by line.productid
   ...> having line.productid=6660;
6660|2010-05-31

Thank you for the feedback.

I was able to repeat the problem with mysql-5.5.5-m3-linux2.6-x86_64.tar.gz package, but was not able with current development sources. So looks like this occasionally fixed. Please wait next release.