~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
#
2
# Test of group functions that depend on innodb
3
#
4
5
--source include/have_innodb.inc
6
7
--disable_warnings
8
create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB;
9
--enable_warnings
10
insert into t1 values (1, 3);
11
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;
12
select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ;
13
drop table t1;
14
15
16
#
17
# Bug #12882  	min/max inconsistent on empty table
18
#
19
20
--disable_warnings
21
create table t1m (a int) engine=myisam;
22
create table t1i (a int) engine=innodb;
23
create table t2m (a int) engine=myisam;
24
create table t2i (a int) engine=innodb;
25
--enable_warnings
26
insert into t2m values (5);
27
insert into t2i values (5);
28
29
# test with MyISAM
30
select min(a) from t1m;
31
select min(7) from t1m;
32
select min(7);
33
explain select min(7) from t2m join t1m;
34
select min(7) from t2m join t1m;
35
36
select max(a) from t1m;
37
select max(7) from t1m;
38
select max(7);
39
explain select max(7) from t2m join t1m;
40
select max(7) from t2m join t1m;
41
42
select 1, min(a) from t1m where a=99;
43
select 1, min(a) from t1m where 1=99;
44
select 1, min(1) from t1m where a=99;
45
select 1, min(1) from t1m where 1=99;
46
47
select 1, max(a) from t1m where a=99;
48
select 1, max(a) from t1m where 1=99;
49
select 1, max(1) from t1m where a=99;
50
select 1, max(1) from t1m where 1=99;
51
52
# test with InnoDB
53
select min(a) from t1i;
54
select min(7) from t1i;
55
select min(7);
56
explain select min(7) from t2i join t1i;
57
select min(7) from t2i join t1i;
58
59
select max(a) from t1i;
60
select max(7) from t1i;
61
select max(7);
62
explain select max(7) from t2i join t1i;
63
select max(7) from t2i join t1i;
64
65
select 1, min(a) from t1i where a=99;
66
select 1, min(a) from t1i where 1=99;
67
select 1, min(1) from t1i where a=99;
68
select 1, min(1) from t1i where 1=99;
69
70
select 1, max(a) from t1i where a=99;
71
select 1, max(a) from t1i where 1=99;
72
select 1, max(1) from t1i where a=99;
73
select 1, max(1) from t1i where 1=99;
74
75
# mixed MyISAM/InnoDB test
76
explain select count(*), min(7), max(7) from t1m, t1i;
77
select count(*), min(7), max(7) from t1m, t1i;
78
79
explain select count(*), min(7), max(7) from t1m, t2i;
80
select count(*), min(7), max(7) from t1m, t2i;
81
82
explain select count(*), min(7), max(7) from t2m, t1i;
83
select count(*), min(7), max(7) from t2m, t1i;
84
85
drop table t1m, t1i, t2m, t2i;