This only happens for queries that force GROUP BY after ORDER BY.
Goal:
Get latest balance for each unit for the given cardID.
Table:
cardID | unit | balance | date
--------|-----------|-----------|--------------
A1 | DEPOSIT | 100 | 2016-05-01
A1 | DEPOSIT | 90 | 2016-05-02
A1 | DEPOSIT | 80 | 2016-05-03
A1 | DEPOSIT | 75 | 2016-05-04
A1 | MINUTE | 1000 | 2016-05-01
A1 | MINUTE | 900 | 2016-05-02
A1 | MINUTE | 800 | 2016-05-03
Query:
SELECT * FROM (
SELECT unit, balance
FROM cardBalances
WHERE cardID = 'A1'
ORDER BY date DESC
) AS cb
GROUP BY cb.unit;
Expected Result (MySQL v5.5.38):
unit | balance
---------|-----------
DEPOSIT | 75
MINUTE | 800
Unexpected Result (MySQL v5.7.13):
unit | balance
---------|-----------
DEPOSIT | 100
MINUTE | 1000
After upgrading to MySQL v5.7.13, the result returns the initial balances; as if no deduction occurred for the given card.
Is this a bug in MySQL version?
Would you suggest any other, more reliable way to solve this?
解决方案
This is a bug in your use of the database. MySQL is quite explicit that when you include columns in the SELECT clause in an aggregation query -- and they are not in the GROUP BY -- then they come from indeterminate rows.
Such syntax is specific to MySQL. It is not only a bad idea to learn, but it simply normally not work in other databases.
You can do what you want in various ways. Here is one:
SELECT cb.*
FROM cardBalances cb
WHERE cardId = 'A1' AND
cb.date = (SELECT MAX(date)
FROM cardBalances cb2
WHERE cb2.cardId = 'A1' AND cb2.unit = cb.unit
);
This has the advantage that it can use an index on cardBalances(unit, CardId, date).