oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#owner: jiangxiu.wt
5
#owner group: sql1
6

7
##
8
## simple test of all group functions
9
##
10
#
11
--disable_warnings
12
drop table if exists t1,t2,t3,t4,t5;
13
--enable_warnings
14

15
#
16
##
17
## Bug #10966: Variance functions return wrong data type
18
##
19
#
20
#create table t1 select variance(0);                                               
21
#show create table t1;                                                           
22
#drop table t1;                                                                  
23
#create table t1 select stddev(0);
24
#show create table t1;
25
#drop table t1;
26
# 
27
#
28
##
29
## Bug#22555: STDDEV yields positive result for groups with only one row
30
##
31
#
32
#create table bug22555 (i smallint primary key auto_increment, s1 smallint, s2 smallint, e decimal(30,10), o double);
33
#insert into bug22555 (s1, s2, e, o) values (53, 78, 11.4276528, 6.828112), (17, 78, 5.916793, 1.8502951), (18, 76, 2.679231, 9.17975591), (31, 62, 6.07831, 0.1), (19, 41, 5.37463, 15.1), (83, 73, 14.567426, 7.959222), (92, 53, 6.10151, 13.1856852), (7, 12, 13.92272, 3.442007), (92, 35, 11.95358909, 6.01376678), (38, 84, 2.572, 7.904571);
34
#select std(s1/s2) from bug22555 group by i;
35
#select std(e) from bug22555 group by i;
36
#select std(o) from bug22555 group by i;
37
#drop table bug22555;
38
#
39
#create table bug22555 (i smallint, s1 smallint, s2 smallint, o1 double, o2 double, e1 decimal, e2 decimal);
40
#insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
41
#select i, count(*) from bug22555 group by i;
42
#select std(s1/s2) from bug22555 where i=1;
43
#select std(s1/s2) from bug22555 where i=2;
44
#select std(s1/s2) from bug22555 where i=3;
45
#select std(s1/s2) from bug22555 where i=1 group by i;
46
#select std(s1/s2) from bug22555 where i=2 group by i;
47
#select std(s1/s2) from bug22555 where i=3 group by i;
48
#select std(s1/s2) from bug22555 group by i order by i;
49
#select i, count(*), std(o1/o2) from bug22555 group by i order by i;
50
#select i, count(*), std(e1/e2) from bug22555 group by i order by i;
51
#set @saved_div_precision_increment=@@div_precision_increment;
52
#set div_precision_increment=19;
53
#select i, count(*), variance(s1/s2) from bug22555 group by i order by i;
54
#select i, count(*), variance(o1/o2) from bug22555 group by i order by i;
55
#select i, count(*), variance(e1/e2) from bug22555 group by i order by i;
56
#select i, count(*), std(s1/s2) from bug22555 group by i order by i;
57
#select i, count(*), std(o1/o2) from bug22555 group by i order by i;
58
#select i, count(*), std(e1/e2) from bug22555 group by i order by i;
59
#set div_precision_increment=20;
60
#select i, count(*), variance(s1/s2) from bug22555 group by i order by i;
61
#select i, count(*), variance(o1/o2) from bug22555 group by i order by i;
62
#select i, count(*), variance(e1/e2) from bug22555 group by i order by i;
63
#select i, count(*), std(s1/s2) from bug22555 group by i order by i;
64
#select i, count(*), std(o1/o2) from bug22555 group by i order by i;
65
#select i, count(*), std(e1/e2) from bug22555 group by i order by i;
66
#set @@div_precision_increment=@saved_div_precision_increment;
67
#insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
68
#insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
69
#insert into bug22555 values (1,53,78,53,78,53,78),(2,17,78,17,78,17,78),(3,18,76,18,76,18,76);
70
#
71
#select i, count(*), std(s1/s2) from bug22555 group by i order by i;
72
#select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
73
#select i, count(*), std(e1/e2) from bug22555 group by i order by i;
74
#select std(s1/s2) from bug22555;
75
#select std(o1/o2) from bug22555;
76
#select std(e1/e2) from bug22555;
77
#set @saved_div_precision_increment=@@div_precision_increment;
78
#set div_precision_increment=19;
79
#select i, count(*), std(s1/s2) from bug22555 group by i order by i;
80
#select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
81
#select i, count(*), std(e1/e2) from bug22555 group by i order by i;
82
#select round(std(s1/s2), 17) from bug22555;
83
#select std(o1/o2) from bug22555;
84
#select round(std(e1/e2), 17) from bug22555;
85
#set div_precision_increment=20;
86
#select i, count(*), std(s1/s2) from bug22555 group by i order by i;
87
#select i, count(*), round(std(o1/o2), 16) from bug22555 group by i order by i;
88
#select i, count(*), std(e1/e2) from bug22555 group by i order by i;
89
#select round(std(s1/s2), 17) from bug22555;
90
#select std(o1/o2) from bug22555;
91
#select round(std(e1/e2), 17) from bug22555;
92
#set @@div_precision_increment=@saved_div_precision_increment;
93
#drop table bug22555;
94
#
95
#create table bug22555 (s smallint, o double, e decimal);
96
#insert into bug22555 values (1,1,1),(2,2,2),(3,3,3),(6,6,6),(7,7,7);
97
#select var_samp(s), var_pop(s) from bug22555;
98
#select var_samp(o), var_pop(o) from bug22555;
99
#select var_samp(e), var_pop(e) from bug22555;
100
#drop table bug22555;
101
#
102
#create table bug22555 (s smallint, o double, e decimal);
103
#insert into bug22555 values (null,null,null),(null,null,null);
104
#select var_samp(s) as 'null', var_pop(s) as 'null' from bug22555;
105
#select var_samp(o) as 'null', var_pop(o) as 'null' from bug22555;
106
#select var_samp(e) as 'null', var_pop(e) as 'null' from bug22555;
107
#insert into bug22555 values (1,1,1);
108
#select var_samp(s) as 'null', var_pop(s) as '0' from bug22555;
109
#select var_samp(o) as 'null', var_pop(o) as '0' from bug22555;
110
#select var_samp(e) as 'null', var_pop(e) as '0' from bug22555;
111
#insert into bug22555 values (2,2,2);
112
#select var_samp(s) as '0.5', var_pop(s) as '0.25' from bug22555;
113
#select var_samp(o) as '0.5', var_pop(o) as '0.25' from bug22555;
114
#select var_samp(e) as '0.5', var_pop(e) as '0.25' from bug22555;
115
#drop table bug22555;
116
#
117
#
118
##
119
## Bug #21976: Unnecessary warning with count(decimal)
120
##
121

122
create table t1 (pk int primary key,a double ,b double);
123
insert into t1(pk,a) values (1,12345678901234567890);
124
select count(a) from t1;
125
select count(distinct a) from t1;
126
drop table t1;
127
#
128
##
129
## Bug #23184: SELECT causes server crash
130
## 
131
CREATE TABLE t1 (a INT, b INT primary key);
132
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
133
#INSERT INTO t1 SELECT a, b+8       FROM t1;
134
#INSERT INTO t1 SELECT a, b+16      FROM t1;
135
#INSERT INTO t1 SELECT a, b+32      FROM t1;
136
#INSERT INTO t1 SELECT a, b+64      FROM t1;
137
#INSERT INTO t1 SELECT a, b+128     FROM t1;
138
#INSERT INTO t1 SELECT a, b+256     FROM t1;
139
#INSERT INTO t1 SELECT a, b+512     FROM t1;
140
#INSERT INTO t1 SELECT a, b+1024    FROM t1;
141
#INSERT INTO t1 SELECT a, b+2048    FROM t1;
142
#INSERT INTO t1 SELECT a, b+4096    FROM t1;
143
#INSERT INTO t1 SELECT a, b+8192    FROM t1;
144
#INSERT INTO t1 SELECT a, b+16384   FROM t1;
145
#INSERT INTO t1 SELECT a, b+32768   FROM t1;
146
SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50;
147
SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50;
148
SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
149
#
150
DROP TABLE t1;
151
#
152
##
153
## Bug #27573: MIN() on an indexed column which is always NULL sets _other_ 
154
## results to NULL
155
##
156
CREATE TABLE t1 ( a INT, b INT primary key);
157
INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
158
--replace_regex /Plan signature: [0-9]*/Plan signature/
159
--disable_result_log
160
EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
161
--enable_result_log
162
SELECT MIN(a), MIN(b) FROM t1;
163
#
164
CREATE TABLE t2( pk int primary key, a INT, b INT, c INT);
165
INSERT INTO t2 ( pk,a, b, c ) VALUES ( 1,1, NULL, 2 ), ( 2,1, 3, 4 ), ( 3,1, 4, 4 );
166
--replace_regex /Plan signature: [0-9]*/Plan signature/
167
--disable_result_log
168
EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
169
--enable_result_log
170
SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
171
#
172
CREATE TABLE t3 (pk int primary key , a INT, b INT, c int);
173
INSERT INTO t3 VALUES (1,1, NULL, 1), (2,2, NULL, 2),  (3,3, NULL, 3);
174
--replace_regex /Plan signature: [0-9]*/Plan signature/
175
--disable_result_log
176
EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
177
--enable_result_log
178
SELECT MIN(a), MIN(b) FROM t3 where a = 2;
179
#
180
CREATE TABLE t4 (a INT, b INT, c int, primary KEY(a, c));
181
INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (3, 1, 3);
182
--replace_regex /Plan signature: [0-9]*/Plan signature/
183
--disable_result_log
184
EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
185
--enable_result_log
186
SELECT MIN(a), MIN(b) FROM t4 where a = 2;
187
SELECT MIN(b), min(c) FROM t4 where a = 2;
188
#
189
CREATE TABLE t5( a INT, b INT, primary KEY( a, b),c int); 
190
INSERT INTO t5(a,b) VALUES( 1, 1 ), ( 1, 2 );
191
--replace_regex /Plan signature: [0-9]*/Plan signature/
192
--disable_result_log
193
EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
194
--enable_result_log
195
SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
196
SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
197
#
198
DROP TABLE t1, t2, t3, t4, t5;
199
#DROP TABLE t1, t2;
200
#DROP TABLE t3;
201
#drop table t4, t5;
202
#
203
##
204
## Bug #31156: mysqld: item_sum.cc:918: 
205
##  virtual bool Item_sum_distinct::setup(THD*): Assertion
206
##
207
#
208
#CREATE TABLE t1 (a INT);
209
#INSERT INTO t1 values (),(),();
210
#SELECT (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ) as x FROM t1 
211
#  GROUP BY x;
212
#SELECT 1 FROM t1 GROUP BY (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) );
213
#
214
#DROP TABLE t1;
215
#
216
##
217
## Bug #30715: Assertion failed: item_field->field->real_maybe_null(), file
218
## .\opt_sum.cc, line
219
##
220
#
221
CREATE TABLE t1 (a int, b datetime(6), primary key (a,b),c int);
222
SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01';
223
DROP TABLE t1;
224
#
225
##
226
## Bug #31794: no syntax error on SELECT id FROM t HAVING count(*)>2;
227
##
228
#
229
CREATE TABLE t1 (a INT primary key, b int);
230
INSERT INTO t1(a) VALUES (1),(2),(3),(4);
231
#
232
#SET SQL_MODE=ONLY_FULL_GROUP_BY;
233
#--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
234
SELECT a FROM t1 HAVING COUNT(*)>2;
235
#--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
236
SELECT COUNT(*), a FROM t1;
237
#
238
#SET SQL_MODE=DEFAULT;
239
SELECT a FROM t1 HAVING COUNT(*)>2;
240
SELECT COUNT(*), a FROM t1;
241
#
242
DROP TABLE t1;
243
#
244
##
245
## Bug #33133: Views are not transparent
246
##
247
#
248
#set SQL_MODE=ONLY_FULL_GROUP_BY;
249
#
250
#CREATE TABLE t1 (a INT);
251
#INSERT INTO t1 VALUES (1),(2),(3),(4);
252
#CREATE VIEW v1 AS SELECT a,(a + 1) AS y FROM t1;
253
--disable_result_log
254
#EXPLAIN EXTENDED SELECT y FROM v1 GROUP BY v1.y;
255
--enable_result_log
256
#
257
#DROP VIEW v1;
258
#DROP TABLE t1;
259
#SET SQL_MODE=DEFAULT;
260
#
261
##
262
## Bug #34512: CAST( AVG( double ) AS DECIMAL ) returns wrong results
263
##
264
#
265
CREATE TABLE t1(pk int primary key,a DOUBLE );
266
INSERT INTO t1 VALUES (1,10), (2,20);
267
SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1;
268
#
269
DROP TABLE t1;
270
#
271
##
272
## Bug #39656: Behaviour different for agg functions with & without where -
273
## ONLY_FULL_GROUP_BY
274
##
275
#
276
CREATE TABLE t1 (a INT, b INT primary key);
277
INSERT INTO t1 VALUES (1,1), (1,2), (1,3);
278
#
279
#SET SQL_MODE='ONLY_FULL_GROUP_BY';
280
#
281
SELECT COUNT(*) FROM t1;
282
SELECT COUNT(*) FROM t1 where a=1;
283
#
284
#--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
285
SELECT COUNT(*),a FROM t1;
286
#
287
SELECT COUNT(*) FROM t1 a JOIN t1 b ON a.a= b.a;
288
#
289
#--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS
290
#SELECT COUNT(*), (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a) 
291
 #FROM t1 outr;
292
#
293
#SELECT COUNT(*) FROM t1 a JOIN t1 outr 
294
#  ON a.a= (SELECT count(*) FROM t1 inr WHERE inr.a = outr.a);
295
#
296
#SET SQL_MODE=default;
297
DROP TABLE t1;
298
#
299
#
300
####
301
#--echo End of 5.0 tests
302
#
303
#--echo #
304
#--echo # BUG#47280 - strange results from count(*) with order by multiple 
305
#--echo #             columns without where/group
306
#--echo # 
307
#
308
#--echo # 
309
#--echo # Initialize test
310
#--echo # 
311
#
312
CREATE TABLE t1 (
313
  pk INT NOT NULL,
314
  i INT,
315
  PRIMARY KEY (pk)
316
);
317
INSERT INTO t1 VALUES (1,11),(2,12),(3,13);
318
#
319
#--echo #
320
#--echo # Start test
321
#--echo # All the following queries shall return 1 record
322
#--echo #
323
#
324
#--echo
325
#--echo # Masking all correct values {11...13} for column i in this result. 
326
#--replace_column 2 #
327
SELECT MAX(pk) as max, i
328
FROM t1
329
ORDER BY max;
330
#
331
#--echo
332
--disable_result_log
333
#EXPLAIN
334
--enable_result_log
335
#SELECT MAX(pk) as max, i
336
#FROM t1
337
#ORDER BY max;
338
#
339
#--echo
340
#--echo # Only 11 is correct for collumn i in this result
341
SELECT MAX(pk) as max, i
342
FROM t1
343
WHERE pk<2
344
ORDER BY max;
345
#
346
#--echo #
347
#--echo # Cleanup
348
#--echo #
349
DROP TABLE t1;
350
#
351
#--echo #
352
#--echo # Bug#43668: Wrong comparison and MIN/MAX for YEAR(2)
353
#--echo #
354
#create table t1 (f1 year(2), f2 year(4), f3  datetime(6), f4 datetime(6));
355
#insert into t1 values
356
#  (98,1998,19980101,'1998-01-01 00:00:00'),
357
#  (00,2000,20000101,'2000-01-01 00:00:01'),
358
#  (02,2002,20020101,'2002-01-01 23:59:59'),
359
#  (60,2060,20600101,'2060-01-01 11:11:11'),
360
#  (70,1970,19700101,'1970-11-11 22:22:22'),
361
#  (NULL,NULL,NULL,NULL);
362
#select min(f1),max(f1) from t1;
363
#select min(f2),max(f2) from t1;
364
#select min(f3),max(f3) from t1;
365
#select min(f4),max(f4) from t1;
366
#select a.f1 as a, b.f1 as b, a.f1 > b.f1 as gt,
367
#       a.f1 < b.f1 as lt, a.f1<=>b.f1 as eq
368
#from t1 a, t1 b;
369
#select a.f1 as a, b.f2 as b, a.f1 > b.f2 as gt,
370
#       a.f1 < b.f2 as lt, a.f1<=>b.f2 as eq
371
#from t1 a, t1 b;
372
#select a.f1 as a, b.f3 as b, a.f1 > b.f3 as gt,
373
#       a.f1 < b.f3 as lt, a.f1<=>b.f3 as eq
374
#from t1 a, t1 b;
375
#select a.f1 as a, b.f4 as b, a.f1 > b.f4 as gt,
376
#       a.f1 < b.f4 as lt, a.f1<=>b.f4 as eq
377
#from t1 a, t1 b;
378
#select *, f1 = f2 from t1;
379
#drop table t1;
380
#
381
#--echo #
382
#--echo # Bug #54465: assert: field_types == 0 || field_types[field_pos] ==
383
#--echo #             MYSQL_TYPE_LONGLONG
384
#--echo #
385
#
386
#CREATE TABLE t1 (a INT);
387
#INSERT INTO t1 VALUES (1), (2);
388
#
389
#SELECT MAX((SELECT 1 FROM t1 ORDER BY @var LIMIT 1)) m FROM t1 t2, t1 
390
#       ORDER BY t1.a;
391
#
392
#DROP TABLE t1;
393
#
394
#--echo #
395
#--echo #  Bug#58030 crash in Item_func_geometry_from_text::val_str
396
#--echo #
397
#
398
#--disable_result_log
399
#
400
#SELECT MAX(TIMESTAMP(RAND(0)));
401
#SELECT MIN(TIMESTAMP(RAND(0)));
402
#
403
#--echo #
404
#--echo #  Bug#58177 crash and valgrind warnings in decimal and protocol sending functions...
405
#--echo #
406
#
407
#SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
408
#SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
409
#SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
410
#SELECT MIN(GET_LOCK('aaaaaaaaaaaaaaaaa',0) / '0b1111111111111111111111111111111111111111111111111111111111111111111111111' ^ (RAND()));
411
#SELECT RELEASE_LOCK('aaaaaaaaaaaaaaaaa');
412
#
413
#--enable_result_log
414
#
415
#
416
#--echo #
417
#--echo # Bug #11766094 - 59132: MIN() AND MAX() REMOVE UNSIGNEDNESS 
418
#--echo #
419
#
420
#CREATE TABLE t1 (a BIGINT UNSIGNED);
421
#INSERT INTO t1 VALUES (18446668621106209655);
422
#SELECT MAX(LENGTH(a)), LENGTH(MAX(a)), MIN(a), MAX(a), CONCAT(MIN(a)), CONCAT(MAX(a)) FROM t1;
423
#DROP TABLE t1;
424
#
425
#--echo #
426
#--echo # Bug #11766270  59343: YEAR(4): INCORRECT RESULT AND VALGRIND WARNINGS WITH MIN/MAX, UNION
427
#--echo #
428
#
429
#CREATE TABLE t1(f1 YEAR(4));
430
#INSERT INTO t1 VALUES (0000),(2001);
431
#--enable_metadata
432
#(SELECT MAX(f1) FROM t1) UNION (SELECT MAX(f1) FROM t1);
433
#--disable_metadata
434
#DROP TABLE t1;
435
#
436
#
437
#--echo #
438
#--echo End of 5.1 tests
439
#
440
####
441
#--echo #
442
#--echo # Bug#52123 Assertion failed: aggregator == aggr->Aggrtype(),
443
#--echo #           file .\item_sum.cc, line 587
444
#--echo #
445
#
446
CREATE TABLE t1(a int, primary KEY(a), b int);
447
INSERT INTO t1(a) VALUES (1), (2);
448
SELECT 1 FROM t1 ORDER BY AVG(DISTINCT a);
449
DROP TABLE t1;
450
#
451
#--echo #
452
#--echo # Bug#55648: Server crash on MIN/MAX on maximum datetime value
453
#--echo #
454
#CREATE TABLE t1(c1 TIME NOT NULL);
455
#INSERT INTO t1 VALUES('837:59:59');
456
#INSERT INTO t1 VALUES('838:59:59');
457
#SELECT MAX(c1) FROM t1;
458
#DROP TABLE t1;
459
#--echo # End of the bug#55648
460
#
461
#--echo #
462
#--echo # Bug#56120: Failed assertion on MIN/MAX on negative datetime value
463
#--echo #
464
#CREATE TABLE t1(c1 TIME NOT NULL);
465
#INSERT INTO t1 VALUES('-00:00:01');
466
#SELECT MAX(c1),MIN(c1) FROM t1;
467
#DROP TABLE t1;
468
#--echo # End of the bug#56120
469
#
470
#--echo #
471
#--echo # Bug#57932 'query with AVG(DISTINCT) returns NULL if last
472
#--echo # aggregated value was NULL'
473
#--echo #
474
CREATE TABLE t1 (pk int primary key,col_int_nokey int);
475
INSERT INTO t1 VALUES (1,7),(2,8),(3,NULL);
476
SELECT AVG(DISTINCT col_int_nokey) FROM t1;
477
# junyue: fix bug http://bugfree.corp.taobao.com/bug/203155
478
SELECT AVG(DISTINCT outr.col_int_nokey) FROM t1 AS outr LEFT JOIN t1 AS outr2 ON
479
outr.col_int_nokey = outr2.col_int_nokey;
480
DROP TABLE t1;
481
#--echo # End of the bug#57932
482

483

484
# Both AVG  and SUM  has the bug 203155, Add :
485

486
create table t1 (pk int primary key, b int);
487
insert into t1 values (1, NULL), (2, 2), (3, 4);
488
select * from t1;
489
select count(b), min(b), max(b), sum(b), avg(b) from t1;
490
drop table t1;
491