~drizzle-trunk/drizzle/development

#

# simple test of all group functions

#

--disable_warnings

drop table if exists t1,t2;

--enable_warnings

set @sav_dpi= @@div_precision_increment;

set div_precision_increment= 5;

show variables like 'div_precision_increment';

create table t1 (grp int, a bigint, 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;

create table t2 (grp int, a bigint, 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,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,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;

# Test different types with avg()

select sql_small_result t2.id, avg(rating) from t2 group by t2.id;

select sql_big_result t2.id, avg(rating) from t2 group by t2.id;

select sql_small_result t2.id, avg(rating+0.0e0) from t2 group by t2.id;

select sql_big_result t2.id, avg(rating+0.0e0) from t2 group by t2.id;

drop table t1,t2;

#

# test of count

#

create table t1 (a int 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 where t1.a1 > 0;

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);

--sorted_result

select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2 where t1.a1 > 0;

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;

#

# 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 int,

  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

--sorted_result

select * from t1;

--sorted_result

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;

# Moved to func_group_innodb

#--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;

#

# Bug #6658 MAX(column) returns incorrect coercibility

#

create table t1 (a char);

insert into t1 values ('a'),('b');

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t1;

create table t2 select max(a),min(a) from t1;

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t2;

drop table t2;

create table t2 select concat(a) from t1;

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

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);

SELECT COUNT(*) FROM t1;

SELECT COUNT(*) FROM t1;

SELECT COUNT(*) FROM t1;

drop table t1;

create table t1 (a int, primary key(a));

insert into t1 values (1),(2);

SELECT max(a) FROM t1;

SELECT max(a) FROM t1;

SELECT max(a) FROM t1;

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 TEMPORARY TABLE t1 (

  id int NOT NULL auto_increment,

  val enum('one','two','three') NOT NULL default 'one',

  PRIMARY KEY  (id)

) ENGINE=MyISAM;

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;

#

# Bug #5615: type of aggregate function column wrong when using group by

#

create table t1(a int, b datetime);

insert into t1 values (1, NOW()), (2, NOW());

create table t2 select MAX(b) from t1 group by a;

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t2;

drop table t1, t2;

#

# Bug 7833:  Wrong datatype of aggregate column is returned

#

create table t1(f1 datetime);

insert into t1 values (now());

create table t2 select f2 from (select max(now()) f2 from t1) a;

show columns from t2;

drop table t2;

create table t2 select f2 from (select now() f2 from t1) a;

show columns from t2;

drop table t2, t1;

#

# Bug 8893: wrong result for min/max optimization with 2 indexes

#

CREATE TABLE t1(

  id int PRIMARY KEY,

  a  int,

  b  int,

  INDEX i_b_id(a,b,id),

  INDEX i_id(a,id)

);

INSERT INTO t1 VALUES 

  (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);

SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;

DROP TABLE t1;

# change the order of the last two index definitions

CREATE TABLE t1(

  id int PRIMARY KEY,

  a  int,

  b  int,

  INDEX i_id(a,id),

  INDEX i_b_id(a,b,id)

);

INSERT INTO t1 VALUES 

  (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);

SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;

DROP TABLE t1;

#

# Bug #12882  	min/max inconsistent on empty table

#

# Test case moved to func_group_innodb

#

# Bug #18206: min/max optimization cannot be applied to partial index

#

CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b));

INSERT INTO t1 VALUES (1,'xx'), (2,'aa');

SELECT * FROM t1;

SELECT MAX(b) FROM t1 WHERE b < 'ppppp';

SHOW WARNINGS;

SELECT MAX(b) FROM t1 WHERE b < 'pp';

DROP TABLE t1;

CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4)));

INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa');

SELECT MAX(b) FROM t1;

EXPLAIN SELECT MAX(b) FROM t1;

DROP TABLE t1;

#

# Bug #16792  query with subselect, join, and group not returning proper values

#

CREATE TABLE t1 (a INT, b INT);

INSERT INTO t1 VALUES (1,1),(1,2),(2,3);

SELECT (SELECT COUNT(DISTINCT t1.b)) FROM t1 GROUP BY t1.a;

SELECT (SELECT COUNT(DISTINCT 12)) FROM t1 GROUP BY t1.a;

# an attempt to test all aggregate function with no table.

SELECT AVG(2), COUNT(*), COUNT(12),

       COUNT(DISTINCT 12), MIN(2),MAX(2),STD(2), VARIANCE(2),SUM(2),

       GROUP_CONCAT(2),GROUP_CONCAT(DISTINCT 2);

DROP TABLE t1;

# End of 4.1 tests

#

# decimal-related tests

#

create table t2 (ff double);

insert into t2 values (2.2);

select cast(sum(distinct ff) as decimal(5,2)) from t2;

select sum(distinct ff) from t2;

select cast(variance(ff) as decimal(10,3)) from t2;

select cast(min(ff) as decimal(5,2)) from t2;

create table t1 (df decimal(5,1));

insert into t1 values(1.1);

insert into t1 values(2.2);

select sum(distinct df) from t1;

select min(df) from t1;

select 1e8 * sum(distinct df) from t1;

select 1e8 * min(df) from t1;

create table t3 (ifl int);

insert into t3 values(1), (2);

select cast(min(ifl) as decimal(5,2)) from t3;

drop table t1, t2, t3;

#

# BUG#3190, WL#1639: Standard Deviation STDDEV - 2 different calculations

#

CREATE TABLE t1 (id int,value1 float(10,2));