diff options
Diffstat (limited to 'mysql-test/r/group_by.result')
| -rw-r--r-- | mysql-test/r/group_by.result | 41 |
1 files changed, 38 insertions, 3 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 449d7bcb818..86a8eb2dd22 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1,4 +1,4 @@ -drop table if exists t1,t2; +drop table if exists t1,t2,t3; CREATE TABLE t1 ( spID int(10) unsigned, userID int(10) unsigned, @@ -234,10 +234,10 @@ userid count(*) 1 2 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range spID spID 5 NULL 2 where used; Using index +1 SIMPLE t1 range spID spID 5 NULL 2 Using where; Using index explain select spid,count(*) from t1 where spid between 1 and 2 group by spid; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range spID spID 5 NULL 2 where used; Using index +1 SIMPLE t1 range spID spID 5 NULL 2 Using where; Using index select spid,count(*) from t1 where spid between 1 and 2 group by spid; spid count(*) 1 1 @@ -417,3 +417,38 @@ xID xID1 Level 3 134 *** 4 185 **** drop table t1; +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); +pid c1id c2id value id active id active +1 1 NULL 1 1 Yes NULL NULL +1 NULL 3 3 NULL NULL 3 Yes +1 4 NULL 4 4 Yes NULL 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); +max(value) +4 +drop table t1,t2,t3; |