~drizzle-trunk/drizzle/development : contents of tests/r/func_group

~drizzle-trunk/drizzle/development : (revision 1685)

create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB;
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;
count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ 
1
select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ;
Case When Count(*) < MAX_REQ Then 1 Else 0 End 
1
drop table t1;
create TEMPORARY table t1m (a int) engine=myisam;
create table t1i (a int) engine=innodb;
create TEMPORARY table t2m (a int) engine=myisam;
create table t2i (a int) engine=innodb;
insert into t2m values (5);
insert into t2i values (5);
select min(a) from t1m;
min(a)
NULL
select min(7) from t1m;
min(7)
NULL
select min(7);
min(7)
7
explain select min(7) from t2m join t1m;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
select min(7) from t2m join t1m;
min(7)
NULL
select max(a) from t1m;
max(a)
NULL
select max(7) from t1m;
max(7)
NULL
select max(7);
max(7)
7
explain select max(7) from t2m join t1m;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
select max(7) from t2m join t1m;
max(7)
NULL
select 1, min(a) from t1m where a=99;
1	min(a)
1	NULL
select 1, min(a) from t1m where 1=99;
1	min(a)
1	NULL
select 1, min(1) from t1m where a=99;
1	min(1)
1	NULL
select 1, min(1) from t1m where 1=99;
1	min(1)
1	NULL
select 1, max(a) from t1m where a=99;
1	max(a)
1	NULL
select 1, max(a) from t1m where 1=99;
1	max(a)
1	NULL
select 1, max(1) from t1m where a=99;
1	max(1)
1	NULL
select 1, max(1) from t1m where 1=99;
1	max(1)
1	NULL
select min(a) from t1i;
min(a)
NULL
select min(7) from t1i;
min(7)
NULL
select min(7);
min(7)
7
explain select min(7) from t2i join t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
select min(7) from t2i join t1i;
min(7)
NULL
select max(a) from t1i;
max(a)
NULL
select max(7) from t1i;
max(7)
NULL
select max(7);
max(7)
7
explain select max(7) from t2i join t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
select max(7) from t2i join t1i;
max(7)
NULL
select 1, min(a) from t1i where a=99;
1	min(a)
1	NULL
select 1, min(a) from t1i where 1=99;
1	min(a)
1	NULL
select 1, min(1) from t1i where a=99;
1	min(1)
1	NULL
select 1, min(1) from t1i where 1=99;
1	min(1)
1	NULL
select 1, max(a) from t1i where a=99;
1	max(a)
1	NULL
select 1, max(a) from t1i where 1=99;
1	max(a)
1	NULL
select 1, max(1) from t1i where a=99;
1	max(1)
1	NULL
select 1, max(1) from t1i where 1=99;
1	max(1)
1	NULL
explain select count(*), min(7), max(7) from t1m, t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1m	system	NULL	NULL	NULL	NULL	0	const row not found
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
select count(*), min(7), max(7) from t1m, t1i;
count(*)	min(7)	max(7)
0	NULL	NULL
explain select count(*), min(7), max(7) from t1m, t2i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1m	system	NULL	NULL	NULL	NULL	0	const row not found
1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
select count(*), min(7), max(7) from t1m, t2i;
count(*)	min(7)	max(7)
0	NULL	NULL
explain select count(*), min(7), max(7) from t2m, t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2m	system	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
select count(*), min(7), max(7) from t2m, t1i;
count(*)	min(7)	max(7)
0	NULL	NULL
drop table t1m, t1i, t2m, t2i;

1 by brian clean slate	1	create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB;
	2	insert into t1 values (1, 3);
	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;
	4	count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ
	5	1
	6	select Case When Count(*) < MAX_REQ Then 1 Else 0 End from t1 where t1.USR_ID = 1 group by MAX_REQ;
	7	Case When Count(*) < MAX_REQ Then 1 Else 0 End
	8	1
	9	drop table t1;
1063.9.3 by Brian Aker Partial fix for tests for tmp	10	create TEMPORARY table t1m (a int) engine=myisam;
1 by brian clean slate	11	create table t1i (a int) engine=innodb;
1063.9.3 by Brian Aker Partial fix for tests for tmp	12	create TEMPORARY table t2m (a int) engine=myisam;
1 by brian clean slate	13	create table t2i (a int) engine=innodb;
	14	insert into t2m values (5);
	15	insert into t2i values (5);
	16	select min(a) from t1m;
	17	min(a)
	18	NULL
	19	select min(7) from t1m;
	20	min(7)
	21	NULL
	22	select min(7);
	23	min(7)
	24	7
	25	explain select min(7) from t2m join t1m;
	26	id select_type table type possible_keys key key_len ref rows Extra
	27	1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
	28	select min(7) from t2m join t1m;
	29	min(7)
	30	NULL
	31	select max(a) from t1m;
	32	max(a)
	33	NULL
	34	select max(7) from t1m;
	35	max(7)
	36	NULL
	37	select max(7);
	38	max(7)
	39	7
	40	explain select max(7) from t2m join t1m;
	41	id select_type table type possible_keys key key_len ref rows Extra
	42	1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
	43	select max(7) from t2m join t1m;
	44	max(7)
	45	NULL
	46	select 1, min(a) from t1m where a=99;
	47	1 min(a)
	48	1 NULL
	49	select 1, min(a) from t1m where 1=99;
	50	1 min(a)
	51	1 NULL
	52	select 1, min(1) from t1m where a=99;
	53	1 min(1)
	54	1 NULL
	55	select 1, min(1) from t1m where 1=99;
	56	1 min(1)
	57	1 NULL
	58	select 1, max(a) from t1m where a=99;
	59	1 max(a)
	60	1 NULL
	61	select 1, max(a) from t1m where 1=99;
	62	1 max(a)
	63	1 NULL
	64	select 1, max(1) from t1m where a=99;
	65	1 max(1)
	66	1 NULL
	67	select 1, max(1) from t1m where 1=99;
	68	1 max(1)
	69	1 NULL
	70	select min(a) from t1i;
	71	min(a)
	72	NULL
	73	select min(7) from t1i;
	74	min(7)
	75	NULL
	76	select min(7);
77	min(7)
78	7
79	explain select min(7) from t2i join t1i;
80	id select_type table type possible_keys key key_len ref rows Extra
81	1 SIMPLE t2i ALL NULL NULL NULL NULL 1
82	1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer
83	select min(7) from t2i join t1i;
84	min(7)
85	NULL
86	select max(a) from t1i;
87	max(a)
88	NULL
89	select max(7) from t1i;
90	max(7)
91	NULL
92	select max(7);
93	max(7)
94	7
95	explain select max(7) from t2i join t1i;
96	id select_type table type possible_keys key key_len ref rows Extra
97	1 SIMPLE t2i ALL NULL NULL NULL NULL 1
98	1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer
99	select max(7) from t2i join t1i;
100	max(7)
101	NULL
102	select 1, min(a) from t1i where a=99;
103	1 min(a)
104	1 NULL
105	select 1, min(a) from t1i where 1=99;
106	1 min(a)
107	1 NULL
108	select 1, min(1) from t1i where a=99;
109	1 min(1)
110	1 NULL
111	select 1, min(1) from t1i where 1=99;
112	1 min(1)
113	1 NULL
114	select 1, max(a) from t1i where a=99;
115	1 max(a)
116	1 NULL
117	select 1, max(a) from t1i where 1=99;
118	1 max(a)
119	1 NULL
120	select 1, max(1) from t1i where a=99;
121	1 max(1)
122	1 NULL
123	select 1, max(1) from t1i where 1=99;
124	1 max(1)
125	1 NULL
126	explain select count(*), min(7), max(7) from t1m, t1i;
127	id select_type table type possible_keys key key_len ref rows Extra
128	1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
129	1 SIMPLE t1i ALL NULL NULL NULL NULL 1
130	select count(*), min(7), max(7) from t1m, t1i;
131	count(*) min(7) max(7)
132	0 NULL NULL
133	explain select count(*), min(7), max(7) from t1m, t2i;
134	id select_type table type possible_keys key key_len ref rows Extra
135	1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
136	1 SIMPLE t2i ALL NULL NULL NULL NULL 1
137	select count(*), min(7), max(7) from t1m, t2i;
138	count(*) min(7) max(7)
139	0 NULL NULL
140	explain select count(*), min(7), max(7) from t2m, t1i;
141	id select_type table type possible_keys key key_len ref rows Extra
142	1 SIMPLE t2m system NULL NULL NULL NULL 1
143	1 SIMPLE t1i ALL NULL NULL NULL NULL 1
144	select count(*), min(7), max(7) from t2m, t1i;
145	count(*) min(7) max(7)
146	0 NULL NULL
147	drop table t1m, t1i, t2m, t2i;