/
envoy.istio
/
oceanbase
GigaCode
beta
ОбзорЦентр заботыВойти

oceanbase

Форк
0
Ветки: 12Коммиты: 12104Теги: 40
oceanbase
/tools
/deploy
/mysql_test
/t
/
func_group_1.test 
286 строк · 10.3 Кб
1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#owner: zhanyue.zzy
5
#owner group: sql1
6
#tags: optimizer, group_by
7
#description:
8


9
--enable_abort_on_error
10
## simple test of all group functions
11
##
12
#
13
--disable_warnings
14
drop table if exists t1,t2;
15
--enable_warnings
16
#
17
##set @sav_dpi= @@div_precision_increment;
18
##set div_precision_increment= 5;
19
##show variables like 'div_precision_increment';
20
##create table t1 (grp int, a bigint unsigned, c char(10) not null);
21
#--real_sleep 1
22
create table t1 (pk int primary key, grp int, a int, c char(10) not null);
23
#--real_sleep 1
24


25
insert into t1 values (1, 1,1,'a');
26
insert into t1 values (2, 2,2,'b');
27
insert into t1 values (3, 2,3,'c');
28
insert into t1 values (4, 3,4,'E');
29
insert into t1 values (5, 3,5,'C');
30
insert into t1 values (6, 3,6,'D');
31
#
32
## Test of MySQL field extension with and without matching records.
33
##### Note: The two following statements may fail if the execution plan
34
##### or optimizer is changed. The result for column c is undefined.
35
select a,c,sum(a) from t1 group by a order by a;
36
select a,c,sum(a) from t1 where a > 10 group by a order by a;
37
select sum(a) from t1 where a > 10;
38
#select a from t1 order by rand(10);
39
#select distinct a from t1 order by rand(10);
40
select count(distinct a),count(distinct grp) from t1;
41
insert into t1 values (7, null,null,'');
42
select count(distinct a),count(distinct grp) from t1;
43
#
44
#select sum(all a),count(all a),avg(all a),std(all a),variance(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1;
45
#select grp, sum(a),count(a),avg(a),std(a),variance(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp;
46
#--disable_warnings
47
#select grp, sum(a)+count(a)+avg(a)+std(a)+variance(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp;
48
#--enable_warnings
49
#
50
#--real_sleep 1
51
create table t2 (pk int primary key, grp int, a int, c char(10));
52
#--real_sleep 1
53
#insert into t2
54
select grp,max(a)+max(grp),max(c) from t1 group by grp order by grp;
55
#
56
## REPLACE ... SELECT doesn't yet work with PS
57
#replace into t2 select grp, a, c from t1 limit 2,1;
58
select * from t2;
59
#
60
drop table t1,t2;
61
#
62
##
63
## Problem with std()
64
##
65
#
66
--disable_warnings
67
drop table if exists t1;
68
--enable_warnings
69
CREATE TABLE t1 (id int ,value1 decimal(10,2),c int primary key);
70
INSERT INTO t1 VALUES (1,0.00,1),(1,1.00,2), (1,2.00,3), (2,10.00,4), (2,11.00,5), (2,12.00,6);
71
CREATE TABLE t2 (id int primary key,name char(20));
72
INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two');
73
#select id, avg(value1), std(value1), variance(value1) from t1 group by id;
74
--sorted_result
75
select id, avg(value1)  from t1 group by id;
76
#select name, avg(value1), std(value1), variance(value1) from t1, t2 where t1.id = t2.id group by t1.id;
77
--sorted_result
78
select name, avg(value1) from t1, t2 where t1.id = t2.id group by t1.id;
79
drop table t1,t2;
80
#
81
##
82
## Test of bug in left join & avg
83
##
84
#
85
#--real_sleep 1
86
create table t1 (pk int primary key, id int not null);
87
#--real_sleep 1
88
create table t2 (pk int primary key, id int not null,rating int null);
89
#--real_sleep 1
90
insert into t1 values(1,1),(2,2),(3,3);
91
insert into t2 values(1, 1, 3),(2, 2, NULL),(3, 2, NULL),(4, 3, 2),(5, 3, NULL);
92
select t1.id, avg(rating) from t1 left join t2 on ( t1.id = t2.id ) group by t1.id order by t1.id;
93
## Test different types with avg()
94
#select sql_small_result t2.id, avg(rating) from t2 group by t2.id;
95
#select sql_big_result t2.id, avg(rating) from t2 group by t2.id;
96
#select sql_small_result t2.id, avg(rating+0.0e0) from t2 group by t2.id;
97
#select sql_big_result t2.id, avg(rating+0.0e0) from t2 group by t2.id;
98
drop table t1,t2;
99
#
100
##
101
## test of count
102
##
103
#--real_sleep 1
104
create table t1 (a int primary key, c char(10), b char(128));
105
#--real_sleep 1
106
INSERT INTO t1 VALUES (1,'1','1');
107
INSERT INTO t1 VALUES (2,'2','2');
108
INSERT INTO t1 VALUES (4,'4','4');
109
#
110
select count(*) from t1;
111
select count(*) from t1 where a = 1;
112
select count(*) from t1 where a = 100;
113
select count(*) from t1 where a >= 10;
114
select count(a) from t1 where a = 1;
115
select count(a) from t1 where a = 100;
116
select count(a) from t1 where a >= 10;
117
select count(b) from t1 where b >= 2;
118
select count(b) from t1 where b >= 10;
119
select count(c) from t1 where c = 10;
120
drop table t1;
121
#
122
##
123
## Test of bug in COUNT(i)*(i+0)
124
##
125
#
126
#--real_sleep 1
127
CREATE TABLE t1 (d datetime default now(), i int primary key);
128
#--real_sleep 1
129
INSERT INTO t1(i) VALUES (1);
130
--sorted_result
131
SELECT COUNT(i), i, COUNT(i)*i FROM t1 GROUP BY i;
132
--sorted_result
133
SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM t1 GROUP BY i;
134
DROP TABLE t1;
135
#
136
##
137
## Another SUM() problem with 3.23.2
138
##
139
#
140
#--real_sleep 1
141
create table t1 (
142
        pk int primary key,
143
        num float,
144
        my_user char(20)
145
);
146
#--real_sleep 1
147
# remeber to remove comments
148
insert into t1 values (1, 10.3,'nem'),(2, 20.53,'monty'),(3, 30.23,'sinisa');
149
insert into t1 values (4, 30.13,'nem'),(5, 20.98,'monty'),(6, 10.45,'sinisa');
150
insert into t1 values (7, 5.2,'nem'),(8, 8.64,'monty'),(9, 11.12,'sinisa');
151
select sum(num) from t1;
152
select sum(num) from t1 group by my_user order by my_user;
153
drop table t1;
154
#
155
##
156
## Test problem with MIN() optimization in case of null values
157
##
158
#
159
#--real_sleep 1
160
--disable_warnings
161
drop table if exists t1,t2,t3;
162
--enable_warnings
163
create table t1 (pk int primary key, a1 int, a2 char(3));
164
#--real_sleep 1
165
insert into t1 values(1, 10,'aaa'), (2, 10,null), (3, 10,'bbb'), (4, 20,'zzz');
166
#--real_sleep 1
167
create table t2(pk int primary key, a1 char(3), a2 int, a3 float);
168
create table t3(pk int primary key, a1 char(3), a2 int, a3 float);
169
#--real_sleep 1
170
select * from t1;
171
## The following returned NULL in 4.0.10
172
select min(a2) from t1;
173


174
select max(t1.a1), max(t2.a2) from t1, t2;
175
#select max(t1.a1) from t1, t2;
176
#select max(t2.a2), max(t1.a1) from t1, t2;
177


178
#
179
--replace_regex /Plan signature: [0-9]*/Plan signature/
180
--disable_result_log
181
#explain select min(a2) from t1;
182
--enable_result_log
183
--replace_regex /Plan signature: [0-9]*/Plan signature/
184
--disable_result_log
185
#explain select max(t1.a1), max(t2.a2) from t1, t2;
186
--enable_result_log
187
#
188
insert into t2 values(1, 'AAA', 10, 0.5);
189
insert into t2 values(2, 'BBB', 20, 1.0);
190
#select t1.a1, t1.a2, t2.a1, t2.a2 from t1,t2;
191
#
192
#select max(t1.a1), max(t2.a1) from t1, t2 where t2.a2=9;
193
#select max(t2.a1), max(t1.a1) from t1, t2 where t2.a2=9;
194
#there is a bug
195
--error 1054
196
select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t3 on t1.a1=10;
197
select max(t1.a2) from t1 left outer join t2 on t1.a1=10;
198
#select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=20;
199
#select max(t2.a1) from t2 left outer join t1 on t2.a2=10 where t2.a2=10;
200
select max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and 1=0 where t2.a1='AAA';
201
--replace_regex /Plan signature: [0-9]*/Plan signature/
202
--disable_result_log
203
explain select max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and 1=0 where t2.a1='AAA';
204
--enable_result_log
205
select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1   where t2.a1='AAA';
206
--replace_regex /Plan signature: [0-9]*/Plan signature/
207
--disable_result_log
208
explain select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1  where t2.a1='AAA';
209
--enable_result_log
210
select max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk   where t2.a1='AAA' and 1=0;
211
--replace_regex /Plan signature: [0-9]*/Plan signature/
212
--disable_result_log
213
explain select max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk  where t2.a1='AAA' and 1=0;
214
--enable_result_log
215
select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and t1.a1=10;
216
--replace_regex /Plan signature: [0-9]*/Plan signature/
217
--disable_result_log
218
explain select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and t1.a1=10;
219
--enable_result_log
220
select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and t1.pk=10;
221
--replace_regex /Plan signature: [0-9]*/Plan signature/
222
--disable_result_log
223
explain select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and t1.pk=10;
224
--enable_result_log
225
select * from t1 left join t2 on t1.pk=t2.pk where t2.a2 > 1 and t2.a2 < 10;
226
--replace_regex /Plan signature: [0-9]*/Plan signature/
227
--disable_result_log
228
explain select * from t1 left join t2 on t1.pk=t2.pk where t2.a2 > 1 and t2.a2 < 10;
229
--enable_result_log
230
select * from t1 left join t2 on t1.pk=t2.pk and t2.a2 > 1 and t2.a2 < 10 order by t1.pk;
231
--replace_regex /Plan signature: [0-9]*/Plan signature/
232
--disable_result_log
233
explain select * from t1 left join t2 on t1.pk=t2.pk and t2.a2 > 1 and t2.a2 < 10;
234
--enable_result_log
235


236
drop table t1,t2;
237
#
238
##
239
## Test of group function and NULL values
240
##
241
#
242
#--real_sleep 1
243
CREATE TABLE t1 (pk int primary key, a int, b int);
244
#--real_sleep 1
245
#select count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1;
246
select count(b), sum(b), avg(b),  min(b), max(b) from t1;
247
select a,count(b), sum(b), avg(b), min(b), max(b) from t1 group by a order by a;
248
insert into t1 values (1, 1,null);
249
select a,count(b), sum(b), avg(b), min(b), max(b) from t1 group by a order by a;
250
insert into t1 values (2, 1,null);
251
insert into t1 values (3, 2,null);
252
select a,count(b), sum(b), avg(b), min(b), max(b) from t1 group by a order by a;
253
#select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
254
insert into t1 values (4, 2,1);
255
select a,count(b), sum(b), avg(b), min(b), max(b) from t1 group by a order by a;
256
#select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b) from t1 group by a;
257
insert into t1 values (5, 3,1);
258
select a,count(b), sum(b), avg(b), min(b), max(b) from t1 group by a order by a;
259
#select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a;
260
--replace_regex /Plan signature: [0-9]*/Plan signature/
261
--disable_result_log
262
#explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a;
263
--enable_result_log
264
drop table t1;
265
#
266
##
267
## Bug #1972: test for bit_and(), bit_or() and negative values
268
##
269
#--real_sleep 1
270
create table t1 (col int primary key,col2 int);
271
#--real_sleep 1
272
insert into t1(col) values (-1), (-2), (-3);
273
#select bit_and(col), bit_or(col) from t1;
274
#select SQL_BIG_RESULT bit_and(col), bit_or(col) from t1 group by col;
275
drop table t1;
276
#
277
##
278
## Bug #3376: avg() and an empty table
279
##
280
#
281
#--real_sleep 1
282
create table t1 (a int primary key,b int);
283
#--real_sleep 1
284
select avg(2) from t1;
285
drop table t1;
286
#
287

Использование cookies

Мы используем файлы cookie в соответствии с Политикой конфиденциальности и Политикой использования cookies.

Нажимая кнопку «Принимаю», Вы даете АО «СберТех» согласие на обработку Ваших персональных данных в целях совершенствования нашего веб-сайта и Сервиса GitVerse, а также повышения удобства их использования.

Запретить использование cookies Вы можете самостоятельно в настройках Вашего браузера.