Bug #16280 When Selecting using MAX(), value of the entire row is incorrect
Submitted: 7 Jan 2006 19:25 Modified: 7 Feb 2006 21:53
Reporter: Churk Leung Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.16 OS:Windows (Windows XP)
Assigned to: Assigned Account CPU Architecture:Any

[7 Jan 2006 19:25] Churk Leung 
Description:
I have this table, name MAIN_RECORD. When I perform this
SELECT
	MAX(MAIN_RECORD.PAYMENT_DATE) AS PAYMENTDATE,
	MAIN_RECORD.PAYEE_NAME AS PAYEENAME,
	MAIN_RECORD.PAYMENT_AMOUNT AS PAYMENTAMOUNT,
	MAIN_RECORD.TRACKING_NUMBER AS TRACKINGNUMBER,
	MAIN_RECORD.PAYMENT_METHOD AS PAYMENTMETHOD
FROM
	MAIN_RECORD
GROUP BY
	MAIN_RECORD.PAYEE_NAME
ORDER BY
	PAYEENAME,
	PAYMENTDATE
;

I expect to get the maximum payment date, and the value of that row.
But when I did that, value from another row was retrieve, but the max payment_date was display correctly.

How to repeat:
Here is my table
+------------------+--------------+---------------------+----------------+-----------------+------------------+
| Payee_Name       | Payment_Date | Date_Entered        | Payment_Amount | Tracking_Number | Payment_Method   |
+------------------+--------------+---------------------+----------------+-----------------+------------------+
| AMERICAN EXPRESS | 2005-10-24   | 2005-12-26 14:37:27 | 1305.00        | W6182           | CHECKING         |
| AMERICAN EXPRESS | 2005-10-24   | 2005-12-26 14:36:26 | 400.00         |  W3759          | CHECKING         |
| AMERICAN EXPRESS | 2005-11-23   | 2005-12-24 10:32:21 | 500.00         | W7754           | AMERICAN EXPRESS |
| AMERICAN EXPRESS | 2005-12-10   | 2005-12-24 10:32:21 | 1000.00        | W7635           | CHECKING         |
| AMERICAN EXPRESS | 2005-12-24   | 2005-12-24 10:32:21 | 200.00         | UNKNOW          | CHECKING         |
| AMERICAN EXPRESS | 2006-01-07   | 2006-01-07 13:14:58 | 800.00         | W9026           | CHECKING         |
| CINGULAR         | 2005-09-02   | 2005-12-26 15:02:11 | 84.64          | QPCODT88042203  | AMERICAN EXPRESS |
| CINGULAR         | 2005-09-29   | 2005-12-26 15:01:04 | 84.68          | QPCODT94174070  | AMERICAN EXPRESS |
| CINGULAR         | 2005-11-04   | 2005-12-26 14:29:26 | 84.69          | QPCODT102548088 | AMERICAN EXPRESS |
| CINGULAR         | 2005-11-30   | 2005-12-24 10:32:21 | 84.70          | QPCODT109446605 | AMERICAN EXPRESS |
| CINGULAR         | 2005-12-30   | 2005-12-30 14:09:14 | 84.64          | QPCODT117709153 | AMERICAN EXPRESS |
| EZ-PASS          | 2005-10-14   | 2005-12-26 14:58:25 | 47.50          | 84612           | AMERICAN EXPRESS |
| GREAT LAKES      | 2005-11-17   | 2005-12-25 02:26:56 | 800.00         | 674946          | CHECKING         |
| GREAT LAKES      | 2005-12-24   | 2005-12-24 10:32:21 | 500.00         | 322628          | CHECKING         |
| HONDA FINANCIAL  | 2005-11-21   | 2005-12-24 10:32:21 | 400.00         | 1121432172      | CHECKING         |
| HONDA FINANCIAL  | 2005-12-18   | 2005-12-30 14:10:53 | 400.00         | 1218446453      | CHECKING         |
| MBNA             | 2005-10-08   | 2005-12-26 15:00:18 | 192.22         | 4LGBG-4WTK3     | CHECKING         |
| MBNA             | 2005-11-13   | 2005-12-25 02:31:07 | 170.80         | 4PRLN-QYP4Q     | CHECKING         |
| MBNA             | 2005-12-27   | 2005-12-25 02:37:43 | 180.44         | 4V17F-9SP9Y     | CHECKING         |
| PROGRESSIVE      | 2005-08-22   | 2005-12-26 15:04:25 | 1646.00        | 022723          | CHECKING         |
| RCN              | 2005-10-22   | 2005-12-26 14:54:00 | 77.05          | NONE            | AMERICAN EXPRESS |
| RCN              | 2005-11-21   | 2005-12-24 10:32:21 | 77.05          | UNKNOWN         | AMERICAN EXPRESS |
| RCN              | 2005-12-24   | 2005-12-24 10:32:21 | 77.05          | UNKNOWN         | AMERICAN EXPRESS |
| RENT             | 2005-08-01   | 2005-12-24 10:32:21 | 1100.00        | CHECK #208      | CHECKING         |
| RENT             | 2005-09-01   | 2005-12-24 10:32:21 | 1100.00        | CHECK #210      | CHECKING         |
| RENT             | 2005-10-01   | 2005-12-24 10:32:21 | 1100.00        | CHECK #216      | CHECKING         |
| RENT             | 2005-11-01   | 2005-12-24 10:32:21 | 1100.00        | CHECK #218      | CHECKING         |
| RENT             | 2005-12-01   | 2005-12-24 10:32:21 | 1100.00        | CHECK #221      | CHECKING         |
| RENT             | 2006-01-01   | 2006-01-07 13:13:50 | 1100.00        | Check 222       | CHECKING         |
| SALLI MAE        | 2005-10-08   | 2005-12-26 14:59:40 | 200.00         | 072833101205    | CHECKING         |
| SALLI MAE        | 2005-11-09   | 2005-12-26 14:28:36 | 200.00         | 213515110905    | CHECKING         |
| SALLI MAE        | 2005-12-10   | 2005-12-24 10:32:21 | 200.00         | 161259121005    | CHECKING         |
+------------------+--------------+---------------------+----------------+-----------------+------------------+

Here is my result when I ran the Query
SELECT
	MAX(MAIN_RECORD.PAYMENT_DATE) AS PAYMENTDATE,
	MAIN_RECORD.PAYEE_NAME AS PAYEENAME,
	MAIN_RECORD.PAYMENT_AMOUNT AS PAYMENTAMOUNT,
	MAIN_RECORD.TRACKING_NUMBER AS TRACKINGNUMBER,
	MAIN_RECORD.PAYMENT_METHOD AS PAYMENTMETHOD
FROM
	MAIN_RECORD
GROUP BY
	MAIN_RECORD.PAYEE_NAME
ORDER BY
	PAYEENAME,
	PAYMENTDATE
;

+-------------+------------------+---------------+-----------------+------------------+
| PAYMENTDATE | PAYEENAME        | PAYMENTAMOUNT | TRACKINGNUMBER  | PAYMENTMETHOD    |
+-------------+------------------+---------------+-----------------+------------------+
| 2006-01-07  | AMERICAN EXPRESS | 200.00        | UNKNOW          | CHECKING         |
| 2005-12-30  | CINGULAR         | 84.70         | QPCODT109446605 | AMERICAN EXPRESS |
| 2005-10-14  | EZ-PASS          | 47.50         | 84612           | AMERICAN EXPRESS |
| 2005-12-24  | GREAT LAKES      | 500.00        | 322628          | CHECKING         |
| 2005-12-18  | HONDA FINANCIAL  | 400.00        | 1121432172      | CHECKING         |
| 2005-12-27  | MBNA             | 170.80        | 4PRLN-QYP4Q     | CHECKING         |
| 2005-08-22  | PROGRESSIVE      | 1646.00       | 022723          | CHECKING         |
| 2005-12-24  | RCN              | 77.05         | UNKNOWN         | AMERICAN EXPRESS |
| 2006-01-01  | RENT             | 1100.00       | CHECK #221      | CHECKING         |
| 2005-12-10  | SALLI MAE        | 200.00        | 161259121005    | CHECKING         |
+-------------+------------------+---------------+-----------------+------------------+
[7 Jan 2006 21:53] MySQL Verification Team 
Could you please provide a complete test case script, with table create,
inserts and failed queries?

Thanks in advance.
[8 Feb 2006 0:00] Bugs System 
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".