/*
 * Test case to MySQL bug #3505
 * Beat Vontobel, MeteoNews GmbH, 2004-04-19
 */

/*
 * Test case table preparation
 */
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t2 VALUES (1);

/*
 * Test case to produce the expected result
 */
SELECT		t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c
FROM		t1
LEFT JOIN	t2
USING		(id);

/*
 * Test case to produce the unexpected result
 */
SELECT		t1.id, ( SELECT COUNT(t.id) FROM t2 AS t WHERE t.id = t1.id ) AS c
FROM		t1
LEFT JOIN	t2
USING		(id)
ORDER BY	t1.id;

/*
 * Clean up
 */
DROP TABLE t1;
DROP TABLE t2;