oceanbase

1
drop table if exists t1,t2;
2
create table t1 (pk int primary key, grp int, a int, c char(10) not null);
3
insert into t1 values (1, 1,1,'a');
4
insert into t1 values (2, 2,2,'b');
5
insert into t1 values (3, 2,3,'c');
6
insert into t1 values (4, 3,4,'E');
7
insert into t1 values (5, 3,5,'C');
8
insert into t1 values (6, 3,6,'D');
9
select a,c,sum(a) from t1 group by a order by a;
10
a	c	sum(a)
11
1	a	1
12
2	b	2
13
3	c	3
14
4	E	4
15
5	C	5
16
6	D	6
17
select a,c,sum(a) from t1 where a > 10 group by a order by a;
18
a	c	sum(a)
19
select sum(a) from t1 where a > 10;
20
sum(a)
21
NULL
22
select count(distinct a),count(distinct grp) from t1;
23
count(distinct a)	count(distinct grp)
24
6	3
25
insert into t1 values (7, null,null,'');
26
select count(distinct a),count(distinct grp) from t1;
27
count(distinct a)	count(distinct grp)
28
6	3
29
create table t2 (pk int primary key, grp int, a int, c char(10));
30
select grp,max(a)+max(grp),max(c) from t1 group by grp order by grp;
31
grp	max(a)+max(grp)	max(c)
32
NULL	NULL	
33
1	2	a
34
2	5	c
35
3	9	E
36
select * from t2;
37
pk	grp	a	c
38
drop table t1,t2;
39
drop table if exists t1;
40
CREATE TABLE t1 (id int ,value1 decimal(10,2),c int primary key);
41
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);
42
CREATE TABLE t2 (id int primary key,name char(20));
43
INSERT INTO t2 VALUES (1,'Set One'),(2,'Set Two');
44
select id, avg(value1)  from t1 group by id;
45
id	avg(value1)
46
1	1.000000
47
2	11.000000
48
select name, avg(value1) from t1, t2 where t1.id = t2.id group by t1.id;
49
name	avg(value1)
50
Set One	1.000000
51
Set Two	11.000000
52
drop table t1,t2;
53
create table t1 (pk int primary key, id int not null);
54
create table t2 (pk int primary key, id int not null,rating int null);
55
insert into t1 values(1,1),(2,2),(3,3);
56
insert into t2 values(1, 1, 3),(2, 2, NULL),(3, 2, NULL),(4, 3, 2),(5, 3, NULL);
57
select t1.id, avg(rating) from t1 left join t2 on ( t1.id = t2.id ) group by t1.id order by t1.id;
58
id	avg(rating)
59
1	3.0000
60
2	NULL
61
3	2.0000
62
drop table t1,t2;
63
create table t1 (a int primary key, c char(10), b char(128));
64
INSERT INTO t1 VALUES (1,'1','1');
65
INSERT INTO t1 VALUES (2,'2','2');
66
INSERT INTO t1 VALUES (4,'4','4');
67
select count(*) from t1;
68
count(*)
69
3
70
select count(*) from t1 where a = 1;
71
count(*)
72
1
73
select count(*) from t1 where a = 100;
74
count(*)
75
0
76
select count(*) from t1 where a >= 10;
77
count(*)
78
0
79
select count(a) from t1 where a = 1;
80
count(a)
81
1
82
select count(a) from t1 where a = 100;
83
count(a)
84
0
85
select count(a) from t1 where a >= 10;
86
count(a)
87
0
88
select count(b) from t1 where b >= 2;
89
count(b)
90
2
91
select count(b) from t1 where b >= 10;
92
count(b)
93
0
94
select count(c) from t1 where c = 10;
95
count(c)
96
0
97
drop table t1;
98
CREATE TABLE t1 (d datetime default now(), i int primary key);
99
INSERT INTO t1(i) VALUES (1);
100
SELECT COUNT(i), i, COUNT(i)*i FROM t1 GROUP BY i;
101
COUNT(i)	i	COUNT(i)*i
102
1	1	1
103
SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM t1 GROUP BY i;
104
COUNT(i)	(i+0)	COUNT(i)*(i+0)
105
1	1	1
106
DROP TABLE t1;
107
create table t1 (
108
pk int primary key,
109
num float,
110
my_user char(20)
111
);
112
insert into t1 values (1, 10.3,'nem'),(2, 20.53,'monty'),(3, 30.23,'sinisa');
113
insert into t1 values (4, 30.13,'nem'),(5, 20.98,'monty'),(6, 10.45,'sinisa');
114
insert into t1 values (7, 5.2,'nem'),(8, 8.64,'monty'),(9, 11.12,'sinisa');
115
select sum(num) from t1;
116
sum(num)
117
147.57999897003174
118
select sum(num) from t1 group by my_user order by my_user;
119
sum(num)
120
50.15000057220459
121
45.6299991607666
122
51.79999923706055
123
drop table t1;
124
drop table if exists t1,t2,t3;
125
create table t1 (pk int primary key, a1 int, a2 char(3));
126
insert into t1 values(1, 10,'aaa'), (2, 10,null), (3, 10,'bbb'), (4, 20,'zzz');
127
create table t2(pk int primary key, a1 char(3), a2 int, a3 float);
128
create table t3(pk int primary key, a1 char(3), a2 int, a3 float);
129
select * from t1;
130
pk	a1	a2
131
1	10	aaa
132
2	10	NULL
133
3	10	bbb
134
4	20	zzz
135
select min(a2) from t1;
136
min(a2)
137
aaa
138
select max(t1.a1), max(t2.a2) from t1, t2;
139
max(t1.a1)	max(t2.a2)
140
NULL	NULL
141
insert into t2 values(1, 'AAA', 10, 0.5);
142
insert into t2 values(2, 'BBB', 20, 1.0);
143
select t1.a1, t1.a2, t2.a1, t2.a2 from t1 left outer join t3 on t1.a1=10;
144
ERROR 42S22: Unknown column 't2.a1' in 'field list'
145
select max(t1.a2) from t1 left outer join t2 on t1.a1=10;
146
max(t1.a2)
147
zzz
148
select max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and 1=0 where t2.a1='AAA';
149
max(t2.a1)
150
NULL
151
explain select max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and 1=0 where t2.a1='AAA';
152
select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1   where t2.a1='AAA';
153
max(t2.a1)
154
AAA
155
explain select max(t2.a1) from t1 left outer join t2 on t1.a2=t2.a1  where t2.a1='AAA';
156
select max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk   where t2.a1='AAA' and 1=0;
157
max(t2.a1)
158
NULL
159
explain select max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk  where t2.a1='AAA' and 1=0;
160
select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and t1.a1=10;
161
max(t1.a2)	max(t2.a1)
162
zzz	BBB
163
explain select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and t1.a1=10;
164
select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and t1.pk=10;
165
max(t1.a2)	max(t2.a1)
166
zzz	NULL
167
explain select max(t1.a2),max(t2.a1) from t1 left outer join t2 on t1.pk=t2.pk and t1.pk=10;
168
select * from t1 left join t2 on t1.pk=t2.pk where t2.a2 > 1 and t2.a2 < 10;
169
pk	a1	a2	pk	a1	a2	a3
170
explain select * from t1 left join t2 on t1.pk=t2.pk where t2.a2 > 1 and t2.a2 < 10;
171
select * from t1 left join t2 on t1.pk=t2.pk and t2.a2 > 1 and t2.a2 < 10 order by t1.pk;
172
pk	a1	a2	pk	a1	a2	a3
173
1	10	aaa	NULL	NULL	NULL	NULL
174
2	10	NULL	NULL	NULL	NULL	NULL
175
3	10	bbb	NULL	NULL	NULL	NULL
176
4	20	zzz	NULL	NULL	NULL	NULL
177
explain select * from t1 left join t2 on t1.pk=t2.pk and t2.a2 > 1 and t2.a2 < 10;
178
drop table t1,t2;
179
CREATE TABLE t1 (pk int primary key, a int, b int);
180
select count(b), sum(b), avg(b),  min(b), max(b) from t1;
181
count(b)	sum(b)	avg(b)	min(b)	max(b)
182
0	NULL	NULL	NULL	NULL
183
select a,count(b), sum(b), avg(b), min(b), max(b) from t1 group by a order by a;
184
a	count(b)	sum(b)	avg(b)	min(b)	max(b)
185
insert into t1 values (1, 1,null);
186
select a,count(b), sum(b), avg(b), min(b), max(b) from t1 group by a order by a;
187
a	count(b)	sum(b)	avg(b)	min(b)	max(b)
188
1	0	NULL	NULL	NULL	NULL
189
insert into t1 values (2, 1,null);
190
insert into t1 values (3, 2,null);
191
select a,count(b), sum(b), avg(b), min(b), max(b) from t1 group by a order by a;
192
a	count(b)	sum(b)	avg(b)	min(b)	max(b)
193
1	0	NULL	NULL	NULL	NULL
194
2	0	NULL	NULL	NULL	NULL
195
insert into t1 values (4, 2,1);
196
select a,count(b), sum(b), avg(b), min(b), max(b) from t1 group by a order by a;
197
a	count(b)	sum(b)	avg(b)	min(b)	max(b)
198
1	0	NULL	NULL	NULL	NULL
199
2	1	1	1.0000	1	1
200
insert into t1 values (5, 3,1);
201
select a,count(b), sum(b), avg(b), min(b), max(b) from t1 group by a order by a;
202
a	count(b)	sum(b)	avg(b)	min(b)	max(b)
203
1	0	NULL	NULL	NULL	NULL
204
2	1	1	1.0000	1	1
205
3	1	1	1.0000	1	1
206
drop table t1;
207
create table t1 (col int primary key,col2 int);
208
insert into t1(col) values (-1), (-2), (-3);
209
drop table t1;
210
create table t1 (a int primary key,b int);
211
select avg(2) from t1;
212
avg(2)
213
NULL
214
drop table t1;
215