1
drop table if exists t1,t2,t3;
2
SELECT 1 FROM (SELECT 1) as a GROUP BY SUM(1);
3
ERROR HY000: Invalid use of group function
11
INSERT INTO t1 VALUES (1,1,1,'',NULL);
12
INSERT INTO t1 VALUES (2,2,2,'',NULL);
13
INSERT INTO t1 VALUES (2,1,1,'',NULL);
14
INSERT INTO t1 VALUES (3,3,3,'',NULL);
16
userID int NOT NULL auto_increment,
20
isAukt enum('N','Y') DEFAULT 'N',
29
INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1');
30
INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1');
31
INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1');
32
INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1');
33
INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1');
34
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid;
39
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL;
44
SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid;
48
SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid;
49
userid MIN(t1.score+0.0)
52
SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL;
53
userid MIN(t1.score+0.0)
56
EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL;
57
id select_type table type possible_keys key key_len ref rows Extra
58
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary
59
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.userID 1
62
PID int NOT NULL auto_increment,
65
URID int DEFAULT '0' NOT NULL,
66
CRID int DEFAULT '0' NOT NULL,
67
amount int DEFAULT '0' NOT NULL,
69
method enum('unknown','cash','dealer','check','card','lazy','delayed','test') DEFAULT 'unknown' NOT NULL,
71
reason char(1) DEFAULT '' NOT NULL,
73
qty int DEFAULT '0' NOT NULL,
80
INSERT INTO t1 VALUES (1,'1970-01-01','1997-10-17 00:00:00',2529,1,21000,11886,'check',0,'F',16200,6);
81
SELECT COUNT(P.URID),SUM(P.amount),P.method, MIN(PP.recdate+0) > 19980501000000 AS IsNew FROM t1 AS P JOIN t1 as PP WHERE P.URID = PP.URID GROUP BY method,IsNew;
82
ERROR 42000: Can't group on 'IsNew'
85
cid int NOT NULL auto_increment,
86
firstname varchar(32) DEFAULT '' NOT NULL,
87
surname varchar(32) DEFAULT '' NOT NULL,
90
INSERT INTO t1 VALUES (1,'That','Guy');
91
INSERT INTO t1 VALUES (2,'Another','Gent');
93
call_id int NOT NULL auto_increment,
94
contact_id int DEFAULT '0' NOT NULL,
95
PRIMARY KEY (call_id),
96
KEY contact_id (contact_id)
98
INSERT INTO t2 VALUES (10,2);
99
INSERT INTO t2 VALUES (18,2);
100
INSERT INTO t2 VALUES (62,2);
101
INSERT INTO t2 VALUES (91,2);
102
INSERT INTO t2 VALUES (92,2);
103
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid;
104
cid CONCAT(firstname, ' ', surname) COUNT(call_id)
105
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY NULL;
106
cid CONCAT(firstname, ' ', surname) COUNT(call_id)
107
SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY surname, firstname;
108
cid CONCAT(firstname, ' ', surname) COUNT(call_id)
111
create table t1 (foo int);
112
insert into t1 values (1);
113
select 1+1, "a",count(*) from t1 where foo in (2);
116
insert into t1 values (1);
117
select 1+1,"a",count(*) from t1 where foo in (2);
128
INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3);
129
explain select userid,count(*) from t1 group by userid desc;
130
id select_type table type possible_keys key key_len ref rows Extra
131
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
132
explain select userid,count(*) from t1 group by userid desc order by null;
133
id select_type table type possible_keys key key_len ref rows Extra
134
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary
135
select userid,count(*) from t1 group by userid desc;
140
select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3);
143
select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*));
145
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
146
id select_type table type possible_keys key key_len ref rows Extra
147
1 SIMPLE t1 ALL spID NULL NULL NULL 8 Using where; Using temporary; Using filesort
148
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;
149
id select_type table type possible_keys key key_len ref rows Extra
150
1 SIMPLE t1 ALL spID NULL NULL NULL 8 Using where; Using temporary; Using filesort
151
explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null;
152
id select_type table type possible_keys key key_len ref rows Extra
153
1 SIMPLE t1 ALL spID NULL NULL NULL 8 Using where; Using temporary
154
select spid,count(*) from t1 where spid between 1 and 2 group by spid;
158
select spid,count(*) from t1 where spid between 1 and 2 group by spid desc;
162
explain extended select sql_big_result spid,sum(userid) from t1 group by spid desc;
163
id select_type table type possible_keys key key_len ref rows filtered Extra
164
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using filesort
166
Note 1003 select sql_big_result `test`.`t1`.`spID` AS `spid`,sum(`test`.`t1`.`userID`) AS `sum(userid)` from `test`.`t1` group by `test`.`t1`.`spID` desc
167
explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null;
168
id select_type table type possible_keys key key_len ref rows Extra
169
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort
170
select sql_big_result spid,sum(userid) from t1 group by spid desc;
179
explain select sql_big_result score,count(*) from t1 group by score desc;
180
id select_type table type possible_keys key key_len ref rows Extra
181
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort
182
explain select sql_big_result score,count(*) from t1 group by score desc order by null;
183
id select_type table type possible_keys key key_len ref rows Extra
184
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort
185
select sql_big_result score,count(*) from t1 group by score desc;
191
create table t1 (a date default null, b date default null);
192
insert t1 values ('1999-10-01','2000-01-10'), ('1997-01-01','1998-10-01');
193
select a,min(b) c,count(distinct rand()) from t1 group by a having c<a + interval 1 day;
194
a c count(distinct rand())
196
CREATE TABLE t1 (a char(1));
197
INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL);
198
SELECT a FROM t1 GROUP BY a;
203
SELECT a,count(*) FROM t1 GROUP BY a;
208
SELECT a FROM t1 GROUP BY a;
213
SELECT a,count(*) FROM t1 GROUP BY a;
218
SELECT a FROM t1 GROUP BY 1;
223
SELECT a,count(*) FROM t1 GROUP BY 1;
228
SELECT a FROM t1 GROUP BY a;
233
SELECT a,count(*) FROM t1 GROUP BY a;
238
SELECT a FROM t1 GROUP BY a;
243
SELECT a,count(*) FROM t1 GROUP BY a;
248
SELECT a FROM t1 GROUP BY 1;
253
SELECT a,count(*) FROM t1 GROUP BY 1;
260
`a` char(193) default NULL,
261
`b` char(63) default NULL
263
INSERT INTO t1 VALUES ('abc','def'),('hij','klm');
264
SELECT CONCAT(a, b) FROM t1 GROUP BY 1;
268
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
269
CONCAT(a, b) count(*)
272
SELECT CONCAT(a, b),count(distinct a) FROM t1 GROUP BY 1;
273
CONCAT(a, b) count(distinct a)
276
SELECT 1 FROM t1 GROUP BY CONCAT(a, b);
280
INSERT INTO t1 values ('hij','klm');
281
SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1;
282
CONCAT(a, b) count(*)
286
create table t1 (One int, Two int, Three int, Four int);
287
insert into t1 values (1,2,1,4),(1,2,2,4),(1,2,3,4),(1,2,4,4),(1,1,1,4),(1,1,2,4),(1,1,3,4),(1,1,4,4),(1,3,1,4),(1,3,2,4),(1,3,3,4),(1,3,4,4);
288
select One, Two, sum(Four) from t1 group by One,Two;
294
create table t1 (id integer primary key not null auto_increment, gender char(1));
295
insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M');
296
create table t2 (user_id integer not null, date date);
297
insert into t2 values (1, '2002-06-09'),(2, '2002-06-09'),(1, '2002-06-09'),(3, '2002-06-09'),(4, '2002-06-09'),(4, '2002-06-09');
298
select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender;
299
gender dist_count percentage
302
select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender order by percentage;
303
gender dist_count percentage
307
CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID
309
insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
310
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;
322
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;
329
CREATE TEMPORARY TABLE t1 (
330
pid int NOT NULL default '0',
331
c1id int default NULL,
332
c2id int default NULL,
333
value int NOT NULL default '0',
334
UNIQUE KEY pid2 (pid,c1id,c2id),
335
UNIQUE KEY pid (pid,value)
337
INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5);
338
CREATE TEMPORARY TABLE t2 (
339
id int NOT NULL default '0',
340
active enum('Yes','No') NOT NULL default 'Yes',
343
INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No');
345
id int NOT NULL default '0',
346
active enum('Yes','No') NOT NULL default 'Yes',
349
INSERT INTO t3 VALUES (3, 'Yes');
350
select * from t1 AS m LEFT JOIN t2 AS c1 ON m.c1id =
351
c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id = c2.id AND
352
c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS NOT NULL);
353
pid c1id c2id value id active id active
354
1 1 NULL 1 1 Yes NULL NULL
355
1 NULL 3 3 NULL NULL 3 Yes
356
1 4 NULL 4 4 Yes NULL NULL
357
select max(value) from t1 AS m LEFT JOIN t2 AS c1 ON
358
m.c1id = c1.id AND c1.active = 'Yes' LEFT JOIN t3 AS c2 ON m.c2id =
359
c2.id AND c2.active = 'Yes' WHERE m.pid=1 AND (c1.id IS NOT NULL OR c2.id IS
364
create table t1 (a blob null);
365
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(""),(""),(""),("b");
366
select a,count(*) from t1 group by a;
371
select a,count(*) from t1 group by a;
377
create table t1 (a int not null, b int not null);
378
insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1);
379
create table t2 (a int not null, b int not null, key(a));
380
insert into t2 values (1,3),(3,1),(2,2),(1,1);
381
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
387
select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
393
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b;
394
id select_type table type possible_keys key key_len ref rows Extra
395
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
396
1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer
397
explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL;
398
id select_type table type possible_keys key key_len ref rows Extra
399
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary
400
1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where; Using join buffer
402
create table t1 (a int, b int);
403
insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4);
404
select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a;
405
a MAX(b) INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000)
408
select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
409
a MAX(b) CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end
412
select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
413
a MAX(b) FIELD(MAX(b), '43', '4', '5')
416
select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;
417
a MAX(b) CONCAT_WS(MAX(b), '43', '4', '5')
420
select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;
421
a MAX(b) ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f')
424
select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;
425
a MAX(b) MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h')
429
create table t1 (id int not null, qty int not null);
430
insert into t1 values (1,2),(1,3),(2,4),(2,5);
431
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and cqty>1;
435
select id, sum(qty) as sqty from t1 group by id having sqty>2 and count(qty)>1;
439
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sqty>2 and cqty>1;
443
select id, sum(qty) as sqty, count(qty) as cqty from t1 group by id having sum(qty)>2 and count(qty)>1;
447
select count(*), case interval(qty,2,3,4,5,6,7,8) when -1 then NULL when 0 then "zero" when 1 then "one" when 2 then "two" end as category from t1 group by category;
452
select count(*), interval(qty,2,3,4,5,6,7,8) as category from t1 group by category;
464
INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(3,3),(3,3),(3,3),(3,3),(3,3);
465
SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
470
EXPLAIN SELECT userid,count(*) FROM t1 GROUP BY userid DESC;
471
id select_type table type possible_keys key key_len ref rows Extra
472
1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
478
INSERT INTO t1 VALUES (1,2),(2,3),(4,5),(3,5),(1,5),(23,5);
479
SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
484
explain SELECT i, COUNT(DISTINCT(i)) FROM t1 GROUP BY j ORDER BY NULL;
485
id select_type table type possible_keys key key_len ref rows Extra
486
1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort
488
create table t1 (a int);
489
insert into t1 values(null);
490
select min(a) is null from t1;
493
select min(a) is null or null from t1;
494
min(a) is null or null
496
select 1 and min(a) is null from t1;
500
create table t1 ( col1 int, col2 int );
501
insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
502
select group_concat( distinct col1 ) as alias from t1
503
group by col2 having alias like '%';
509
create table t1 (a integer, b integer, c integer);
510
insert into t1 (a,b) values (1,2),(1,3),(2,5);
511
select a, 0.1*0+1 r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2=1;
514
select a, round(rand(100)*10) r2, sum(1) r1 from t1 where a = 1 group by a having r1>1 and r2<=2;
517
select a,sum(b) from t1 where a=1 group by c;
520
select a*sum(b) from t1 where a=1 group by c;
523
select sum(a)*sum(b) from t1 where a=1 group by c;
526
select a,sum(b) from t1 where a=1 group by c having a=1;
529
select a as d,sum(b) from t1 where a=1 group by c having d=1;
532
select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
536
create table t1(a int);
537
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
540
b varchar(200) NOT NULL,
541
c varchar(50) NOT NULL,
542
d varchar(100) NOT NULL,
543
primary key (a,b(132),c,d),
546
insert into t2 select
548
concat('val-', x3.a + 3*x4.a), -- 12
549
concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
550
concat('val-', @a + 120*D.a)
551
from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
552
delete from t2 where a = 2 and b = 'val-2' order by a,b,c,d limit 30;
553
explain select c from t2 where a = 2 and b = 'val-2' group by c;
554
id select_type table type possible_keys key key_len ref rows Extra
555
1 SIMPLE t2 ref PRIMARY,a PRIMARY 534 const,const 1 Using where; Using temporary; Using filesort
556
select c from t2 where a = 2 and b = 'val-2' group by c;
561
create table t1 (b int4 not null);
562
insert into t1 values(300000);
566
select min(b) from t1;
570
CREATE TABLE t1 (id int PRIMARY KEY, user_id int, hostname longtext);
571
INSERT INTO t1 VALUES
572
(1, 7, 'cache-dtc-af05.proxy.aol.com'),
573
(2, 3, 'what.ever.com'),
574
(3, 7, 'cache-dtc-af05.proxy.aol.com'),
575
(4, 7, 'cache-dtc-af05.proxy.aol.com');
576
SELECT hostname, COUNT(DISTINCT user_id) as no FROM t1
577
WHERE hostname LIKE '%aol%'
580
cache-dtc-af05.proxy.aol.com 1
582
CREATE TABLE t1 (a int, b int);
583
INSERT INTO t1 VALUES (1,2), (1,3);
584
SELECT a, b FROM t1 GROUP BY 'const';
587
SELECT DISTINCT a, b FROM t1 GROUP BY 'const';
591
CREATE TABLE t1 (id INT, dt DATETIME);
592
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
593
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
594
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
595
INSERT INTO t1 VALUES ( 1, '2005-05-01 12:30:00' );
596
SELECT dt DIV 1 AS f, id FROM t1 GROUP BY f;
600
CREATE TABLE t1 (id varchar(20) NOT NULL);
601
INSERT INTO t1 VALUES ('trans1'), ('trans2');
602
CREATE TABLE t2 (id varchar(20) NOT NULL, err_comment blob NOT NULL);
603
INSERT INTO t2 VALUES ('trans1', 'a problem');
604
SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment
605
FROM t1 LEFT JOIN t2 ON t1.id=t2.id GROUP BY comment;
606
COUNT(DISTINCT(t1.id)) comment
610
create table t1 (f1 date);
611
insert into t1 values('2005-06-06');
612
insert into t1 values('2005-06-06');
613
select date(left(f1+0,8)) from t1 group by 1;
617
CREATE TABLE t1 (n int);
618
INSERT INTO t1 VALUES (1);
619
SELECT n+1 AS n FROM t1 GROUP BY n;
623
Warning 1052 Column 'n' in group statement is ambiguous
625
create table t1(f1 varchar(5) key);
626
insert into t1 values (1),(2);
627
select sql_buffer_result max(f1) is null from t1;
630
select sql_buffer_result max(f1)+1 from t1;
634
CREATE TABLE t1(a INT);
635
INSERT INTO t1 VALUES (1),(2);
636
SELECT a FROM t1 GROUP BY 'a';
639
SELECT a FROM t1 GROUP BY "a";
642
SELECT a FROM t1 GROUP BY `a`;
646
SELECT a FROM t1 GROUP BY "a";
649
SELECT a FROM t1 GROUP BY 'a';
652
SELECT a FROM t1 GROUP BY `a`;
656
SELECT a FROM t1 HAVING 'a' > 1;
659
Warning 1292 Truncated incorrect DOUBLE value: 'a'
660
SELECT a FROM t1 HAVING "a" > 1;
663
Warning 1292 Truncated incorrect DOUBLE value: 'a'
664
SELECT a FROM t1 HAVING `a` > 1;
667
SELECT a FROM t1 ORDER BY 'a' DESC;
671
SELECT a FROM t1 ORDER BY "a" DESC;
675
SELECT a FROM t1 ORDER BY `a` DESC;
681
f1 int NOT NULL auto_increment primary key,
682
f2 varchar(100) NOT NULL default ''
685
f1 varchar(10) NOT NULL default '',
686
f2 char(3) NOT NULL default '',
690
INSERT INTO t1 values(NULL, '');
691
INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
692
SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
694
SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
697
create table t1 (c1 char(3), c2 char(3));
698
create table t2 (c3 char(3), c4 char(3));
699
insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
700
insert into t2 values ('aaa', 'bb1'), ('aaa', 'bb2');
701
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
707
Warning 1052 Column 'c2' in group statement is ambiguous
710
Warning 1052 Column 'c2' in group statement is ambiguous
711
select t1.c1 as c2 from t1, t2 where t1.c2 = t2.c4
718
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
719
INSERT INTO t1 VALUES (1, 1);
720
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20) FROM t1;
721
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20) FROM t1;
722
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20) FROM t1;
723
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20) FROM t1;
724
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20) FROM t1;
725
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20) FROM t1;
726
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20) FROM t1;
727
SELECT MIN(b), MAX(b) from t1;
730
EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
731
id select_type table type possible_keys key key_len ref rows Extra
732
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
733
EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
734
id select_type table type possible_keys key key_len ref rows Extra
735
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
736
SELECT b, sum(1) FROM t1 GROUP BY b;
758
SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
781
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
782
INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
783
SELECT MAX(a)-MIN(a) FROM t1 GROUP BY b;
788
SELECT CEILING(MIN(a)) FROM t1 GROUP BY b;
793
SELECT CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END FROM t1
795
CASE WHEN AVG(a)>=0 THEN 'Positive' ELSE 'Negative' END
799
SELECT a + 1 FROM t1 GROUP BY a;
807
SELECT a + b FROM t1 GROUP BY b;
812
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
814
(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1)
821
SELECT 1 FROM t1 as t1_outer GROUP BY a
822
HAVING (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1);
830
SELECT (SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
831
FROM t1 AS t1_outer GROUP BY t1_outer.b;
832
(SELECT t1_outer.a FROM t1 AS t1_inner LIMIT 1)
836
SELECT 1 FROM t1 as t1_outer GROUP BY a
837
HAVING (SELECT t1_outer.b FROM t1 AS t1_inner LIMIT 1);
838
ERROR 42S22: Unknown column 'test.t1_outer.b' in 'field list'
839
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
840
FROM t1 AS t1_outer GROUP BY t1_outer.b;
841
(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner LIMIT 1)
845
SELECT (SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
847
(SELECT SUM(t1_inner.a) FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1)
854
SELECT (SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
855
FROM t1 AS t1_outer GROUP BY t1_outer.b;
856
(SELECT SUM(t1_outer.a) FROM t1 AS t1_inner LIMIT 1)
860
SELECT 1 FROM t1 as t1_outer
861
WHERE (SELECT t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.b LIMIT 1);
869
SELECT b FROM t1 GROUP BY b HAVING CEILING(b) > 0;
874
SELECT 1 FROM t1 GROUP BY b HAVING b = 2 OR b = 3 OR SUM(a) > 12;
878
SELECT 1 FROM t1 GROUP BY b HAVING ROW (b,b) = ROW (1,1);
881
SELECT 1 FROM t1 GROUP BY b HAVING a = 2;
882
ERROR 42S22: Unknown column 'a' in 'having clause'
883
SELECT 1 FROM t1 GROUP BY SUM(b);
884
ERROR HY000: Invalid use of group function
885
SELECT b FROM t1 AS t1_outer GROUP BY a HAVING t1_outer.a IN
886
(SELECT SUM(t1_inner.b)+t1_outer.b FROM t1 AS t1_inner GROUP BY t1_inner.a
887
HAVING SUM(t1_inner.b)+t1_outer.b > 5);
891
create table t1(f1 int, f2 int);
892
select * from t1 group by f1;
894
select * from t1 group by f2;
896
select * from t1 group by f1, f2;
898
select t1.f1,t.* from t1, t1 t group by 1;
902
id INT AUTO_INCREMENT PRIMARY KEY,
906
INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3);
907
SELECT * FROM t1 ORDER BY c1;
914
SELECT * FROM t1 GROUP BY id ORDER BY c1;
921
SELECT * FROM t1 GROUP BY id ORDER BY id DESC;
928
SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1;
935
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1;
942
SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC;
949
SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1;
954
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1;
959
SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC;
966
# Bug#27219: Aggregate functions in ORDER BY.
968
CREATE TABLE t1 (a INT, b INT, c INT DEFAULT 0);
969
INSERT INTO t1 (a, b) VALUES (3,3), (2,2), (3,3), (2,2), (3,3), (4,4);
970
CREATE TABLE t2 SELECT * FROM t1;
971
SELECT 1 FROM t1 ORDER BY COUNT(*);
974
SELECT 1 FROM t1 ORDER BY COUNT(*) + 1;
977
SELECT 1 FROM t1 ORDER BY COUNT(*) + a;
980
SELECT 1 FROM t1 ORDER BY COUNT(*), 1;
983
SELECT 1 FROM t1 ORDER BY COUNT(*), a;
986
SELECT 1 FROM t1 ORDER BY SUM(a);
989
SELECT 1 FROM t1 ORDER BY SUM(a + 1);
992
SELECT 1 FROM t1 ORDER BY SUM(a) + 1;
995
SELECT 1 FROM t1 ORDER BY SUM(a), b;
998
SELECT a FROM t1 ORDER BY COUNT(b);
1001
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2);
1009
SELECT t1.a FROM t1 ORDER BY (SELECT SUM(t2.a) FROM t2 ORDER BY t2.a);
1017
SELECT t1.a FROM t1 ORDER BY (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
1026
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.b) LIMIT 1);
1031
SELECT t1.a FROM t1 GROUP BY t1.a
1032
HAVING t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1035
SELECT t1.a FROM t1 GROUP BY t1.a
1036
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
1041
SELECT t1.a FROM t1 GROUP BY t1.a
1042
HAVING t1.a IN (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
1045
SELECT t1.a FROM t1 GROUP BY t1.a
1046
HAVING t1.a > ANY (SELECT t2.a FROM t2 ORDER BY t2.a, SUM(t2.b));
1050
WHERE t1.a = (SELECT t2.a FROM t2 ORDER BY SUM(t1.b));
1055
SELECT 1 FROM t1 GROUP BY t1.a
1056
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1061
SELECT 1 FROM t1 GROUP BY t1.a
1062
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1067
SELECT 1 FROM t1 GROUP BY t1.a
1068
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY SUM(t2.a) LIMIT 1);
1073
SELECT 1 FROM t1 GROUP BY t1.a
1074
HAVING (SELECT AVG(SUM(t1.b) + 1) FROM t2 ORDER BY t2.a LIMIT 1);
1079
SELECT 1 FROM t1 GROUP BY t1.a
1080
HAVING (SELECT AVG(SUM(t1.b) + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
1085
SELECT 1 FROM t1 GROUP BY t1.a
1086
HAVING (SELECT AVG(t1.b + t2.b) FROM t2 ORDER BY t2.a LIMIT 1);
1092
WHERE t1.a = (SELECT t2.a FROM t2 GROUP BY t2.a
1093
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1);
1096
SELECT t1.a, SUM(t1.b) FROM t1
1097
WHERE t1.a = (SELECT SUM(t2.b) FROM t2 GROUP BY t2.a
1098
ORDER BY SUM(t2.b), SUM(t1.b) LIMIT 1)
1102
SELECT t1.a, SUM(t1.b) FROM t1
1103
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
1104
ORDER BY SUM(t2.b) + SUM(t1.b) LIMIT 1)
1107
SELECT t1.a, SUM(t1.b) FROM t1
1108
WHERE t1.a = (SELECT SUM(t2.b) FROM t2
1109
ORDER BY SUM(t2.b + t1.a) LIMIT 1)
1112
SELECT t1.a FROM t1 GROUP BY t1.a
1113
HAVING (1, 1) = (SELECT SUM(t1.a), t1.a FROM t2 LIMIT 1);
1117
(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
1118
from t1 as outr order by outr.a limit 1))
1119
from t1 as most_outer;
1122
(select sum(outr.a + innr.a) from t1 as innr limit 1) as tt
1123
from t1 as outr order by outr.a limit 1))
1127
(select sum(outr.a + innr.a) from t1 as innr limit 1)) as tt
1128
from t1 as outr order by count(outr.a) limit 1)) as tt
1129
from t1 as most_outer;
1132
select (select sum(outr.a + t1.a) from t1 limit 1) as tt from t1 as outr order by outr.a;
1142
CREATE TABLE t1 (a INT, b INT,
1145
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
1146
INSERT INTO t1 SELECT a + 8,b FROM t1;
1147
INSERT INTO t1 SELECT a + 16,b FROM t1;
1148
INSERT INTO t1 SELECT a + 32,b FROM t1;
1149
INSERT INTO t1 SELECT a + 64,b FROM t1;
1150
INSERT INTO t1 SELECT a + 128,b FROM t1 limit 16;
1152
Table Op Msg_type Msg_text
1153
test.t1 analyze note The storage engine for the table doesn't support analyze
1154
EXPLAIN SELECT a FROM t1 WHERE a < 2;
1155
id select_type table type possible_keys key key_len ref rows Extra
1156
1 SIMPLE t1 ALL PRIMARY,i2 NULL NULL NULL 144 Using where
1157
EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
1158
id select_type table type possible_keys key key_len ref rows Extra
1159
1 SIMPLE t1 ALL PRIMARY,i2 NULL NULL NULL 144 Using where; Using filesort
1160
EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
1161
id select_type table type possible_keys key key_len ref rows Extra
1162
1 SIMPLE t1 ALL PRIMARY,i2 NULL NULL NULL 144 Using where; Using filesort
1163
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
1164
id select_type table type possible_keys key key_len ref rows Extra
1165
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1166
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
1167
id select_type table type possible_keys key key_len ref rows Extra
1168
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1169
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
1170
id select_type table type possible_keys key key_len ref rows Extra
1171
1 SIMPLE t1 ALL NULL NULL NULL NULL 144 Using filesort
1172
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
1173
id select_type table type possible_keys key key_len ref rows Extra
1174
1 SIMPLE t1 ALL NULL NULL NULL NULL 144 Using filesort
1175
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
1321
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
1322
IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
1323
id select_type table type possible_keys key key_len ref rows Extra
1324
1 SIMPLE t1 ALL NULL NULL NULL NULL 144 Using filesort
1325
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
1326
id select_type table type possible_keys key key_len ref rows Extra
1327
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1328
EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
1329
id select_type table type possible_keys key key_len ref rows Extra
1330
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1331
EXPLAIN SELECT a FROM t1 USE INDEX ();
1332
id select_type table type possible_keys key key_len ref rows Extra
1333
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1334
EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
1335
id select_type table type possible_keys key key_len ref rows Extra
1336
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1337
EXPLAIN SELECT a FROM t1
1338
FORCE INDEX (PRIMARY)
1339
IGNORE INDEX FOR GROUP BY (i2)
1340
IGNORE INDEX FOR ORDER BY (i2)
1342
ERROR HY000: Incorrect usage of USE INDEX and FORCE INDEX
1343
EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
1344
id select_type table type possible_keys key key_len ref rows Extra
1345
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1346
EXPLAIN SELECT a FROM t1 FORCE INDEX ();
1347
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1
1348
EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
1349
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near ')' at line 1
1350
EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2)
1351
USE INDEX FOR GROUP BY (i2) GROUP BY a;
1352
id select_type table type possible_keys key key_len ref rows Extra
1353
1 SIMPLE t1 # NULL NULL # NULL # #
1354
EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)
1355
FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
1356
id select_type table type possible_keys key key_len ref rows Extra
1357
1 SIMPLE t1 ALL NULL NULL NULL NULL 144 Using filesort
1358
EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
1359
id select_type table type possible_keys key key_len ref rows Extra
1360
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1361
EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
1362
id select_type table type possible_keys key key_len ref rows Extra
1363
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1364
EXPLAIN SELECT a FROM t1
1365
USE INDEX FOR GROUP BY (i2)
1366
USE INDEX FOR ORDER BY (i2)
1367
USE INDEX FOR JOIN (i2);
1368
id select_type table type possible_keys key key_len ref rows Extra
1369
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1370
EXPLAIN SELECT a FROM t1
1371
USE INDEX FOR JOIN (i2)
1372
USE INDEX FOR JOIN (i2)
1373
USE INDEX FOR JOIN (i2,i2);
1374
id select_type table type possible_keys key key_len ref rows Extra
1375
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
1376
EXPLAIN SELECT 1 FROM t1 WHERE a IN
1377
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
1378
id select_type table type possible_keys key key_len ref rows Extra
1379
1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where
1380
2 SUBQUERY t1 ALL NULL NULL NULL NULL 144
1381
CREATE TABLE t2 (a INT, b INT, KEY(a));
1382
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
1383
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
1384
id select_type table type possible_keys key key_len ref rows Extra
1385
1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
1386
EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
1387
id select_type table type possible_keys key key_len ref rows Extra
1388
1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
1389
EXPLAIN SELECT 1 FROM t2 WHERE a IN
1390
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
1391
id select_type table type possible_keys key key_len ref rows Extra
1392
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where
1393
2 SUBQUERY t1 ALL NULL NULL NULL NULL 144
1402
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
1408
INSERT INTO t2 VALUES (NULL, NULL), (NULL, NULL), (NULL, 1), (1, NULL), (1, 1), (1,2);
1409
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
1410
id select_type table type possible_keys key key_len ref rows Extra
1411
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1412
SELECT c,b,d FROM t1 GROUP BY c,b,d;
1417
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1418
id select_type table type possible_keys key key_len ref rows Extra
1419
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
1420
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1425
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
1426
id select_type table type possible_keys key key_len ref rows Extra
1427
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1428
SELECT c,b,d FROM t1 ORDER BY c,b,d;
1433
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
1434
id select_type table type possible_keys key key_len ref rows Extra
1435
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1436
SELECT c,b,d FROM t1 GROUP BY c,b;
1441
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
1442
id select_type table type possible_keys key key_len ref rows Extra
1443
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1444
SELECT c,b FROM t1 GROUP BY c,b;
1449
EXPLAIN SELECT a,b from t2 ORDER BY a,b;
1450
id select_type table type possible_keys key key_len ref rows Extra
1451
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using filesort
1452
SELECT a,b from t2 ORDER BY a,b;
1460
EXPLAIN SELECT a,b from t2 GROUP BY a,b;
1461
id select_type table type possible_keys key key_len ref rows Extra
1462
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
1463
SELECT a,b from t2 GROUP BY a,b;
1470
EXPLAIN SELECT a from t2 GROUP BY a;
1471
id select_type table type possible_keys key key_len ref rows Extra
1472
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
1473
SELECT a from t2 GROUP BY a;
1477
EXPLAIN SELECT b from t2 GROUP BY b;
1478
id select_type table type possible_keys key key_len ref rows Extra
1479
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
1480
SELECT b from t2 GROUP BY b;
1486
CREATE TABLE t1 ( a INT, b INT );
1487
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1489
c (SELECT a FROM t1 WHERE b = c)
1490
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1493
c (SELECT a FROM t1 WHERE b = c)
1494
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1497
ERROR 42S22: Reference 'c' not supported (reference to group function)
1498
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1500
c (SELECT a FROM t1 WHERE b = c)
1501
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1504
c (SELECT a FROM t1 WHERE b = c)
1505
SELECT MAX(b) c, (SELECT a FROM t1 WHERE b = c)
1508
ERROR 42S22: Reference 'c' not supported (reference to group function)
1509
INSERT INTO t1 VALUES (1, 1);
1510
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1512
c (SELECT a FROM t1 WHERE b = c)
1514
INSERT INTO t1 VALUES (2, 1);
1515
SELECT b c, (SELECT a FROM t1 WHERE b = c)
1517
ERROR 21000: Subquery returns more than 1 row
added
removed
tests/r/pbxt/group_by.result
