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