Bug #2927 EXTRACT() with GROUP BY produces wrong results
Submitted: 23 Feb 2004 10:49 Modified: 10 Aug 2005 17:28
Reporter: Dean Ellis Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:3.23.58 OS:Linux (Linux)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[23 Feb 2004 10:49] Dean Ellis
Description:
EXTRACT/GROUP BY combination produces incorrect results.  Only affects 3.23.

How to repeat:
CREATE TABLE test (a datetime, v integer);
INSERT INTO test VALUES ('2003-01-01', 1);
INSERT INTO test VALUES ('2003-04-01', 1);
INSERT INTO test VALUES ('2002-04-01', 1);
INSERT INTO test VALUES ('2003-01-01', 2);
SELECT SUM(v), EXTRACT(MONTH FROM a) FROM test WHERE EXTRACT(YEAR FROM a) = 2003
GROUP BY EXTRACT(MONTH FROM a);
DROP TABLE test;

Should produce:

+--------+-----------------------+
| SUM(v) | EXTRACT(MONTH FROM a) |
+--------+-----------------------+
|      3 |                     1 |
|      1 |                     4 |
+--------+-----------------------+

Suggested fix:
n/a
[20 Feb 2005 1:35] Martin Friebe
The result looks valid to me, after applying the where, we look at the remaining rows:
2 in 2003-01 with v = 1 and v = 2  ( sum 3 / month 1)
1 in 2003-04 with v = 1                ( sum 1 / month 4)
and the row in 2002-04 is not in the result
[20 Feb 2005 1:41] Martin Friebe
sorry, been on the wrong version, please ignore previous
[10 Aug 2005 17:28] Jim Winstead
This bug isn't critical enough to warrant a fix in 3.23, and as noted it already works in 4.0 and later.