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
## simple test of all group functions
8
##
9
#
10
--disable_warnings
11
drop table if exists t1,t2;
12
--enable_warnings
13

14
#
15
##
16
## BUG#3190, WL#1639: Standard Deviation STDDEV - 2 different calculations
17
##
18
#
19
#CREATE TABLE t1 (id int(11),value1 float(10,2));
20
#INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00), (2,13.00);
21
#select id, stddev_pop(value1), var_pop(value1), stddev_samp(value1), var_samp(value1) from t1 group by id;
22
#DROP TABLE t1;
23
#
24
##
25
## BUG#8464 decimal AVG returns incorrect result
26
##
27

28
CREATE TABLE t1 (pk int primary key,col1 double , col2 double);
29
INSERT INTO t1(pk,col1) VALUES (1,-5.00000000001),(2,-5.00000000002),(3,-5.00000000003),(4,-5.00000000000);
30
#insert into t1 select * from t1;
31
select col1,count(col1),sum(col1),avg(col1) from t1 group by col1 order by col1;
32
DROP TABLE t1;
33
#
34
##
35
## BUG#8465 decimal MIN and MAX return incorrect result
36
##
37
#
38
create table t1 (pk int primary key,col1 double , col2 double);
39
insert into t1(pk,col1) values (1,-5.00000000001);
40
insert into t1(pk,col1) values (2,-5.00000000002);
41
select col1,sum(col1),max(col1),min(col1) from t1 group by col1 order by col1;
42
#delete from t1;
43
insert into t1(pk,col1) values (3,5.00000000003);
44
insert into t1(pk,col1) values (4,5.00000000004);
45
select col1,sum(col1),max(col1),min(col1) from t1 group by col1 order by col1;
46
DROP TABLE t1;
47