oceanbase

1
drop database if exists db1;
2
drop database if exists db2;
3
create database db1;
4
create database db2;
5
use db1;
6
drop table if exists t1;
7
create table t1(c1 int key) partition by key() partitions 2;
8
use db2;
9
drop table if exists t1;
10
create table t1(c1 int key) partition by key() partitions 2;
11
select * from db1.t1,db2.t1;
12
c1	c1
13
use db1;
14
drop table if exists t1;
15
create table t1(c1 int key) partition by key(c1) partitions 2;
16
create database if not exists db2;
17
use db2;
18
drop table if exists t1;
19
create table t1(c1 int key) partition by key(c1) partitions 2;
20
select * from db1.t1,db2.t1;
21
c1	c1
22
use db1;
23
drop table if exists t1;
24
create table t1(c1 int key) partition by hash(c1) partitions 2;
25
create database if not exists db2;
26
use db2;
27
drop table if exists t1;
28
create table t1(c1 int key) partition by hash(c1) partitions 2;
29
select * from db1.t1,db2.t1;
30
c1	c1
31
drop table if exists t1;
32
create table t1 (c1 int primary key, c2 int);
33
insert into t1 values(1,8), (2,7), (3,6), (4,5);
34
select c1 from t1 group by t2.c1;
35
ERROR 42S22: Unknown column 't2.c1' in 'group statement'
36
select c1, c2 as c1 from t1 group by c1;
37
ERROR 23000: Column 'c1' in group statement is ambiguous
38
select c1, c2 as c1 from t1 group by t1.c1;
39
c1	c1
40
1	8
41
2	7
42
3	6
43
4	5
44
select c1 as c1, c2 as c1 from t1 group by c1;
45
ERROR 23000: Column 'c1' in group statement is ambiguous
46
select c1 as c1, c2 as c1 from t1 group by t1.c1;
47
c1	c1
48
1	8
49
2	7
50
3	6
51
4	5
52
select t1.c1 as cc from t1 group by t1.c1;
53
cc
54
1
55
2
56
3
57
4
58
select c1, c1 from t1 group by c1;
59
c1	c1
60
1	1
61
2	2
62
3	3
63
4	4
64
select 1 as c1, 2 as c2 from t1 group by c1;
65
c1	c2
66
1	2
67
1	2
68
1	2
69
1	2
70
Warnings:
71
Warning	1052	Column 'c1' in group statement is ambiguous
72
select 1 as c1, c1 from t1 group by c1;
73
ERROR 23000: Column 'c1' in group statement is ambiguous
74
select c1 as c2, c2 as c1 from t1 group by c1;
75
c2	c1
76
1	8
77
2	7
78
3	6
79
4	5
80
select c2+1 as c1, c1 from t1 group by c1;
81
ERROR 23000: Column 'c1' in group statement is ambiguous
82
select c1, c2 as c1 from t1 having t1.c1 > 2;
83
c1	c1
84
3	6
85
4	5
86
select c1, c2 + 1 as c1 from t1 having t1.c1 > 2;
87
c1	c1
88
3	7
89
4	6
90
select c1, c2 + 1 as c1 from t1 having c1 > 2;
91
ERROR 23000: Column 'c1' in having clause is ambiguous
92
select c1, c2 + 1 as c1 from t1 order by c1;
93
ERROR 23000: Column 'c1' in order clause is ambiguous
94
select c1, c2 + 1 as c1 from t1 order by t1.c1;
95
c1	c1
96
1	9
97
2	8
98
3	7
99
4	6
100
select c1, c2 + 1 as c1 from t1 group by c1;
101
ERROR 23000: Column 'c1' in group statement is ambiguous
102
select c1, c2 + 1 as c1 from t1 group by t1.c1;
103
c1	c1
104
1	9
105
2	8
106
3	7
107
4	6
108
select t1.c1 as c1 from t1 order by t1.c1;
109
c1
110
1
111
2
112
3
113
4
114
select t1.c1 as c2 from t1 order by t1.c1;
115
c2
116
1
117
2
118
3
119
4
120
drop table t1;
121
drop table if exists t1, t2;
122
create table t1 (a int(11), b char(10), key (a));
123
insert into t1 (a) values (1),(2),(3),(4);
124
create table t2 (a int);
125
select * from t1 left join t2 on t1.a=t2.a order by t1.a;
126
a	b	a
127
1	NULL	NULL
128
2	NULL	NULL
129
3	NULL	NULL
130
4	NULL	NULL
131
select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a) order by t1.a;
132
a	b	a
133
1	NULL	NULL
134
2	NULL	NULL
135
3	NULL	NULL
136
4	NULL	NULL
137
select exists(( select 1));
138
exists(( select 1))
139
1
140
select exists( select 1);
141
exists( select 1)
142
1
143
drop table t1,t2;
144
create table t1(c1 int, c2 int);
145
insert into t1 values(1, 1), (2, 2);
146
select * from t1 where c1<'2';
147
c1	c2
148
1	1
149
select * from t1 where c1>'1';
150
c1	c2
151
2	2
152
select * from t1 where c1='2';
153
c1	c2
154
2	2
155
select * from t1 where c1!='2';
156
c1	c2
157
1	1
158
select * from t1 where c1<='2';
159
c1	c2
160
1	1
161
2	2
162
select * from t1 where c1>='2';
163
c1	c2
164
2	2
165
select * from t1 where c1+'1'<=3;
166
c1	c2
167
1	1
168
2	2
169
select * from t1 where c1+'1'>=2;
170
c1	c2
171
1	1
172
2	2
173
drop table t1;
174
create table t1(c1 int primary key, c2 int);
175
insert into t1 values(1, 1), (2, 2);
176
select * from t1 where c1<'2';
177
c1	c2
178
1	1
179
select * from t1 where c1>'1';
180
c1	c2
181
2	2
182
select * from t1 where c1='2';
183
c1	c2
184
2	2
185
select * from t1 where c1!='2';
186
c1	c2
187
1	1
188
select * from t1 where c1<='2';
189
c1	c2
190
1	1
191
2	2
192
select * from t1 where c1>='2';
193
c1	c2
194
2	2
195
select * from t1 where c1+'1'<=3;
196
c1	c2
197
1	1
198
2	2
199
select * from t1 where c1+'1'>=2;
200
c1	c2
201
1	1
202
2	2
203
drop table t1;
204
SELECT 'a' = 'a ', 'a' LIKE 'a ';
205
'a' = 'a '	'a' LIKE 'a '
206
1	0
207
SELECT 'David!' LIKE 'David_';
208
'David!' LIKE 'David_'
209
1
210
SELECT 'David!' LIKE '%D%v%';
211
'David!' LIKE '%D%v%'
212
1
213
SELECT 'David!' LIKE 'David\_';
214
'David!' LIKE 'David\_'
215
0
216
SELECT 'David_' LIKE 'David\_';
217
'David_' LIKE 'David\_'
218
1
219
SELECT 'David_' LIKE 'David|_' ESCAPE '|';
220
'David_' LIKE 'David|_' ESCAPE '|'
221
1
222
SELECT 'abc' LIKE 'ABC';
223
'abc' LIKE 'ABC'
224
1
225
SELECT 'abc' LIKE BINARY 'ABC';
226
'abc' LIKE BINARY 'ABC'
227
0
228
SELECT 10 LIKE '1%';
229
10 LIKE '1%'
230
1
231
SELECT 'David_' LIKE 'David|_' ESCAPE null;
232
'David_' LIKE 'David|_' ESCAPE null
233
0
234
SELECT 'David_' LIKE 'David|_' ESCAPE 1;
235
'David_' LIKE 'David|_' ESCAPE 1
236
0
237
SELECT 'David_' LIKE 'David|_' ESCAPE 12;
238
ERROR HY000: Incorrect arguments to ESCAPE
239
SELECT 'David_' LIKE 'David|_' ESCAPE '12';
240
ERROR HY000: Incorrect arguments to ESCAPE
241
drop table if exists a1,a2;
242
create table a2(rowkey_suffix2 int primary key, price2 int);
243
create table a1(rowkey_suffix int primary key, price int);
244
select (select price from a2 where 1000 > price) as 'x', a1.* as 'with_alias' from a1;
245
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'as 'with_alias' from a1' at line 1
246
select (select price from a2 where 1000 > price) as 'x', a1.* as with_alias from a1;
247
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'as with_alias from a1' at line 1
248
select 1 from dual where @a:=1 and (1, 2);
249
ERROR 21000: Operand should contain 1 column(s)
250
select 1 from dual where @a:=1 and (1, (select 2));
251
ERROR 21000: Operand should contain 1 column(s)
252
select 1 from dual where @a:=1 and (1, exists(select 2));
253
ERROR 21000: Operand should contain 1 column(s)
254
create table t1(c1 int);
255
select 1 from t1 where c1 in (select c1 from t1 where exists( (select 1) union (select 1)));
256
1
257
select (1 and 100) or 100;
258
(1 and 100) or 100
259
1
260
drop table t1;
261
create table t1(c1 bigint, c2 varchar(64), c3 datetime);
262
insert into t1 values(20101010000000, '020101010000000', '2010-10-10 00:00:00');
263
select * from t1 where c1=c2 and c1=cast('2010-10-10 00:00:00' as datetime);
264
c1	c2	c3
265
20101010000000	020101010000000	2010-10-10 00:00:00
266
drop table if exists bug;
267
create table bug (col_float float);
268
insert into bug values(100);
269
select ((col_float and 100) or col_float) from bug;
270
((col_float and 100) or col_float)
271
1
272
drop table t1;
273
create table test1 (id int,dt datetime(6), primary key(id, dt));
274
insert into test1 values (0, '2017-01-01'), (0, '2017-01-02'), (0, '2017-01-03'), (1, '2017-01-01'), (1, '2017-01-02'), (1, '2017-01-03');
275
select * from test1 where (id, dt) > (0, '2017-01-02') and (id, dt) <= (1, '2017-01-03');
276
id	dt
277
0	2017-01-03 00:00:00.000000
278
1	2017-01-01 00:00:00.000000
279
1	2017-01-02 00:00:00.000000
280
1	2017-01-03 00:00:00.000000
281
select * from test1 where (id, dt) > (0, '2017-01-02') and (id, dt) <= (1, '2017-01-03') and dt < '2017-01-02';
282
id	dt
283
1	2017-01-01 00:00:00.000000
284
result_format: 4
285
select cast '';
286
ERROR 42S22: Unknown column 'cast' in 'field list'
287
select length '';
288
ERROR 42S22: Unknown column 'length' in 'field list'
289
select yearweek '';
290
ERROR 42S22: Unknown column 'yearweek' in 'field list'
291
select lala '';
292
ERROR 42S22: Unknown column 'lala' in 'field list'
293
select 1 '';
294
+---+
295
|   |
296
+---+
297
| 1 |
298
+---+
299
select 2 '';
300
+---+
301
|   |
302
+---+
303
| 2 |
304
+---+
305
select 2 as '';
306
+---+
307
|   |
308
+---+
309
| 2 |
310
+---+
311
select 1 as '';
312
+---+
313
|   |
314
+---+
315
| 1 |
316
+---+
317

318
drop database if exists db1;
319
drop database if exists db2;
320