func_group.test

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

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;
show create table t1;
create table t2 select max(a),min(a) from t1;
show create table t2;
drop table t2;
create table t2 select concat(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;

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

--disable_warnings
create table t1m (a int) engine=myisam;
create table t1i (a int) engine=innodb;
create table t2m (a int) engine=myisam;
create table t2i (a int) engine=innodb;
--enable_warnings
insert into t2m values (5);
insert into t2i values (5);

# test with MyISAM
select min(a) from t1m;
select min(7) from t1m;
select min(7) from DUAL;
explain select min(7) from t2m join t1m;
select min(7) from t2m join t1m;

select max(a) from t1m;
select max(7) from t1m;
select max(7) from DUAL;
explain select max(7) from t2m join t1m;
select max(7) from t2m join t1m;

select 1, min(a) from t1m where a=99;
select 1, min(a) from t1m where 1=99;
select 1, min(1) from t1m where a=99;
select 1, min(1) from t1m where 1=99;

select 1, max(a) from t1m where a=99;
select 1, max(a) from t1m where 1=99;
select 1, max(1) from t1m where a=99;
select 1, max(1) from t1m where 1=99;

# test with InnoDB
select min(a) from t1i;
select min(7) from t1i;
select min(7) from DUAL;
explain select min(7) from t2i join t1i;
select min(7) from t2i join t1i;

select max(a) from t1i;
select max(7) from t1i;
select max(7) from DUAL;
explain select max(7) from t2i join t1i;
select max(7) from t2i join t1i;

select 1, min(a) from t1i where a=99;
select 1, min(a) from t1i where 1=99;
select 1, min(1) from t1i where a=99;
select 1, min(1) from t1i where 1=99;

select 1, max(a) from t1i where a=99;
select 1, max(a) from t1i where 1=99;
select 1, max(1) from t1i where a=99;
select 1, max(1) from t1i where 1=99;

# mixed MyISAM/InnoDB test
explain select count(*), min(7), max(7) from t1m, t1i;
select count(*), min(7), max(7) from t1m, t1i;

explain select count(*), min(7), max(7) from t1m, t2i;
select count(*), min(7), max(7) from t1m, t2i;

explain select count(*), min(7), max(7) from t2m, t1i;
select count(*), min(7), max(7) from t2m, t1i;

drop table t1m, t1i, t2m, t2i;

# 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 cast(sum(distinct ff) as signed) 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 cast(sum(distinct df) as signed) from t1;
select cast(min(df) as signed) 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(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), (2,13.00);
select id, stddev_pop(value1), var_pop(value1), stddev_samp(value1), var_samp(value1) from t1 group by id;
DROP TABLE t1;

#
# BUG#8464 decimal AVG returns incorrect result
#

CREATE TABLE t1 (col1 decimal(16,12));
INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002);
insert into t1 select * from t1;
select col1,count(col1),sum(col1),avg(col1) from t1 group by col1;
DROP TABLE t1;

#
# BUG#8465 decimal MIN and MAX return incorrect result
#

create table t1 (col1 decimal(16,12));
insert into t1 values (-5.00000000001);
insert into t1 values (-5.00000000001);
select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
delete from t1;
insert into t1 values (5.00000000001);
insert into t1 values (5.00000000001);
select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
DROP TABLE t1;

#
# Test that new VARCHAR correctly works with COUNT(DISTINCT)
#

CREATE TABLE t1 (a VARCHAR(400));
INSERT INTO t1 (a) VALUES ("A"), ("a"), ("a "), ("a   "),
                          ("B"), ("b"), ("b "), ("b   ");
SELECT COUNT(DISTINCT a) FROM t1;
DROP TABLE t1;

#
# Test for buf #9210: GROUP BY with expression if a decimal type
#

CREATE TABLE t1 (a int, b int, c int);
INSERT INTO t1 (a, b, c) VALUES
  (1,1,1), (1,1,2), (1,1,3),
  (1,2,1), (1,2,2), (1,2,3),
  (1,3,1), (1,3,2), (1,3,3),
  (2,1,1), (2,1,2), (2,1,3),
  (2,2,1), (2,2,2), (2,2,3),
  (2,3,1), (2,3,2), (2,3,3),
  (3,1,1), (3,1,2), (3,1,3),
  (3,2,1), (3,2,2), (3,2,3),
  (3,3,1), (3,3,2), (3,3,3);

SELECT b/c as v, a FROM t1 ORDER BY v;
SELECT b/c as v, SUM(a) FROM t1 GROUP BY v;
SELECT SUM(a) FROM t1 GROUP BY b/c;

DROP TABLE t1;
set div_precision_increment= @sav_dpi;