oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: yuchen.wyc
5
# owner group: sql1
6
# 测试delete stmt
7
# tags: delete,dml
8

9
--disable_warnings
10
drop table if exists t1, t_part, t_single;
11
--enable_warnings
12
--disable_warnings
13
drop table if exists t2;
14
--enable_warnings
15
--disable_warnings
16
drop table if exists t3;
17
--enable_warnings
18
--disable_warnings
19
drop table if exists t4;
20
--enable_warnings
21
--result_format 4
22
#--explain_protocol 2
23

24
#single column rowkey
25
create table t1(a int primary key, b int);
26

27
insert into t1 values(1,0),(2,0),(3,0);
28
delete from t1;
29
select * from t1;
30

31
insert into t1 values(1,0),(2,0),(3,0);
32
delete from t1 where a = 1;
33
delete from t1 where a = 2;
34
delete from t1 where a = 3;
35
select * from t1;
36

37
insert into t1 values(1,0),(2,0),(3,0);
38
delete from t1 where a=1;
39
select * from t1;
40

41
insert into t1 values(1,0);
42
replace into t1 values(2,0),(3,0);
43
delete from t1 where a=1 or a=2 or a=3;
44
select * from t1;
45

46
insert into t1 values(1,0),(2,0);
47
delete from t1 where a=1;
48
delete from t1 where a=2;
49
delete from t1 where a=3;
50
select * from t1;
51

52
drop table t1;
53

54
create table t_part (c1 bigint primary key, c2 bigint) partition by hash(c1) partitions 2;
55
create table t_single(c1 int);
56
insert into t_part(c1) values(1231346464513131);
57
insert into t_part values(1, 2), (3, 1);
58
insert into t_part values(2, 3), (4, 3);
59
insert into t_part partition(p0, p1) values(5, 6);
60
insert into t_part values('6', '6'), ('8', '8');
61
delete from t_part where c1 in (select c1 from t_single);
62
select * from t_part;
63
drop table t_part, t_single;
64

65
# multi-column rowkey
66
create table t2 (p1 int, p2 int, p3 int, p4 int, primary key(p1,p2,p3));
67

68
insert into t2 values(1,1,1,0),(2,2,2,0),(3,3,3,0),(4,4,4,0),(5,4,4,0),(6,4,4,0),(7,4,6,0),(8,4,6,0),(9,4,6,0),(10,4,6,0),(11,4,6,0),(12,4,6,0), (13,4,6,0);
69

70
delete from t2 where p1 = 1;
71

72
--disable_query_log
73
--let $p=13
74
while($p>1)
75
{
76
   eval delete from t2 where p1=$p and p2 = $p and p3=$p;
77
   dec $p;
78
}
79
--enable_query_log
80

81
select * from t2;
82
select p1,p3 from t2;
83

84
replace into t2 values(1,1,1,0),(2,2,2,0),(3,3,3,0),(4,4,4,0),(5,4,4,0),(6,4,4,0),(7,4,6,0),(8,4,6,0),(9,4,6,0),(10,4,6,0),(11,4,6,0),(12,4,6,0), (13,4,6,0);
85

86
delete from t2 where p1>3 or p3 >=6;
87
select * from t2;
88
select p1,p3 from t2;
89
delete from t2 where p1=1 and p3 =2;
90
select * from t2;
91
select p1,p3 from t2;
92
delete from t2 where p1=1 and p2 =1 and p3=1;
93
select * from t2;
94
select p1,p3 from t2;
95

96
replace into t2 values(1,1,1,0),(2,2,2,0),(3,3,3,0),(4,4,4,0),(5,4,4,0),(6,4,4,0),(7,4,6,0),(8,4,6,0),(9,4,6,0),(10,4,6,0),(11,4,6,0),(12,4,6,0), (13,4,6,0);
97

98
delete from t2 where p1 in (1,2,3,6,7,8,12,13,0);
99
select * from t2;
100

101
drop table t2;
102

103

104
# mix common column and rowkey column
105
create table t3 (p1 int, p2 int, p3 int, primary key(p1,p2));
106

107
insert into t3 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,4,4),(6,4,4),(7,4,6),(8,4,6),(9,4,6),(10,4,6),(11,4,6),(12,4,6), (13,4,6);
108
delete from t3 where p1=1 and p2=1 and p3=1;
109
select * from t3;
110
select p1,p3 from t3;
111

112
replace into t3 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,4,4),(6,4,4),(7,4,6),(8,4,6),(9,4,6),(10,4,6),(11,4,6),(12,4,6), (13,4,6);
113
delete from t3 where p1>3 or p3 >=6;
114
select * from t3;
115
select p1,p3 from t3;
116

117
delete from t3 where p1=1 and p3 =2;
118
select * from t3;
119
select p1,p3 from t3;
120

121
replace into t3 values(1,1,1),(2,2,2);
122
delete from t3 where p1=1 and p2 =1 and p3=1;
123
select * from t3;
124
select p1,p3 from t3;
125

126
replace into t3 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,4,4),(6,4,4),(7,4,6),(8,4,6),(9,4,6),(10,4,6),(11,4,6),(12,4,6),(13,4,6);
127
delete from t3 where p1 in (1,2,3,6,7,8,12,13,0);
128
select * from t3;
129

130
drop table t3;
131

132
# where clause
133
create table t4 (p1 varchar(100), p2 int, p3 datetime(6), p4 int, primary key(p1,p2,p3));
134

135
insert into t4 values('a', 41, '2012-10-23 17:14:00',0), ('b', 42, '2012-10-23 17:15:00',0), ('c', 43, '2012-10-23 17:16:00',0);
136
delete from t4 where p3='2012-10-23 17:14:00';
137
select * from t4;
138

139
replace into t4 values('a', 41, '2012-10-23 17:14:00',0), ('b', 42, '2012-10-23 17:15:00',0), ('c', 43, '2012-10-23 17:16:00',0);
140
delete from t4 where p2='42';
141
select * from t4;
142

143
replace into t4 values('a', 41, '2012-10-23 17:14:00',0), ('b', 42, '2012-10-23 17:15:00',0), ('c', 43, '2012-10-23 17:16:00',0);
144
delete from t4 where p2<'401' and p1='c';
145
select * from t4;
146

147
delete from t4 where p1='a' and p2=41 and p3='2012-10-23 17:14:00';
148
select * from t4;
149

150
drop table t4;
151

152
create table t2(c1 int primary key, c2 int, c3 int);
153
create table t3 (c1 int, c2 int, c3 int);
154

155
insert into t2 values (1, 1, 1);
156
insert into t2 values (2, 2, 2);
157
insert into t2 values (3, 2, 3);
158
insert into t2 values (4, 6, 4);
159
insert into t2 values (5, 4, 3);
160
insert into t2 values (6, 4, 2);
161
insert into t3 values(2, 2, 2);
162
insert into t3 values(3, 3, 3);
163
insert into t3 values(4, 4, 4);
164
insert into t3 values(5, 5, 5);
165

166
delete t3 from t3, t2 where t3.c1 = t2.c2;
167
delete t3 from t3, t2 where t3.c1 = t2.c1;
168
--error 1235
169
delete t3, t33 from t3 left join t3 as t33 on t3.c1 = t33.c1;
170
select * from t3;
171
select * from t2;
172

173
drop table t2;
174
drop table t3;
175

176
--disable_warnings
177
drop table if exists dns_inner_resource_record_info;
178
--enable_warnings
179

180
CREATE TABLE `dns_inner_resource_record_info`( 
181
         `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', 
182
         `owner` varchar(256) NOT NULL COMMENT '资源',
183
         `cluster_name` varchar(20) NOT NULL COMMENT '所属集群 冗余字段', 
184
         `zone_name` varchar(255) NOT NULL DEFAULT '' COMMENT 'ZONE NAME', 
185
         `view_group_name` varchar(60) DEFAULT '' COMMENT 'VIEW GRP name', 
186
         `domain_group_name` varchar(60) DEFAULT NULL COMMENT '域名组', 
187
         `idc_id` varchar(20) DEFAULT NULL COMMENT 'IDC ID',
188
         `rr_type` varchar(10) NOT NULL COMMENT '资源类型', 
189
         `ttl` varchar(20) NOT NULL DEFAULT '' COMMENT 'TTL', 
190
         `rr_class` varchar(12) NOT NULL DEFAULT '' COMMENT 'RECODR 类型', 
191
         `data` varchar(500) NOT NULL COMMENT '数据', 
192
         `status` varchar(10) DEFAULT NULL COMMENT '状态', 
193
         `gmt_create` datetime NOT NULL COMMENT '创建时间', 
194
         `gmt_modified` datetime DEFAULT NULL COMMENT '修改时间',
195
         PRIMARY KEY (`id`),
196
         KEY `rrdata` (`data`(383)) BLOCK_SIZE 16384 GLOBAL,
197
         KEY `rrtype` (`rr_type`) BLOCK_SIZE 16384 GLOBAL,
198
         KEY `owner` (`owner`) BLOCK_SIZE 16384 GLOBAL,
199
         KEY `cluster` (`cluster_name`) BLOCK_SIZE 16384 GLOBAL,
200
         KEY `zonename` (`zone_name`) BLOCK_SIZE 16384 GLOBAL,
201
         KEY `viewgroupname` (`view_group_name`) BLOCK_SIZE 16384 GLOBAL,
202
         KEY `domain_group_name` (`domain_group_name`) BLOCK_SIZE 16384 GLOBAL,
203
         KEY `idc_id` (`idc_id`) BLOCK_SIZE 16384 GLOBAL
204
         ) COMMENT = 'dns 内网集群资源记录表';
205
delete from dns_inner_resource_record_info WHERE cluster_name = "1";
206
set binlog_row_image='MINIMAL';
207
delete from dns_inner_resource_record_info WHERE cluster_name = "1";
208
drop table dns_inner_resource_record_info;
209

210