# # simple test of all group functions # --disable_warnings drop table if exists t1,t2; --enable_warnings create table t1 (grp int, a bigint unsigned, c char(10) not null); insert into t1 values (1,1,"a"); insert into t1 values (2,2,"b"); insert into t1 values (2,3,"c"); insert into t1 values (3,4,"E"); insert into t1 values (3,5,"C"); insert into t1 values (3,6,"D"); # Test of MySQL field extension with and without matching records. select a,c,sum(a) from t1 group by a; select a,c,sum(a) from t1 where a > 10 group by a; select sum(a) from t1 where a > 10; select a from t1 order by rand(10); select distinct a from t1 order by rand(10); select count(distinct a),count(distinct grp) from t1; insert into t1 values (null,null,''); select count(distinct a),count(distinct grp) from t1; select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1; select grp, sum(a),count(a),avg(a),std(a),variance(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp; select grp, sum(a)+count(a)+avg(a)+std(a)+variance(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp; create table t2 (grp int, a bigint unsigned, c char(10)); insert into t2 select grp,max(a)+max(grp),max(c) from t1 group by grp; # REPLACE ... SELECT doesn't yet work with PS replace into t2 select grp, a, c from t1 limit 2,1; select * from t2; drop table t1,t2; # # Problem with std() # CREATE TABLE t1 (id int(11),value1 float(10,2)); INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00); CREATE TABLE t2 (id int(11),name char(20)); INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two'); select id, avg(value1), std(value1), variance(value1) from t1 group by id; select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id; drop table t1,t2; # # Test of bug in left join & avg # create table t1 (id int not null); create table t2 (id int not null,rating int null); insert into t1 values(1),(2),(3); insert into t2 values(1, 3),(2, NULL),(2, NULL),(3, 2),(3, NULL); select t1.id, avg(rating) from t1 left join t2 on ( t1.id = t2.id ) group by t1.id; drop table t1,t2; # # test of count # create table t1 (a smallint(6) primary key, c char(10), b text); INSERT INTO t1 VALUES (1,'1','1'); INSERT INTO t1 VALUES (2,'2','2'); INSERT INTO t1 VALUES (4,'4','4'); select count(*) from t1; select count(*) from t1 where a = 1; select count(*) from t1 where a = 100; select count(*) from t1 where a >= 10; select count(a) from t1 where a = 1; select count(a) from t1 where a = 100; select count(a) from t1 where a >= 10; select count(b) from t1 where b >= 2; select count(b) from t1 where b >= 10; select count(c) from t1 where c = 10; drop table t1; # # Test of bug in COUNT(i)*(i+0) # CREATE TABLE t1 (d DATETIME, i INT); INSERT INTO t1 VALUES (NOW(), 1); SELECT COUNT(i), i, COUNT(i)*i FROM t1 GROUP BY i; SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM t1 GROUP BY i; DROP TABLE t1; # # Another SUM() problem with 3.23.2 # create table t1 ( num float(5,2), user char(20) ); insert into t1 values (10.3,'nem'),(20.53,'monty'),(30.23,'sinisa'); insert into t1 values (30.13,'nem'),(20.98,'monty'),(10.45,'sinisa'); insert into t1 values (5.2,'nem'),(8.64,'monty'),(11.12,'sinisa'); select sum(num) from t1; select sum(num) from t1 group by user; drop table t1; # # Test problem with MIN() optimization in case of null values # create table t1 (a1 int, a2 char(3), key k1(a1), key k2(a2)); insert into t1 values(10,'aaa'), (10,null), (10,'bbb'), (20,'zzz'); create table t2(a1 char(3), a2 int, a3 real, key k1(a1), key k2(a2, a1)); select * from t1; # The following returned NULL in 4.0.10 select min(a2) from t1; select max(t1.a1), max(t2.a2) from t1, t2; select max(t1.a1) from t1, t2; select max(t2.a2), max(t1.a1) from t1, t2; explain select min(a2) from t1; explain select max(t1.a1), max(t2.a2) from t1, t2; insert into t2 values('AAA', 10, 0.5); insert into t2 values('BBB', 20, 1.0); select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2; select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9; select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9; select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t2 on t1.a1=10; select max(t1.a2) from t1 left outer join t2 on t1.a1=10; select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20; select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10; select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1 and 1=0 where t2.a1='AAA'; select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.a1=10; drop table t1,t2; # # Test of group function and NULL values # CREATE TABLE t1 (a int, b int); select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1; select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; insert into t1 values (1,null); select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; insert into t1 values (1,null); insert into t1 values (2,null); select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; insert into t1 values (2,1); select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; insert into t1 values (3,1); select a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a; select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; drop table t1; # # Bug #1972: test for bit_and(), bit_or() and negative values # create table t1 (col int); insert into t1 values (-1), (-2), (-3); select bit_and(col), bit_or(col) from t1; select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col; drop table t1; # # Bug #3376: avg() and an empty table # create table t1 (a int); select avg(2) from t1; drop table t1; # # Tests to check MIN/MAX query optimization # # Create database schema create table t1( a1 char(3) primary key, a2 smallint, a3 char(3), a4 real, a5 date, key k1(a2,a3), key k2(a4 desc,a1), key k3(a5,a1) ); create table t2( a1 char(3) primary key, a2 char(17), a3 char(2), a4 char(3), key k1(a3, a2), key k2(a4) ); # Populate table t1 insert into t1 values('AME',0,'SEA',0.100,date'1942-02-19'); insert into t1 values('HBR',1,'SEA',0.085,date'1948-03-05'); insert into t1 values('BOT',2,'SEA',0.085,date'1951-11-29'); insert into t1 values('BMC',3,'SEA',0.085,date'1958-09-08'); insert into t1 values('TWU',0,'LAX',0.080,date'1969-10-05'); insert into t1 values('BDL',0,'DEN',0.080,date'1960-11-27'); insert into t1 values('DTX',1,'NYC',0.080,date'1961-05-04'); insert into t1 values('PLS',1,'WDC',0.075,date'1949-01-02'); insert into t1 values('ZAJ',2,'CHI',0.075,date'1960-06-15'); insert into t1 values('VVV',2,'MIN',0.075,date'1959-06-28'); insert into t1 values('GTM',3,'DAL',0.070,date'1977-09-23'); insert into t1 values('SSJ',null,'CHI',null,date'1974-03-19'); insert into t1 values('KKK',3,'ATL',null,null); insert into t1 values('XXX',null,'MIN',null,null); insert into t1 values('WWW',1,'LED',null,null); # Populate table t2 insert into t2 values('TKF','Seattle','WA','AME'); insert into t2 values('LCC','Los Angeles','CA','TWU'); insert into t2 values('DEN','Denver','CO','BDL'); insert into t2 values('SDC','San Diego','CA','TWU'); insert into t2 values('NOL','New Orleans','LA','GTM'); insert into t2 values('LAK','Los Angeles','CA','TWU'); insert into t2 values('AAA','AAA','AA','AME'); # Show the table contents select * from t1; select * from t2; # Queries with min/max functions # which regular min/max optimization are applied to explain select min(a1) from t1; select min(a1) from t1; explain select max(a4) from t1; select max(a4) from t1; explain select min(a5), max(a5) from t1; select min(a5), max(a5) from t1; explain select min(a3) from t1 where a2 = 2; select min(a3) from t1 where a2 = 2; explain select min(a1), max(a1) from t1 where a4 = 0.080; select min(a1), max(a1) from t1 where a4 = 0.080; explain select min(t1.a5), max(t2.a3) from t1, t2; select min(t1.a5), max(t2.a3) from t1, t2; explain select min(t1.a3), max(t2.a2) from t1, t2 where t1.a2 = 0 and t2.a3 = 'CA'; select min(t1.a3), max(t2.a2) from t1, t2 where t1.a2 = 0 and t2.a3 = 'CA'; # Queries with min/max functions # which extended min/max optimization are applied to explain select min(a1) from t1 where a1 > 'KKK'; select min(a1) from t1 where a1 > 'KKK'; explain select min(a1) from t1 where a1 >= 'KKK'; select min(a1) from t1 where a1 >= 'KKK'; explain select max(a3) from t1 where a2 = 2 and a3 < 'SEA'; select max(a3) from t1 where a2 = 2 and a3 < 'SEA'; explain select max(a5) from t1 where a5 < date'1970-01-01'; select max(a5) from t1 where a5 < date'1970-01-01'; explain select max(a3) from t1 where a2 is null; select max(a3) from t1 where a2 is null; explain select max(a3) from t1 where a2 = 0 and a3 between 'K' and 'Q'; select max(a3) from t1 where a2 = 0 and a3 between 'K' and 'Q'; explain select min(a1), max(a1) from t1 where a1 between 'A' and 'P'; select min(a1), max(a1) from t1 where a1 between 'A' and 'P'; explain select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN'; select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN'; explain select max(a3) from t1 where a3 = 'MIN' and a2 = 2; select max(a3) from t1 where a3 = 'MIN' and a2 = 2; explain select max(a3) from t1 where a3 = 'DEN' and a2 = 2; select max(a3) from t1 where a3 = 'DEN' and a2 = 2; explain select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 = 'CA'; select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 = 'CA'; explain select max(a3) from t1 where a2 is null and a2 = 2; select max(a3) from t1 where a2 is null and a2 = 2; explain select max(a2) from t1 where a2 >= 1; select max(a2) from t1 where a2 >= 1; explain select min(a3) from t1 where a2 = 2 and a3 < 'SEA'; select min(a3) from t1 where a2 = 2 and a3 < 'SEA'; explain select min(a3) from t1 where a2 = 4; select min(a3) from t1 where a2 = 4; explain select min(a3) from t1 where a2 = 2 and a3 > 'SEA'; select min(a3) from t1 where a2 = 2 and a3 > 'SEA'; explain select (min(a4)+max(a4))/2 from t1; select (min(a4)+max(a4))/2 from t1; explain select min(a3) from t1 where 2 = a2; select min(a3) from t1 where 2 = a2; explain select max(a3) from t1 where a2 = 2 and 'SEA' > a3; select max(a3) from t1 where a2 = 2 and 'SEA' > a3; explain select max(a3) from t1 where a2 = 2 and 'SEA' < a3; select max(a3) from t1 where a2 = 2 and 'SEA' < a3; explain select min(a3) from t1 where a2 = 2 and a3 >= 'CHI'; select min(a3) from t1 where a2 = 2 and a3 >= 'CHI'; explain select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA'; select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA'; explain select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN'; select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN'; explain select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN'; select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN'; explain select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK'; select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK'; # Queries to which max/min optimization is not applied explain select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX'; explain select min(a1) from t1 where a1 != 'KKK'; explain select max(a3) from t1 where a2 < 2 and a3 < 'SEA'; explain select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA'; explain select min(a4 - 0.01) from t1; explain select max(a4 + 0.01) from t1; explain select min(a3) from t1 where (a2 +1 ) is null; explain select min(a3) from t1 where (a2 + 1) = 2; explain select min(a3) from t1 where 2 = (a2 + 1); explain select min(a2) from t1 where a2 < 2 * a2 - 8; explain select min(a1) from t1 where a1 between a3 and 'KKK'; explain select min(a4) from t1 where (a4 + 0.01) between 0.07 and 0.08; explain select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME'; drop table t1, t2; --disable_warnings create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB; --enable_warnings insert into t1 values (1, 3); select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ; select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ; drop table t1; create table t1 (a char(10)); insert into t1 values ('a'),('b'),('c'); select coercibility(max(a)) from t1; drop table t1; # # Bug #6658 MAX(column) returns incorrect coercibility # create table t1 (a char character set latin2); insert into t1 values ('a'),('b'); select charset(max(a)), coercibility(max(a)), charset(min(a)), coercibility(min(a)) from t1; create table t2 select max(a),min(a) from t1; show create table t2; drop table t2,t1; # # aggregate functions on static tables # create table t1 (a int); insert into t1 values (1); select max(a) as b from t1 having b=1; select a from t1 having a=1; drop table t1; # # Bug #3435: variance(const), stddev(const) and an empty table # create table t1 (a int); select variance(2) from t1; select stddev(2) from t1; drop table t1; # # cleunup() of optimized away count(*) and max/min # create table t1 (a int); insert into t1 values (1),(2); prepare stmt1 from 'SELECT COUNT(*) FROM t1'; execute stmt1; execute stmt1; execute stmt1; deallocate prepare stmt1; drop table t1; create table t1 (a int, primary key(a)); insert into t1 values (1),(2); prepare stmt1 from 'SELECT max(a) FROM t1'; execute stmt1; execute stmt1; execute stmt1; deallocate prepare stmt1; drop table t1; # # Bug #5406 min/max optimization for empty set # CREATE TABLE t1 (a int primary key); INSERT INTO t1 VALUES (1),(2),(3),(4); SELECT MAX(a) FROM t1 WHERE a > 5; SELECT MIN(a) FROM t1 WHERE a < 0; DROP TABLE t1; # # Bug #5555 GROUP BY enum_field" returns incorrect results # CREATE TABLE t1 ( id int(10) unsigned NOT NULL auto_increment, val enum('one','two','three') NOT NULL default 'one', PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO t1 VALUES (1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two'); select val, count(*) from t1 group by val; drop table t1; CREATE TABLE t1 ( id int(10) unsigned NOT NULL auto_increment, val set('one','two','three') NOT NULL default 'one', PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO t1 VALUES (1,'one'),(2,'two'),(3,'three'),(4,'one'),(5,'two'); select val, count(*) from t1 group by val; drop table t1;