diff options
Diffstat (limited to 'mysql-test/t/group_by.test')
| -rw-r--r-- | mysql-test/t/group_by.test | 41 |
1 files changed, 40 insertions, 1 deletions
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 072a1830f57..2f2f50c4085 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -2,7 +2,7 @@ # Test of group (Failed for Lars Hoss <lh@pbm.de>) # -drop table if exists t1,t2; +drop table if exists t1,t2,t3; CREATE TABLE t1 ( spID int(10) unsigned, userID int(10) unsigned, @@ -312,3 +312,42 @@ insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL); select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2; select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1; drop table t1; + +# +# Problem with MAX and LEFT JOIN +# + +CREATE TABLE t1 ( + pid int(11) unsigned NOT NULL default '0', + c1id int(11) unsigned default NULL, + c2id int(11) unsigned default NULL, + value int(11) unsigned NOT NULL default '0', + UNIQUE KEY pid2 (pid,c1id,c2id), + UNIQUE KEY pid (pid,value) +) TYPE=MyISAM; + +INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5); + +CREATE TABLE t2 ( + id int(11) unsigned NOT NULL default '0', + active enum('Yes','No') NOT NULL default 'Yes', + PRIMARY KEY (id) +) TYPE=MyISAM; + +INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No'); + +CREATE TABLE t3 ( + id int(11) unsigned NOT NULL default '0', + active enum('Yes','No') NOT NULL default 'Yes', + PRIMARY KEY (id) +); +INSERT INTO t3 VALUES (3, 'Yes'); + +select * from t1 AS m LEFT JOIN t2 AS c1 ON m.c1id = +c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = c2.id AND +c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS NOT NULL); +select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON +m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = +c2.id AND c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS +NOT NULL); +drop table t1,t2,t3; |