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
--source mysql_test/include/index_quick_major.inc
8
#case:int as idx
9
--disable_warnings
10
drop table if exists tbl1;
11
--enable_warnings
12
create table tbl1 (pk int,i1 int, v2 varchar(80), i3 char(20),i4 float, d4 datetime(6),i5 decimal(5,3),i6 bool, primary key(pk));
13
insert into tbl1 values(1,1,'abc12','1agc',1.25,'2014-01-01 12:00:00',23.23,1),
14
                       (2,10,'23abc12','4agc1',-0.25,'2015-11-01 12:00:00',-23.230,0),
15
                       (3,201,'xx23abc12','cagc1',-2.25,'2015-11-21 11:20:10',3.231,1),
16
                       (4,80,'zx23abc12','zcagc1',2.25,'2013-01-21 11:20:10',-3.231,1),
17
                       (5,53,'cx23abc12','rcagc1',109.25,'2016-01-22 11:20:10',-13.131,0),
18
                       (6,78,'tx23abc12','icagc1',-109.25,'2014-09-16 16:24:00',-13.531,1),
19
                       (7,400,'yx23abc12','ocagc1',85.85,'2014-09-17 16:20:21',-93.131,0),
20
                       (8,50,'vx23abc12','wcagc1',-85.85,'2014-09-10 16:20:21',93.131,1),
21
                       (9,610,'ukx23abc12','pecagc1',-1.85,'2014-09-19 16:20:21',99.131,0),
22
                       (10,42,'kx23abc12','ecagc1',-85.86,'2014-09-18 16:20:21',-99.131,1),
23
                       (11,null,null,null,null,null,null,null);
24
create unique index idx_1 on tbl1(i1);
25
--source mysql_test/include/check_all_idx_ok.inc
26
select date_sub(null, INTERVAL -6 DAY);  
27
select * from tbl1;
28
update  tbl1 set i1=i1-20 where pk<=11 limit 20;
29
update  tbl1 set i1=i1+20 where pk<=11 limit 20;
30
select * from tbl1;
31
update  tbl1 set v2=concat(v2,'abc') where i1<=100 order by i1 limit 10;
32
update  tbl1 set v2=substr(v2,1,10) where i1<=100 order by i1 limit 10;
33
select * from tbl1;
34
#update  tbl1 set i3=concat(v2,'abc') where i1 in (1,201,10,80,420,600) order by i1 asc limit 5;
35
#update  tbl1 set i3=substr(v2,1,10)  where i1 in (1,201,10,80,420,600) order by i1 asc limit 5;
36
update  tbl1 set i3=concat(v2,'abc') where i1 in (1,201,10,80,420,600)   limit 5;
37
update  tbl1 set i3=substr(v2,1,10)  where i1 in (1,201,10,80,420,600)  limit 5;
38
--disable_parsing
39
update  tbl1 set i4=i4*2;
40
update  tbl1 set i4=i4/2;
41
#update  tbl1 set d4=date_sub(d4, INTERVAL -6 DAY) ;
42
update  tbl1 set d4=date_sub(d4, INTERVAL -6 DAY) where d4 is not null;
43
#update  tbl1 set d4=date_add(d4, INTERVAL -6 DAY) ;
44
update  tbl1 set d4=date_add(d4, INTERVAL -6 DAY) where d4 is not null;
45
update  tbl1 set i5=i5+0.05;
46
update  tbl1 set i5=i5-0.05;
47
update  tbl1 set i6=i6 and 1;
48
select * from tbl1;
49
select * from tbl1 where i1>10 and i1<=1000;
50
select * from tbl1 where i1>10+1 and i1<1000; 
51
select * from tbl1 where i1 between 10+1 and 1000; 
52
prepare stmt1 from select * from tbl1 where i1>?+1 and i1<?-10;
53
set @a=10;
54
set @b=1000;
55
execute stmt1 using @a, @b;
56
set @a=10+1-1;
57
set @b=1000-1+1;
58
execute stmt1 using @a, @b;
59
select * from tbl1 where i1>10 and i1<1000-10; 
60
select * from tbl1 where i1>10+1 and i1<1000-10; 
61
select * from tbl1 where i1>1*10+1 and i1<1000; 
62
select * from tbl1 where i1>10 and i1<=1000*2; 
63
select * from tbl1 where i1>10*2+3 and i1<=1000*2; 
64
select * from tbl1 where i1>1*50/5+1 and i1<=1000*2/2+1; 
65
select * from tbl1 where i1>50 and i1<=1000*2/2+1; 
66
select * from tbl1 where i1>50*1+3 and i1<=1000; 
67
select * from tbl1 where i1>(19 mod 10)+1 and i1<=1000; 
68
select * from tbl1 where i1>(19 mod 10)+1 and i1<=(1000 mod 1001) +10; 
69
prepare stmt1 from select * from tbl1 where i1>(? mod 10) and i1<(? mod 1005)-10;
70
set @a=10;
71
set @b=1001;
72
execute stmt1 using @a, @b;
73
set @a=10+1-1;
74
set @b=1001-1+1;
75
execute stmt1 using @a, @b;
76
select * from tbl1 where i1>10 and i1<1000-10; 
77
select * from tbl1 where i1>10+1 and i1<1000-10; 
78
select * from tbl1 where i1>1*10+1 and i1<1000; 
79
select * from tbl1 where i1>10 and i1<=1000*2; 
80
select * from tbl1 where i1>10*2+3 and i1<=1000*2; 
81
update tbl1 set i1=i1+1-1  where i1>10*2+3 and i1<=1000*2; 
82
select * from tbl1 where i1>1*50/5+1 and i1<=1000*2/2+1; 
83
select * from tbl1 where i1>50 and i1<=1000*2/2+1; 
84
select * from tbl1 where i1>50*1+3 and i1<=1000; 
85
select * from tbl1 where i1>(19 mod 10)+1 and i1<=1000; 
86
select * from tbl1 where i1>(19 mod 10)+1 and i1<=(1000 mod 1001) +10; 
87
select * from tbl1 where i1>10 and i1<=(1000 mod 1001) +10; 
88
select * from tbl1 where i1>10+0 and i1<=(1000*1001) +10; 
89
select * from tbl1 where i1>=10+null and i1<=(1000*1001) +10; 
90
select * from tbl1 where i1>=10+1 and i1<=(1000*2) +null; 
91
select * from tbl1 where i1>=10+1 and i1<=(1000*2) or i1 is null; 
92
update tbl1 set i1=i1+1-1 where i1>=10+1 and i1<=(1000*2) or i1 is null; 
93
--error 1064
94
select * from tbl1 where i1>=10+1 and i1<=(1000*2) or i1 is (null+1); 
95
--error 5083
96
select * from tbl1 where i1>=10+1 and i1<=(1000*2) or i1 is null+1; 
97
select * from tbl1 where i1>=10+1 and i1<=(1000*2) and i1 is null; 
98
select * from tbl1 where i1>=10+1 and i1<=(1000*2) and i1 is not  null; 
99
update tbl1 set i1=i1+100-100  where i1>=10+1 and i1<=(1000*2) and i1 is not  null; 
100
select * from tbl1 where i1>=10+1 and i1<=(100*2) or i1 = 610; 
101
select * from tbl1 where i1>=10+1 and i1<=(100*2) or i3 = 'cx23abc12'; 
102
select * from tbl1 where i1>=10+1 and i1<=(100*2) and i5  between 0 and 1000; 
103
prepare stmt1 from select * from tbl1 where i1>=?+1 and i1<=(?*2) and i5  between ? and ?;
104
set @a=10;
105
set @b=100;
106
set @c=0;
107
set @d=1000;
108
execute stmt1 using @a, @b,@c,@d;
109
set @a=10+1-1;
110
set @b=1000-1+1;
111
set @c=0+1000-1+1-1000;
112
set @d=1000-1+1;
113
execute stmt1 using @a, @b,@c,@d;
114
prepare stmt1 from update tbl1 set i1=i1+15-15 where i1>=?+1 and i1<=(?*2) and i5  between ? and ?;
115
set @a=10;
116
set @b=100;
117
set @c=0;
118
set @d=1000;
119
execute stmt1 using @a, @b,@c,@d;
120
set @a=10+1-1;
121
set @b=1000-1+1;
122
set @c=0+1000-1+1-1000;
123
set @d=1000-1+1;
124
execute stmt1 using @a, @b,@c,@d;
125
select * from tbl1 where i1>=10+1 and i1<=(100*10) and i5  between -100 and 1000;
126
select * from tbl1 where i1 between 10+1 and (100*10) and i5  between -100 and 1000;
127
select * from tbl1 where i1<=round(1000.5) and i1>=round(0.5);
128
update  tbl1  set i1=i1*2/2 where i1<=round(1000.5) and i1>=round(0.5);
129
select * from tbl1 where  i1>=0 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
130
select * from tbl1 where  i1>=EXTRACT(DAY FROM '2014-09-30 12:00:00' )-29 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
131
update tbl1 set i1=i1+1-1 where  i1>=EXTRACT(DAY FROM '2014-09-30 12:00:00' )-29 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
132
update tbl1 set i5=i5+1-1 where  i1>=EXTRACT(DAY FROM '2014-09-30 12:00:00' )-29 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
133
#case:more than noe cloumn idx
134
drop index idx_1 on tbl1;
135
create index idx_12 on tbl1(i1,i5);
136
--source mysql_test/include/check_all_idx_ok.inc
137
select * from tbl1 where i1>10 and i1<=1000;
138
select * from tbl1 where i1>10+1 and i1<1000; 
139
select * from tbl1 where i1>10 and i1<1000-10; 
140
select * from tbl1 where i1>10+1 and i1<1000-10; 
141
select * from tbl1 where i1>1*10+1 and i1<1000; 
142
select * from tbl1 where i1>10 and i1<=1000*2; 
143
select * from tbl1 where i1>10*2+3 and i1<=1000*2; 
144
select * from tbl1 where i1>10 and i1<=(1000 mod 1001) +10; 
145
select * from tbl1 where i1>10+0 and i1<=(1000*1001) +10; 
146
select * from tbl1 where i1>=10+null and i1<=(1000*1001) +10; 
147
select * from tbl1 where i1>=10+1 and i1<=(1000*2) +null; 
148
select * from tbl1 where i1>=10+1 and i1<=(1000*2) or i1 is null; 
149
select * from tbl1 where i1>=10+1 and i1<=(1000*2) and i1 is null; 
150
select * from tbl1 where i1>=10+1 and i1<=(1000*2) and i1 is not  null; 
151
select * from tbl1 where i1>=10+1 and i1<=(100*2) or i1 = 610; 
152
select * from tbl1 where i1>=10+1 and i1<=(100*2) or i3 = 'cx23abc12'; 
153
select * from tbl1 where i1>=10+1 and i1<=(100*2) and i5  between 0 and 1000; 
154
select * from tbl1 where i1>=10+1 and i1<=(100*10) and i5  between -100 and 1000;
155
select * from tbl1 where i1<=round(1000.5) and i1>=round(0.5);
156
select * from tbl1 where  i1>=0 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
157
select * from tbl1 where  i1>=EXTRACT(DAY FROM '2014-09-30 12:00:00' )-29 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
158
select * from tbl1 where i1 in(1,10,42,50,null,610,400);
159
select * from tbl1 where (i1,i4) in((1,1.25),(10,-0.25),(80,2.25),(42,-85.85));
160
select * from tbl1 where (i4,i1) in ((1.25,1),(-0.25,10),(-85.85,42),(2.25,80));
161
#case:more than noe cloumn idx
162
#drop index idx_1 on tbl1;
163
create index idx_15 on tbl1(i1,i5);
164
--source mysql_test/include/check_all_idx_ok.inc
165
select * from tbl1 where i1>10 and i1<=1000;
166
select * from tbl1 where i1>10+1 and i1<1000; 
167
select * from tbl1 where i1>10 and i1<1000-10; 
168
select * from tbl1 where i1>10+1 and i1<1000-10; 
169
select * from tbl1 where i1>1*10+1 and i1<1000; 
170
select * from tbl1 where i1>10 and i1<=1000*2; 
171
select * from tbl1 where i1>10*2+3 and i1<=1000*2; 
172
select * from tbl1 where i1>1*50/5+1 and i1<=1000*2/2+1; 
173
select * from tbl1 where i1>50 and i1<=1000*2/2+1; 
174
select * from tbl1 where i1>50*1+3 and i1<=1000; 
175
select * from tbl1 where i1>(19 mod 10)+1 and i1<=1000; 
176
select * from tbl1 where i1>(19 mod 10)+1 and i1<=(1000 mod 1001) +10; 
177
select * from tbl1 where i1>10 and i1<=(1000 mod 1001) +10; 
178
select * from tbl1 where i1>10+0 and i1<=(1000*1001) +10; 
179
select * from tbl1 where i1>=10+null and i1<=(1000*1001) +10; 
180
select * from tbl1 where i1>=10+1 and i1<=(1000*2) +null; 
181
select * from tbl1 where i1>=10+1 and i1<=(1000*2) or i1 is null; 
182
select * from tbl1 where i1>=10+1 and i1<=(1000*2) and i1 is null; 
183
select * from tbl1 where i1>=10+1 and i1<=(1000*2) and i1 is not  null; 
184
select * from tbl1 where i1>=10+1 and i1<=(100*2) or i1 = 610; 
185
select * from tbl1 where i1>=10+1 and i1<=(100*2) or i3 = 'cx23abc12'; 
186
select * from tbl1 where i1>=10+1 and i1<=(100*2) and i5  between 0 and 1000; 
187
select * from tbl1 where i1>=10+1 and i1<=(100*10) and i5  between -100 and 1000;
188
select * from tbl1 where i1>10+1 and i1<1000 and pk<=10;
189
select * from tbl1 where i1>-9999999999+1+0 and i1<10000000000-2+1-1+2-2+3-3+4-4+5-5+6-6+7-7+8-8+9-9+0-0+10-10 and pk<=10;
190
select * from tbl1 where i1>-9999999999+1+0 and i1<10000000000-2+1-1+2-2+3-3+4-4+5-5+6-6+7-7+8-8+9-9+0-0+10-10 ;
191
select * from tbl1 where i1>=10+1 and i1<=(100*10) and i5  between -100 and 1000;
192
prepare stmt1 from  select * from tbl1 where i1>=?+1 and i1<=(?*10) and i5  between ? and ?;                                                                                                        
193
set @a=10;
194
set @b=100;
195
set @c=-100;
196
set @d=1000;
197
execute stmt1 using @a, @b,@c,@d;
198
set @a=10+1-1+10-10+1-1+1-1+1-1+1-1;
199
set @b=100-1+1+10-10+10-10+1-1+10-10;
200
set @c=-100+1-1+1-1+1-1+1-1+2-2+3-3+4-4+5-5+6-6+7-7+8-8+9-9+10-10;
201
set @d=1000+1-1+1-1+1-1+1-1+2-2+3-3+4-4+5-5+6-6+7-7+8-8+9-9+10-10;
202
execute stmt1 using @a, @b,@c,@d;
203
select * from tbl1 where i1>=length('abc')-10 and i1<length('dagafdgafdgfg')+1000;
204
set @a='agafgfdgfdghfdhadfh';
205
set @b='adgfdagdfgggfgfdgfgfgfgfdgdfgadfghdfgfdg';
206
prepare stmt1 from  select * from tbl1 where i1>=length(?)-10 and i1<length(?)+1000;
207
execute stmt1 using @a, @b;
208
select * from tbl1 where (i1,i5) in ((1,23.23),(42,-99.131),(50,93.131),(610,99.131));
209
select * from tbl1 where (i5,i1) in ((23.23,1),(-99.131,42),(93.131,50),(99.131,610));
210
select * from tbl1 where (i1,i5,i4) in ((1,23.23,1.25),(610,99.131,-1.85),(42,-99.131,-85.85));
211
select * from tbl1 where (i4,i1,i5) in ((1.25,1,23.23),(-1.85,610,99.131),(-85.85,42,-99.131));
212
prepare stmt1 from  select * from tbl1 where (i4,i1,i5) in ((1.25,?,23.23),(-1.85,?,99.131),(?,42,?));  
213
set @a=-99.131;
214
set @b=610;
215
set @c=1;
216
set @d=-85.85;
217
execute stmt1 using @c,@b,@d,@a;
218
prepare stmt1 from  select * from tbl1 where i1>=?*?+?+? and i1<=?*?+?-?;  
219
set @a=-10;
220
set @b=2;
221
set @c=1;
222
set @d=-1;
223
set @e=100;
224
set @f=10;
225
set @g=2;
226
set @h=3;
227
execute stmt1 using @a,@b,@c,@d,@e,@f,@g,@h;
228
#test  update
229
update tbl1 set i1=i1+1-1 where  i1>=EXTRACT(DAY FROM '2014-09-30 12:00:00' )-29 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
230
update tbl1 set i5=i5+1-1 where  i1>=EXTRACT(DAY FROM '2014-09-30 12:00:00' )-29 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
231
update tbl1 set i5=round(i5) where  i1>=EXTRACT(DAY FROM '2014-09-30 12:00:00' )-29 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
232
update tbl1 set d4=date_add(d4, INTERVAL -6 hour) where  i1>=EXTRACT(DAY FROM '2014-09-30 12:00:00' )-29 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
233
select * from tbl1;
234
delete from  tbl1  where  i1>=EXTRACT(DAY FROM '2014-09-30 12:00:00' )-29 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
235
select *  from  tbl1  where  i1>=EXTRACT(DAY FROM '2014-09-30 12:00:00' )-29 and i1<=EXTRACT(DAY FROM '2014-09-30 12:00:00' )+600;
236
select * from tbl1;
237
--enable_parsing
238