oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: link.zt
5
# owner group: SQL4
6
# description: foobar
7
####TITLE: range update
8
--disable_warnings
9
drop table if exists t1;
10
--enable_warnings
11

12

13
####CASE: single rowkey, using rowkey, >,>=,<,<=
14
create table t1 (a int, b int, primary key (a));
15
insert into t1(a,b) values(1,1),(2,1),(3,1),(4,1);
16
update t1 set b=2 where a>0;
17
select * from t1;
18
update t1 set b=3 where a<5;
19
select * from t1;
20
update t1 set b=4 where a>1;
21
update t1 set b=5 where a<4;
22
select * from t1;
23
update t1 set b=6 where a>=1;
24
select * from t1;
25
update t1 set b=7 where a<=5;
26
select * from t1;
27
update t1 set b=8 where a>0 and a<2;
28
update t1 set b=9 where a>=2 and a<3;
29
update t1 set b=10 where a>=3 and a<=4;
30
select * from t1;
31
update t1 set b=11 where a=1 or a=2 or a=3 or a=4;
32
select * from t1;
33

34
####CASE: single rowkey, using non_rowkey, >, >=, <, <=
35
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
36
update t1 set b=b+1 where b>0;
37
select * from t1;
38
update t1 set b=b-1 where b<6;
39
select * from t1;
40
update t1 set b=5 where b>1;
41
update t1 set b=6 where b<5;
42
select * from t1;
43
update t1 set b=7 where b>=1;
44
select * from t1;
45
update t1 set b=8 where b<=7;
46
select * from t1;
47

48
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
49
update t1 set b=10 where b>0 and b<2;
50
update t1 set b=11 where b>=2 and b<3;
51
update t1 set b=12 where b>=3 and b<=4;
52
select * from t1;
53

54
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
55
update t1 set b=11 where b=1 or b=2 or b=3 or b=4;
56
select * from t1;
57

58
####CASE: single rowkey: using rowkey + non_rowkey, >, >=, <,<=, or
59
--disable_warnings
60
drop table if exists t1;
61
--enable_warnings
62
create table t1(a int, b int, c int, primary key(a));
63
replace into t1(a,b) values(1,1),(2,2),(3,1),(4,2);
64
update t1 set c=1 where a>0 and b>1;
65
update t1 set c=2 where a<5 and b<2;
66
select * from t1;
67

68
update t1 set c=3 where a>=0 and b<=1;
69
update t1 set c=4 where a<=5 and b>=2;
70
select * from t1;
71

72
update t1 set c=5 where a=0 or a=1 or a=2 or b=1;
73
update t1 set c=6 where b=2 or a=1 or a=2;
74
select * from t1;
75

76
replace into t1(a,b,c) values(1,1,NULL),(2,2,NULL),(3,3,NULL),(4,4,NULL);
77
update t1 set c=1 where a<=1 or b>=4;
78
update t1 set c=2 where a>1 or b<4;
79
select * from t1;
80

81
####CASE: two rowkeys, using rowkey, >, >=, <, <=
82
--disable_warnings
83
drop table if exists t1;
84
--enable_warnings
85
create table t1(a int, b int, c int, d int, primary key(a,b));
86
insert into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
87
update t1 set c=c+1, d=d+1 where (a,b) > (0,0);
88
select * from t1;
89
update t1 set c=c+1, d=d+1 where (a,b) < (4,4);
90
select * from t1;
91
update t1 set c=c+1, d=d+1 where (a,b) >= (1,1);
92
select * from t1;
93
update t1 set c=c+1, d=d+1 where (a,b) <= (1,1);
94
select * from t1;
95
update t1 set c=c+1, d=d+1 where (a,b) in ((NULL,NULL),(0,0),(1,1),(2,2),(3,3),(4,4));
96
select * from t1;
97
update t1 set c=c+1, d=d+1 where (a,b)=(1,1) or (a,b)=(2,2) or (a,b)=(3,3);
98
select * from t1;
99
update t1 set c=1, d=1 where (a,b) in ((1,1),(2,2),(3,3));
100
update t1 set c=c+1, d=d+1 where (a,b,c,d) >= (1,1,1,1);
101
select * from t1;
102
update t1 set c=c+1, d=d+1 where (a,b,c,d) <= (3,3,3,3);
103
select * from t1;
104
update t1 set c=c+1, d=d+1 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);
105
select * from t1;
106

107
####CASE: two rowkeys, using one of rowkey, =, >, >=, <, <=
108
--disable_warnings
109
drop table if exists t1;
110
--enable_warnings
111
create table t1(a int, b int, c int, d int, primary key(a,b));
112
insert into t1(a,b,c,d) values(1,1,1,1),(1,2,1,1),(1,3,1,1);
113

114
update t1 set c=c+1, d=d+1 where a = 1;
115
select * from t1;
116
update t1 set c=c+1, d=d+1 where a > 0;
117
update t1 set c=c+1, d=d+1 where a >=1 ;
118
select * from t1;
119
update t1 set c=c+1, d=d+1 where a < 4;
120
select * from t1;
121
update t1 set c=c+1, d=d+1 where a <= 1;
122
select * from t1;
123
update t1 set c=c+1, d=d+1 where a=1 or a=2 or a=3;
124
select * from t1;
125
update t1 set c=c+1, d=d+1 where (a=1 or a=2 or a=3) and (b=1 or b=2);
126
select * from t1;
127
update t1 set c=c+1, d=d+1 where a>=1 and b>=2;
128
select * from t1;
129

130
####CASE:two rowkeys, update using rowkey + non_rowkey, >, >=, <, <=, =
131
--disable_warnings
132
drop table if exists t1;
133
--enable_warnings
134
create table t1(a int, b int, c int, d int, primary key(a,b));
135
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,2,1),(3,3,3,1);
136

137
update t1 set d=d+1 where (a,b)>=(1,1) and c=1;
138
select * from t1;
139
update t1 set d=d+1 where (a,b)>=(1,1) and c=3;
140
select * from t1;
141

142
update t1 set d=d+1 where (a=1 or a=2 or a=3) and c>=1;
143
select * from t1;
144
update t1 set d=d+1 where (a=1 or a=2 or a=3) and c<1;
145
select * from t1;
146

147
update t1 set d=d+1 where (a=1 or a=2 or a=3) and (b=2 or b=3);
148
select * from t1;
149
update t1 set d=d+1 where (a=1 or a=2 or a=3) and (b=2 or b=3) and c=3;
150
select * from t1;
151

152
update t1 set c=c+1, d=d+1 where a>=0 and a<=3 and b>1 and b<3;
153
select * from t1;
154
update t1 set c=c+1, d=d+1 where (c,d)>=(1,1);
155
select * from t1;
156
update t1 set c=c+1, d=d+1 where (b,a) > (1,1);
157
select * from t1;
158

159
####CASE:multi rowkeys
160
--disable_warnings
161
drop table if exists t1, t2, t3;
162
--enable_warnings
163
#int
164
create table t1(a int, b int, c int, d int, primary key(a,b,c));
165
insert into t1(a,b,c) values (1,1,1),(2,2,2),(3,3,3);
166
update t1 set d=1 where (a,b,c)>(0,0,0) and (a,b,c)<=(3,3,3);
167
select * from t1;
168
#varchar
169
create table t2(a varchar(1024), b varchar(1024), c varchar(1024), d varchar(1024), primary key(a,b,c));
170
insert into t2(a,b,c) values ('a','a','a'),('b','b','b'),('c','c','c');
171
update t2 set d='a' where (a,b,c)>=('a','a','b') and (a,b,c)<('b','b','c');
172
select * from t2;
173
#timestamp
174
create table t3(a int, b varchar(1024), c  timestamp(6) default "2012-01-01 12:00:00", d int, primary key(a,b,c));
175
insert into t3(a,b,c) values (1,'a','2014-02-17'),(2,'b','2014-02-17'),(3,'c','2014-02-18');
176
update t3 set d=1 where (a,b,c)>=(1,'a','2014-02-17 00:00:00') and (a,b,c)<=(2,'b','2014-02-18');
177
select * from t3;
178

179
--disable_warnings
180
drop table if exists t1, t2, t3;
181
--enable_warnings
182