oceanbase

1
drop table if exists t1,t2,t3,t4,t5;
2
create table t1 (pk int primary key,a double ,b double);
3
insert into t1(pk,a) values (1,12345678901234567890);
4
select count(a) from t1;
5
count(a)
6
1
7
select count(distinct a) from t1;
8
count(distinct a)
9
1
10
drop table t1;
11
CREATE TABLE t1 (a INT, b INT primary key);
12
INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
13
SELECT a,COUNT(DISTINCT b) AS cnt FROM t1 GROUP BY a HAVING cnt > 50;
14
a	cnt
15
SELECT a,SUM(DISTINCT b) AS sumation FROM t1 GROUP BY a HAVING sumation > 50;
16
a	sumation
17
SELECT a,AVG(DISTINCT b) AS average FROM t1 GROUP BY a HAVING average > 50;
18
a	average
19
DROP TABLE t1;
20
CREATE TABLE t1 ( a INT, b INT primary key);
21
INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
22
EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
23
SELECT MIN(a), MIN(b) FROM t1;
24
MIN(a)	MIN(b)
25
NULL	1
26
CREATE TABLE t2( pk int primary key, a INT, b INT, c INT);
27
INSERT INTO t2 ( pk,a, b, c ) VALUES ( 1,1, NULL, 2 ), ( 2,1, 3, 4 ), ( 3,1, 4, 4 );
28
EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
29
SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
30
MIN(b)	MIN(c)
31
3	2
32
CREATE TABLE t3 (pk int primary key , a INT, b INT, c int);
33
INSERT INTO t3 VALUES (1,1, NULL, 1), (2,2, NULL, 2),  (3,3, NULL, 3);
34
EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
35
SELECT MIN(a), MIN(b) FROM t3 where a = 2;
36
MIN(a)	MIN(b)
37
2	NULL
38
CREATE TABLE t4 (a INT, b INT, c int, primary KEY(a, c));
39
INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (3, 1, 3);
40
EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
41
SELECT MIN(a), MIN(b) FROM t4 where a = 2;
42
MIN(a)	MIN(b)
43
2	NULL
44
SELECT MIN(b), min(c) FROM t4 where a = 2;
45
MIN(b)	min(c)
46
NULL	2
47
CREATE TABLE t5( a INT, b INT, primary KEY( a, b),c int);
48
INSERT INTO t5(a,b) VALUES( 1, 1 ), ( 1, 2 );
49
EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
50
SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
51
MIN(a)	MIN(b)
52
1	1
53
SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
54
MIN(a)	MIN(b)
55
1	2
56
DROP TABLE t1, t2, t3, t4, t5;
57
CREATE TABLE t1 (a int, b datetime(6), primary key (a,b),c int);
58
SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01';
59
MIN(b)
60
NULL
61
DROP TABLE t1;
62
CREATE TABLE t1 (a INT primary key, b int);
63
INSERT INTO t1(a) VALUES (1),(2),(3),(4);
64
SELECT a FROM t1 HAVING COUNT(*)>2;
65
a
66
1
67
SELECT COUNT(*), a FROM t1;
68
COUNT(*)	a
69
4	1
70
SELECT a FROM t1 HAVING COUNT(*)>2;
71
a
72
1
73
SELECT COUNT(*), a FROM t1;
74
COUNT(*)	a
75
4	1
76
DROP TABLE t1;
77
CREATE TABLE t1(pk int primary key,a DOUBLE );
78
INSERT INTO t1 VALUES (1,10), (2,20);
79
SELECT AVG(a), CAST(AVG(a) AS DECIMAL) FROM t1;
80
AVG(a)	CAST(AVG(a) AS DECIMAL)
81
15	15
82
DROP TABLE t1;
83
CREATE TABLE t1 (a INT, b INT primary key);
84
INSERT INTO t1 VALUES (1,1), (1,2), (1,3);
85
SELECT COUNT(*) FROM t1;
86
COUNT(*)
87
3
88
SELECT COUNT(*) FROM t1 where a=1;
89
COUNT(*)
90
3
91
SELECT COUNT(*),a FROM t1;
92
COUNT(*)	a
93
3	1
94
SELECT COUNT(*) FROM t1 a JOIN t1 b ON a.a= b.a;
95
COUNT(*)
96
9
97
DROP TABLE t1;
98
CREATE TABLE t1 (
99
pk INT NOT NULL,
100
i INT,
101
PRIMARY KEY (pk)
102
);
103
INSERT INTO t1 VALUES (1,11),(2,12),(3,13);
104
SELECT MAX(pk) as max, i
105
FROM t1
106
ORDER BY max;
107
max	i
108
3	11
109
SELECT MAX(pk) as max, i
110
FROM t1
111
WHERE pk<2
112
ORDER BY max;
113
max	i
114
1	11
115
DROP TABLE t1;
116
CREATE TABLE t1(a int, primary KEY(a), b int);
117
INSERT INTO t1(a) VALUES (1), (2);
118
SELECT 1 FROM t1 ORDER BY AVG(DISTINCT a);
119
1
120
1
121
DROP TABLE t1;
122
CREATE TABLE t1 (pk int primary key,col_int_nokey int);
123
INSERT INTO t1 VALUES (1,7),(2,8),(3,NULL);
124
SELECT AVG(DISTINCT col_int_nokey) FROM t1;
125
AVG(DISTINCT col_int_nokey)
126
7.5000
127
SELECT AVG(DISTINCT outr.col_int_nokey) FROM t1 AS outr LEFT JOIN t1 AS outr2 ON
128
outr.col_int_nokey = outr2.col_int_nokey;
129
AVG(DISTINCT outr.col_int_nokey)
130
7.5000
131
DROP TABLE t1;
132
create table t1 (pk int primary key, b int);
133
insert into t1 values (1, NULL), (2, 2), (3, 4);
134
select * from t1;
135
pk	b
136
1	NULL
137
2	2
138
3	4
139
select count(b), min(b), max(b), sum(b), avg(b) from t1;
140
count(b)	min(b)	max(b)	sum(b)	avg(b)
141
2	2	4	6	3.0000
142
drop table t1;
143