MySQL Bugs: #33417: group by 3 columns with MAX() produces incorrect results

Bug #33417	group by 3 columns with MAX() produces incorrect results
Submitted:	20 Dec 2007 19:38	Modified:	8 Nov 2009 16:18
Reporter:	Bruce Greenblatt	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.46, 5.0.52	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I've been using this query to populate a temporary table in our code from many years without problems.  Recently, I noticed that it is not producing the correct results anymore, after upgrading to 5.0.46.

mysql> select s.hmid, s.volid, s.sampletid, max(s.sampletime) as sampletime from ark_hist_volumesamples s where sampletid in (10, 11) group by s.hmid, s.volid, s.sampletid;
...
9 rows in set (0.00 sec)

In my database, hmid represents a unique machine, and volid represents a unique volume on that machine.  It turns out the volids are globally unique, so I removed the group by hmid from the query, and thankfully got the correct results:

mysql> select s.hmid, s.volid, s.sampletid, max(s.sampletime) as sampletime from ark_hist_volumesamples s where s.sampletid in (10,11) group by s.volid,s.sampletid;
...
124 rows in set (0.01 sec)

Based on the data in our database, the group by hmid clause is redundant, and I don't think that it should not effect the results.

How to repeat:
I have saved the mysqldump for the ark_hist_volumesamples.  Perhaps the results are data dependent and are related to an optimization issue?

Mysql dump output for ark_hist_volumesamples table

Attachment: mysqldump.sql (text/plain), 81.61 KiB.

I also saved the .myd, .myi and .frm files from the table if the mysqldump output is insufficient, and can upload those if necessary.  Table uses the MYISAM engine.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51 or 5.0.52, and inform about the results.

I get identical results with 5.0.52:

Server version: 5.0.52-classic-nt-log MySQL Enterprise Server - Classic (Commercial)

The same thing happens with min().

I am just starting out learning mysql and as I was working through the ref man I discovered this error.

command window contents below:

mysql> select article, min(price) as price, dealer
    -> from shop
    -> group by article;
+---------+-------+--------+
| article | price | dealer |
+---------+-------+--------+
|    0001 |  3.45 | A      |
|    0002 | 10.99 | A      |
|    0003 |  1.25 | B      |
|    0004 | 19.95 | D      |
+---------+-------+--------+
4 rows in set (0.00 sec)

mysql> select * from shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
7 rows in set (0.00 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.31, for Win32 (ia32)

Connection id:          5
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.1.31-community MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 1 hour 59 min 0 sec

I couldn't repeat anymore with current source server 5.0.87, please try to upgrade at least to the last release. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".