/*
 * Test case to MySQL bug #2901
 * Beat Vontobel, MeteoNews GmbH, 2004-02-20
 * Server crash (4.1.1-alpha-standard-log) on multiple use
 * of COUNT(*) in expressions in an unnamed view
 */

/*
 * Test case table preparation
 */
CREATE TABLE t (n CHAR(1), t INT, c1 INT, c2 INT, c3 INT);
INSERT INTO t VALUES ('a', 1, 1, 1, 2);
INSERT INTO t VALUES ('a', 2, 4, 1, 3);

/*
 * Test case query to crash the server
 * (expected result and no crash if you leave out one of the cumulated
 *  columns c2 or c3 in the subquery or if you replace the COUNT(*) in
 *  c2 and c3 by a constant; no problem and expected result, too, if
 *  you just execute the whole subquery as a 'stand-alone' query)
 */
SELECT		t.n, t.t, t.c1, cum.c2, cum.c3
FROM		t
LEFT JOIN	(	SELECT	sub.n,
				(SUM(sub.c2) / COUNT(sub.c2) * COUNT(*)) AS c2,
				(SUM(sub.c3) / COUNT(sub.c3) * COUNT(*)) AS c3
			FROM	t AS sub
			WHERE	sub.t >= 1
			GROUP BY sub.n
		) AS cum
USING		(n)
WHERE		t.t = 1;