MySQL Bugs: #2927: EXTRACT() with GROUP BY produces wrong results

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

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

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

sorry, been on the wrong version, please ignore previous

This bug isn't critical enough to warrant a fix in 3.23, and as noted it already works in 4.0 and later.