oceanbase

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

7
#
8
## Bug 
9
#
10
# partition by key()
11
--disable_warnings
12
drop database if exists db1;
13
drop database if exists db2;
14
create database db1;
15
create database db2;
16
use db1;
17
drop table if exists t1;
18
create table t1(c1 int key) partition by key() partitions 2;
19
--enable_warnings
20
use db2;
21
--disable_warnings
22
drop table if exists t1;
23
--enable_warnings
24
create table t1(c1 int key) partition by key() partitions 2;
25
select * from db1.t1,db2.t1;
26
# partition by key(expr)
27
use db1;
28
--disable_warnings
29
drop table if exists t1;
30
create table t1(c1 int key) partition by key(c1) partitions 2;
31
create database if not exists db2;
32
use db2;
33
--enable_warnings
34
--disable_warnings
35
drop table if exists t1;
36
--enable_warnings
37
create table t1(c1 int key) partition by key(c1) partitions 2;
38
select * from db1.t1,db2.t1;
39
# partition by hash(expr)
40
use db1;
41
--disable_warnings
42
drop table if exists t1;
43
create table t1(c1 int key) partition by hash(c1) partitions 2;
44
create database if not exists db2;
45
--enable_warnings
46
use db2;
47
--disable_warnings
48
drop table if exists t1;
49
--enable_warnings
50
create table t1(c1 int key) partition by hash(c1) partitions 2;
51
select * from db1.t1,db2.t1;
52

53
#
54
--disable_warnings
55
drop table if exists t1;
56
--enable_warnings
57
create table t1 (c1 int primary key, c2 int);
58
insert into t1 values(1,8), (2,7), (3,6), (4,5);
59
--error 1054
60
select c1 from t1 group by t2.c1;
61
--error 1052
62
select c1, c2 as c1 from t1 group by c1;
63
--sorted_result
64
select c1, c2 as c1 from t1 group by t1.c1;
65
--error 1052
66
select c1 as c1, c2 as c1 from t1 group by c1;
67
--sorted_result
68
select c1 as c1, c2 as c1 from t1 group by t1.c1;
69
--sorted_result
70
select t1.c1 as cc from t1 group by t1.c1;
71
--sorted_result
72
select c1, c1 from t1 group by c1;
73
select 1 as c1, 2 as c2 from t1 group by c1;
74
--error 1052
75
select 1 as c1, c1 from t1 group by c1;
76
--disable_warnings
77
select c1 as c2, c2 as c1 from t1 group by c1;
78
--error 1052
79
select c2+1 as c1, c1 from t1 group by c1;
80
--enable_warnings
81
select c1, c2 as c1 from t1 having t1.c1 > 2;
82
select c1, c2 + 1 as c1 from t1 having t1.c1 > 2;
83
--error 1052
84
select c1, c2 + 1 as c1 from t1 having c1 > 2;
85
--error 1052
86
select c1, c2 + 1 as c1 from t1 order by c1;
87
select c1, c2 + 1 as c1 from t1 order by t1.c1;
88
--disable_warnings
89
--error 1052
90
select c1, c2 + 1 as c1 from t1 group by c1;
91
--sorted_result
92
select c1, c2 + 1 as c1 from t1 group by t1.c1;
93
--enable_warnings
94
select t1.c1 as c1 from t1 order by t1.c1;
95
select t1.c1 as c2 from t1 order by t1.c1;
96
drop table t1;
97

98
#
99
--disable_warnings
100
drop table if exists t1, t2;
101
--enable_warnings
102
create table t1 (a int(11), b char(10), key (a));
103
insert into t1 (a) values (1),(2),(3),(4);
104
create table t2 (a int);
105
select * from t1 left join t2 on t1.a=t2.a order by t1.a;
106
select * from t1 left join t2 on t1.a=t2.a having not (t2.a <=> t1.a) order by t1.a;
107
select exists(( select 1));
108
select exists( select 1);
109
drop table t1,t2;
110

111
create table t1(c1 int, c2 int);
112
insert into t1 values(1, 1), (2, 2);
113
select * from t1 where c1<'2';
114
select * from t1 where c1>'1';
115
select * from t1 where c1='2';
116
select * from t1 where c1!='2';
117
select * from t1 where c1<='2';
118
select * from t1 where c1>='2';
119
select * from t1 where c1+'1'<=3;
120
select * from t1 where c1+'1'>=2;
121
drop table t1;
122

123
create table t1(c1 int primary key, c2 int);
124
insert into t1 values(1, 1), (2, 2);
125
select * from t1 where c1<'2';
126
select * from t1 where c1>'1';
127
select * from t1 where c1='2';
128
select * from t1 where c1!='2';
129
select * from t1 where c1<='2';
130
select * from t1 where c1>='2';
131
select * from t1 where c1+'1'<=3;
132
select * from t1 where c1+'1'>=2;
133
drop table t1;
134

135
##like syntax
136
SELECT 'a' = 'a ', 'a' LIKE 'a ';
137
SELECT 'David!' LIKE 'David_';
138
SELECT 'David!' LIKE '%D%v%';
139
SELECT 'David!' LIKE 'David\_';
140
SELECT 'David_' LIKE 'David\_';
141
SELECT 'David_' LIKE 'David|_' ESCAPE '|';
142
SELECT 'abc' LIKE 'ABC';
143
SELECT 'abc' LIKE BINARY 'ABC';
144
SELECT 10 LIKE '1%';
145
SELECT 'David_' LIKE 'David|_' ESCAPE null;
146
SELECT 'David_' LIKE 'David|_' ESCAPE 1;
147
--error 1210
148
SELECT 'David_' LIKE 'David|_' ESCAPE 12;
149
--error 1210
150
SELECT 'David_' LIKE 'David|_' ESCAPE '12';
151
## alias
152
--disable_warnings
153
drop table if exists a1,a2;
154
--enable_warnings
155
create table a2(rowkey_suffix2 int primary key, price2 int);
156
create table a1(rowkey_suffix int primary key, price int);
157
--error 1064
158
select (select price from a2 where 1000 > price) as 'x', a1.* as 'with_alias' from a1;
159
--error 1064
160
select (select price from a2 where 1000 > price) as 'x', a1.* as with_alias from a1;
161
--error 1241
162
select 1 from dual where @a:=1 and (1, 2);
163
--error 1241
164
select 1 from dual where @a:=1 and (1, (select 2));
165
--error 1241
166
select 1 from dual where @a:=1 and (1, exists(select 2));
167

168
create table t1(c1 int);
169
select 1 from t1 where c1 in (select c1 from t1 where exists( (select 1) union (select 1)));
170
select (1 and 100) or 100;
171

172
drop table t1;
173
create table t1(c1 bigint, c2 varchar(64), c3 datetime);
174
insert into t1 values(20101010000000, '020101010000000', '2010-10-10 00:00:00');
175
select * from t1 where c1=c2 and c1=cast('2010-10-10 00:00:00' as datetime);
176

177
--disable_warnings
178
drop table if exists bug;
179
--enable_warnings
180
create table bug (col_float float);
181
insert into bug values(100);
182
select ((col_float and 100) or col_float) from bug;
183

184
drop table t1;
185
create table test1 (id int,dt datetime(6), primary key(id, dt));
186
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');
187
select * from test1 where (id, dt) > (0, '2017-01-02') and (id, dt) <= (1, '2017-01-03');
188
select * from test1 where (id, dt) > (0, '2017-01-02') and (id, dt) <= (1, '2017-01-03') and dt < '2017-01-02';
189

190

191
#fix a bug :
192
--result_format 4
193
--error 1054
194
select cast '';
195
--error 1054
196
select length '';
197
--error 1054
198
select yearweek '';
199
--error 1054
200
select lala '';
201
select 1 '';
202
select 2 '';
203
select 2 as '';
204
select 1 as '';
205

206
drop database if exists db1;
207
drop database if exists db2;
208