# Testcase derived from select.test
#

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
--enable_warnings

if (1)
{
# Original testcase
CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL, UNIQUE idx (a,b) );
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4);
CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL, e INT );
INSERT INTO t2 VALUES ( 1,10,1), (1,10,2), (1,11,1), (1,11,2), (1,2,1), (1,2,2),(1,2,3);

--echo # Where is the record with t2.b = 11 ?
--echo # Count(*) for the record with t2.b = 10 must be 2.
SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c, COUNT(*) AS d FROM t2 LEFT JOIN
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b, c;

CREATE TABLE t3 AS SELECT t2.a, t2.b, IF(t1.b IS NULL,'',e) AS c FROM t2 LEFT JOIN
t1 ON t2.a = t1.a AND t2.b = t1.b;
SELECT a,b,c FROM t3;
SELECT a,b,c,COUNT(*) AS d FROM t3 GROUP BY a, b, c;

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
}

# Extreme simplified testcase
CREATE TABLE t1 ( a INT NOT NULL, b INT NOT NULL);

INSERT INTO t1 VALUES (1,2);
CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL );
INSERT INTO t2 VALUES ( 1,10), (1,11), (1,2);

--echo # Where is the record with t2.b = 11 ?
SELECT t2.a, t2.b AS d FROM t2 LEFT JOIN
t1 ON t2.a = t1.a AND t2.b = t1.b GROUP BY t1.a, t1.b;

--echo Comparable testcase using two statements:
CREATE TABLE t3 AS SELECT t2.a, t2.b FROM t2 LEFT JOIN
t1 ON t2.a = t1.a AND t2.b = t1.b;
SELECT a,b FROM t3;
SELECT a,b AS d FROM t3 GROUP BY a, b;

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;