oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: xiaoyi.xy
5
# owner group: SQL3
6
# description: 本case是为了测试delete 一个range内的数据
7
# tags: delete,dml
8

9
####TITLE: range delete
10
--disable_warnings
11
drop table if exists t1;
12
--enable_warnings
13

14

15
####CASE: single rowkey, using rowkey, >,>=,<,<=
16
create table t1 (a int, b int, primary key (a));
17
insert into t1(a,b) values(1,1),(2,1),(3,1),(4,1);
18
delete from t1 where a=1;
19
delete from t1 where a>0;
20
select * from t1;
21

22
replace into t1(a,b) values(1,1),(2,1),(3,1),(4,1);
23
delete from t1 where a<5;
24
select * from t1;
25

26
replace into t1(a,b) values(1,1),(2,1),(3,1),(4,1);
27
delete from t1 where a>1;
28
select * from t1;
29

30
replace into t1(a,b) values(2,1),(3,1),(4,1);
31
delete from t1 where a<4;
32
select * from t1;
33

34
replace into t1(a,b) values(1,1),(2,1),(3,1);
35
delete from t1 where a>=1;
36
select * from t1;
37

38
replace into t1(a,b) values(1,1),(2,1),(3,1),(4,1);
39
delete from t1 where a<=5;
40
select * from t1;
41

42
replace into t1(a,b) values(1,1),(2,1),(3,1),(4,1);
43
delete from t1 where a>0 and a<2;
44
delete from t1 where a>=2 and a<3;
45
delete from t1 where a>=3 and a<=4;
46
select * from t1;
47

48
replace into t1(a,b) values(1,1),(2,1),(3,1),(4,1);
49
delete from t1 where a=1 or a=2 or a=3 or a=4;
50
select * from t1;
51

52
####CASE: single rowkey, using non_rowkey, >, >=, <, <=
53
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
54
delete from t1 where b>0;
55
select * from t1;
56

57
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
58
delete from t1 where b<6;
59
select * from t1;
60

61
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
62
delete from t1 where b>1;
63
select * from t1;
64

65
replace into t1(a,b) values(2,2),(3,3),(4,4);
66
delete from t1 where b<5;
67
select * from t1;
68

69
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
70
delete from t1 where b>=1;
71
select * from t1;
72

73
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
74
delete from t1 where b<=7;
75
select * from t1;
76

77
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
78
delete from t1 where b>0 and b<2;
79
delete from t1 where b>=2 and b<3;
80
delete from t1 where b>=3 and b<=4;
81
select * from t1;
82

83
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
84
delete from t1 where b=1 or b=2 or b=3 or b=4;
85
select * from t1;
86

87
####CASE: single rowkey: using rowkey + non_rowkey, >, >=, <,<=, or
88
--disable_warnings
89
drop table if exists t1;
90
--enable_warnings
91
create table t1(a int, b int, c int, primary key(a));
92
replace into t1(a,b) values(1,1),(2,2),(3,1),(4,2);
93
delete from t1 where a>0 and b>1;
94
delete from t1 where a<5 and b<2;
95
select * from t1;
96

97
replace into t1(a,b) values(1,1),(2,2),(3,1),(4,2);
98
delete from t1 where a>=0 and b<=1;
99
delete from t1 where a<=5 and b>=2;
100
select * from t1;
101

102
replace into t1(a,b) values(1,1),(2,2),(3,1),(4,2);
103
delete from t1 where a=0 or a=1 or a=2 or b=1;
104
select * from t1;
105

106
replace into t1(a,b) values(1,1),(2,2),(3,1),(4,2);
107
delete from t1 where b=2 or a=1 or a=2;
108
select * from t1;
109

110
replace into t1(a,b,c) values(1,1,NULL),(2,2,NULL),(3,3,NULL),(4,4,NULL);
111
delete from t1 where a<=1 or b>=4;
112
select * from t1;
113

114
replace into t1(a,b,c) values(1,1,NULL),(2,2,NULL),(3,3,NULL),(4,4,NULL);
115
delete from t1 where a>1 or b<4;
116
select * from t1;
117

118
####CASE: two rowkeys, using rowkey, >, >=, <, <=
119
--disable_warnings
120
drop table if exists t1;
121
--enable_warnings
122
create table t1(a int, b int, c int, d int, primary key(a,b));
123
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
124
delete from t1 where (a,b) > (0,0);
125
select * from t1;
126

127
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
128
delete from t1 where (a,b) < (4,4);
129
select * from t1;
130

131
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
132
delete from t1 where (a,b) >= (1,1);
133
select * from t1;
134

135

136
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
137
delete from t1 where (a,b) <= (1,1);
138
select * from t1;
139

140
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
141
delete from t1 where (a,b) in ((NULL,NULL),(0,0),(1,1),(2,2),(3,3),(4,4));
142
select * from t1;
143

144
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
145
delete from t1 where (a,b)=(1,1) or (a,b)=(2,2) or (a,b)=(3,3);
146
select * from t1;
147

148
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
149
delete from t1 where (a,b) in ((1,1),(2,2),(3,3));
150
select * from t1;
151

152
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
153
delete from t1 where (a,b,c,d) >= (1,1,1,1);
154
select * from t1;
155

156
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
157
delete from t1 where (a,b,c,d) <= (3,3,3,3);
158
select * from t1;
159

160
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
161
delete from t1 where (a,b,c,d)=(1,1,1,1) or (a,b,c,d)=(2,2,1,1) or (a,b,c,d)=(3,3,3,3);
162
select * from t1;
163

164
####CASE: two rowkeys, using one of rowkey, =, >, >=, <, <=
165
--disable_warnings
166
drop table if exists t1;
167
--enable_warnings
168
create table t1(a int, b int, c int, d int, primary key(a,b));
169
replace into t1(a,b,c,d) values(1,1,1,1),(1,2,1,1),(1,3,1,1);
170
delete from t1 where a = 1;
171
select * from t1;
172

173
replace into t1(a,b,c,d) values(1,1,1,1),(1,2,1,1),(1,3,1,1);
174
delete from t1 where a > 0;
175
select * from t1;
176

177
replace into t1(a,b,c,d) values(1,1,1,1),(1,2,1,1),(1,3,1,1);
178
delete from t1 where a >=1 ;
179
select * from t1;
180

181
replace into t1(a,b,c,d) values(1,1,1,1),(1,2,1,1),(1,3,1,1);
182
delete from t1 where a < 4;
183
select * from t1;
184

185
replace into t1(a,b,c,d) values(1,1,1,1),(1,2,1,1),(1,3,1,1);
186
delete from t1 where a <= 1;
187
select * from t1;
188

189
replace into t1(a,b,c,d) values(1,1,1,1),(1,2,1,1),(1,3,1,1);
190
delete from t1 where a=1 or a=2 or a=3;
191
select * from t1;
192

193
replace into t1(a,b,c,d) values(1,1,1,1),(1,2,1,1),(1,3,1,1);
194
delete from t1 where (a=1 or a=2 or a=3) and (b=1 or b=2);
195
select * from t1;
196

197
replace into t1(a,b,c,d) values(1,1,1,1),(1,2,1,1),(1,3,1,1);
198
delete from t1 where a>=1 and b>=2;
199
select * from t1;
200

201
####CASE:two rowkeys, using rowkey + non_rowkey, >, >=, <, <=, =
202
--disable_warnings
203
drop table if exists t1;
204
--enable_warnings
205
create table t1(a int, b int, c int, d int, primary key(a,b));
206
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,2,1),(3,3,3,1);
207
delete from t1 where (a,b)>=(1,1) and c=1;
208
select * from t1;
209

210
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,2,1),(3,3,3,1);
211
delete from t1 where (a,b)>=(1,1) and c=3;
212
select * from t1;
213

214
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,2,1),(3,3,3,1);
215
delete from t1 where (a=1 or a=2 or a=3) and c>=1;
216
select * from t1;
217

218
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,2,1),(3,3,3,1);
219
delete from t1 where (a=1 or a=2 or a=3) and c<1;
220
select * from t1;
221

222
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,2,1),(3,3,3,1);
223
delete from t1 where (a=1 or a=2 or a=3) and (b=2 or b=3);
224
select * from t1;
225

226
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,2,1),(3,3,3,1);
227
delete from t1 where (a=1 or a=2 or a=3) and (b=2 or b=3) and c=3;
228
select * from t1;
229

230
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,2,1),(3,3,3,1);
231
delete from t1 where a>=0 and a<=3 and b>1 and b<3;
232
select * from t1;
233

234
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,2,1),(3,3,3,1);
235
delete from t1 where (c,d)>=(1,1);
236
select * from t1;
237

238
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,2,1),(3,3,3,1);
239
delete from t1 where (b,a) > (1,1);
240
select * from t1;
241

242
####CASE:multi rowkeys
243
--disable_warnings
244
drop table if exists t1, t2, t3;
245
--enable_warnings
246
#int
247
create table t1(a int, b int, c int, d int, primary key(a,b,c));
248
insert into t1(a,b,c) values (1,1,1),(2,2,2),(3,3,3);
249
delete from t1 where (a,b,c)>(0,0,0) and (a,b,c)<=(3,3,3);
250
select * from t1;
251
#varchar
252
create table t2(a varchar(128), b varchar(128), c varchar(128), d varchar(128), primary key(a,b,c));
253
insert into t2(a,b,c) values ('a','a','a'),('b','b','b'),('c','c','c');
254
delete from t2 where (a,b,c)>=('a','a','b') and (a,b,c)<('b','b','c');
255
select * from t2;
256
#timestamp
257
create table t3(a int, b varchar(128), c  timestamp(6) default "2012-01-01 12:00:00", d int, primary key(a,b,c));
258
insert into t3(a,b,c) values (1,'a','2014-02-17'),(2,'b','2014-02-17'),(3,'c','2014-02-18');
259
delete from t3 where (a,b,c)>=(1,'a','2014-02-17 00:00:00') and (a,b,c)<=(2,'b','2014-02-18');
260
select * from t3;
261

262
--disable_warnings
263
drop table if exists t1, t2, t3;
264
--enable_warnings
265