diff options
Diffstat (limited to 'mysql-test/r/func_gconcat.result')
| -rw-r--r-- | mysql-test/r/func_gconcat.result | 62 |
1 files changed, 43 insertions, 19 deletions
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 09ff3fc5a6e..10afb61ba1d 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -343,18 +343,6 @@ GROUP_CONCAT(b ORDER BY b) First Row Second Row DROP TABLE t1; -CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1; -INSERT INTO t1 VALUES (1),(2),(3); -CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a), -CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1; -INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2); -SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz; -a_id b_list -1 1,2,3 -2 4,5 -3 NULL -DROP TABLE t2; -DROP TABLE t1; CREATE TABLE t1 (A_ID INT NOT NULL,A_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID)); INSERT INTO t1 VALUES (1,'ABC'), (2,'EFG'), (3,'HIJ'); CREATE TABLE t2 (A_ID INT NOT NULL,B_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID,B_DESC)); @@ -462,6 +450,49 @@ SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL; gc NULL DROP TABLE t1; +create table t2 (a int, b int); +insert into t2 values (1,1), (2,2); +select b x, (select group_concat(x) from t2) from t2; +x (select group_concat(x) from t2) +1 1,1 +2 2,2 +drop table t2; +create table t1 (d int not null auto_increment,primary key(d), a int, b int, c int); +insert into t1(a,b) values (1,3), (1,4), (1,2), (2,7), (1,1), (1,2), (2,3), (2,3); +select d,a,b from t1 order by a; +d a b +1 1 3 +2 1 4 +3 1 2 +5 1 1 +6 1 2 +4 2 7 +7 2 3 +8 2 3 +explain select a, group_concat(b) from t1 group by a with rollup; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort +select a, group_concat(b) from t1 group by a with rollup; +a group_concat(b) +1 3,4,2,1,2 +2 7,3,3 +NULL 3,4,2,1,2,7,3,3 +select a, group_concat(distinct b) from t1 group by a with rollup; +a group_concat(distinct b) +1 3,4,2,1 +2 7,3 +NULL 3,4,2,1,7 +select a, group_concat(b order by b) from t1 group by a with rollup; +a group_concat(b order by b) +1 1,2,2,3,4 +2 3,3,7 +NULL 1,2,2,3,3,3,4,7 +select a, group_concat(distinct b order by b) from t1 group by a with rollup; +a group_concat(distinct b order by b) +1 1,2,3,4 +2 3,7 +NULL 1,2,3,4,7 +drop table t1; create table t1 (a char(3), b char(20), primary key (a, b)); insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English'); select group_concat(a) from t1 group by b; @@ -469,10 +500,3 @@ group_concat(a) ABW ABW drop table t1; -create table r2 (a int, b int); -insert into r2 values (1,1), (2,2); -select b x, (select group_concat(x) from r2) from r2; -x (select group_concat(x) from r2) -1 1,1 -2 2,2 -drop table r2; |