| blob | 704215336305cc75823e510b6862eee20b13693e |
1 echo "----------Table having single field f1 and index on t1(f1)--------------";
2 echo create table t1(f1 int);
3 create table t1(f1 int);
4 echo create index idx1 on t1(f1);
5 create index idx1 on t1(f1);
6 echo insert into t1 values(10);
7 insert into t1 values(10);
8 echo insert into t1 values(21);
9 insert into t1 values(21);
10 echo insert into t1 values(32);
11 insert into t1 values(32);
12 echo insert into t1 values(123);
13 insert into t1 values(123);
14 echo insert into t1 values(-2147483648);
15 insert into t1 values(-2147483648);
16 echo insert into t1 values(2147483647);
17 insert into t1 values(2147483647);
18 echo select * from t1 where f1>-5;
19 select * from t1 where f1>-5;
20 echo select * from t1 where f1<-10;
21 select * from t1 where f1<-10;
22 echo select f1 from t1 where f1 in (10,30,123);
23 select f1 from t1 where f1 in (10,30,123);
24 echo select * from t1;
25 select * from t1;
26 echo update t1 set f1=25 where f1=21;
27 update t1 set f1=25 where f1=21;
28 echo select * from t1;
29 select * from t1;
30 echo update t1 set f1=1010101 where f1 in(32,10,40);
31 update t1 set f1=1010101 where f1 in(32,10,40);
32 echo select * from t1;
33 select * from t1;
34 echo update t1 set f1=987654 where f1 between 25 and 200;
35 update t1 set f1=987654 where f1 between 25 and 200;
36 echo select * from t1;
37 select * from t1;
38 echo delete from t1 where f1=2147483647;
39 delete from t1 where f1=2147483647;
40 echo select * from t1;
41 select * from t1;
42 echo delete from t1 where f1 between 25 and 6000000;
43 delete from t1 where f1 between 25 and 6000000;
44 echo select * from t1;
45 select * from t1;
46 echo delete from t1;
47 delete from t1;
48 echo select * from t1;
49 select * from t1;
50 echo drop table t1;
51 drop table t1;
53 echo "-----Table having two fields f1 int,f2 int and index on t1(f1)-----";
54 echo create table t1(f1 int,f2 int);
55 create table t1(f1 int,f2 int);
56 echo create index idx1 on t1(f1);
57 create index idx1 on t1(f1);
58 echo insert into t1 values(10,2147483647);
59 insert into t1 values(10,2147483647);
60 echo insert into t1 values(21,-2147483648);
61 insert into t1 values(21,-2147483648);
62 echo insert into t1 values(32,1000000000);
63 insert into t1 values(32,1000000000);
64 echo insert into t1 values(123,999999999);
65 insert into t1 values(123,999999999);
66 echo insert into t1 values(-2147483648,0);
67 insert into t1 values(-2147483648,0);
68 echo insert into t1 values(2147483647,-2147483648);
69 insert into t1 values(2147483647,-2147483648);
70 echo insert into t1 values(123,9);
71 insert into t1 values(123,9);
72 echo select * from t1;
73 select * from t1;
74 echo select * from t1 where f1>-5;
75 select * from t1 where f1>-5;
76 echo select * from t1 where f1<-10;
77 select * from t1 where f1<-10;
78 echo select f1 from t1 where f1 in (10,30,123);
79 select f1 from t1 where f1 in (10,30,123);
80 echo select * from t1;
81 select * from t1;
82 echo update t1 set f1=25 where f1=21;
83 update t1 set f1=25 where f1=21;
84 echo select * from t1;
85 select * from t1;
86 echo update t1 set f1=21 where f1=25 and f2=-2147483648;
87 update t1 set f1=21 where f1=25 and f2=-2147483648;
88 echo select * from t1;
89 select * from t1;
90 echo update t1 set f1=1010101 where f1 in(32,10,40);
91 update t1 set f1=1010101 where f1 in(32,10,40);
92 echo select * from t1;
93 select * from t1;
94 echo update t1 set f1=7777 where f1=1010101 and f2 in(1000000000,999999999);
95 update t1 set f1=7777 where f1=1010101 and f2 in(1000000000,999999999);
96 echo select * from t1;
97 select * from t1;
98 echo update t1 set f1=2020202 where f2 in(1000000000,999999999) and f1>=7777;
99 update t1 set f1=2020202 where f2 in(1000000000,999999999) and f1>=7777;
100 echo select * from t1;
101 select * from t1;
102 echo update t1 set f1=987654 where f1 between 25 and 200;
103 update t1 set f1=987654 where f1 between 25 and 200;
104 echo select * from t1;
105 select * from t1;
106 echo delete from t1 where f1=2147483647;
107 delete from t1 where f1=2147483647;
108 echo select * from t1;
109 select * from t1;
110 echo delete from t1 where f1 between 25 and 6000000;
111 delete from t1 where f1 between 25 and 6000000;
112 echo select * from t1;
113 select * from t1;
114 echo delete from t1;
115 delete from t1;
116 echo select * from t1;
117 select * from t1;
118 echo drop table t1;
119 drop table t1;
121 echo "--Table having 5 fields f1,f2,f3,f4,f5(all int) index on t1(f1,f2)--";
122 echo create table t1(f1 int,f2 int,f3 int,f4 int,f5 int);
123 create table t1(f1 int,f2 int,f3 int,f4 int,f5 int);
124 echo create index idx1 on t1(f1,f2);
125 create index idx1 on t1(f1,f2);
126 echo insert into t1 values(11,222,3333,44444,555555);
127 insert into t1 values(11,222,3333,44444,555555);
128 echo insert into t1 values(12,123,1234,12345,123456);
129 insert into t1 values(12,123,1234,12345,123456);
130 echo insert into t1 values(234,4567,56789,1234,4321);
131 insert into t1 values(234,4567,56789,1234,4321);
132 echo insert into t1 values(-2147483647,1,101,212,2147483647);
133 insert into t1 values(-2147483647,1,101,212,2147483647);
134 echo insert into t1 values(101,12121,0,32123,78987);
135 insert into t1 values(101,12121,0,32123,78987);
136 echo insert into t1 values(12,123,1000,1111,1);
137 insert into t1 values(12,123,1000,1111,1);
138 echo select * from t1;
139 select * from t1;
140 echo select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
141 select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
142 echo select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
143 select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
144 echo select * from t1 where f1<-1 and f2>=1 or f3=101;
145 select * from t1 where f1<-1 and f2>=1 or f3=101;
146 echo select * from t1 where f1<-1 and f2>=1 or f3=1234;
147 select * from t1 where f1<-1 and f2>=1 or f3=1234;
148 echo select * from t1;
149 select * from t1;
150 echo update t1 set f1=22 where f1=11;
151 update t1 set f1=22 where f1=11;
152 echo select * from t1;
153 select * from t1;
154 echo update t1 set f1=11 where f1=22 and f2=222;
155 update t1 set f1=11 where f1=22 and f2=222;
156 echo select * from t1;
157 select * from t1;
158 echo update t1 set f1=10 where f1 in(234,10);
159 update t1 set f1=10 where f1 in(234,10);
160 echo select * from t1;
161 select * from t1;
162 echo update t1 set f2=100 where f1 in(12,10);
163 update t1 set f2=100 where f1 in(12,10);
164 echo select * from t1;
165 select * from t1;
166 echo update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
167 update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
168 echo select * from t1;
169 select * from t1;
170 echo update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
171 update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
172 echo select * from t1;
173 select * from t1;
174 echo update t1 set f2=101,f2=12121 where f3=0;
175 update t1 set f2=101,f2=12121 where f3=0;
176 echo select * from t1;
177 select * from t1;
178 echo update t1 set f1=2020202 where f1 in(-2147483647,2147483647) or f1>=7777;
179 update t1 set f1=2020202 where f1 in(-2147483647,2147483647) or f1>=7777;
180 echo select * from t1;
181 select * from t1;
182 echo update t1 set f1=987654 where f1 between 25 and 200;
183 update t1 set f1=987654 where f1 between 25 and 200;
184 echo select * from t1;
185 select * from t1;
186 echo delete from t1 where f1=2147483647;
187 delete from t1 where f1=2147483647;
188 echo select * from t1;
189 select * from t1;
190 echo delete from t1 where f1 between 25 and 6000000;
191 delete from t1 where f1 between 25 and 6000000;
192 echo select * from t1;
193 select * from t1;
194 echo delete from t1;
195 delete from t1;
196 echo select * from t1;
197 select * from t1;
198 echo drop table t1;
199 drop table t1;
201 echo "--Table having 5 fields f1,f2,f3,f4,f5(all int) index on t1(f1,f3)--";
202 echo create table t1(f1 int,f2 int,f3 int,f4 int,f5 int);
203 create table t1(f1 int,f2 int,f3 int,f4 int,f5 int);
204 echo create index idx1 on t1(f1,f3);
205 create index idx1 on t1(f1,f3);
206 echo insert into t1 values(11,222,3333,44444,555555);
207 insert into t1 values(11,222,3333,44444,555555);
208 echo insert into t1 values(12,123,1234,12345,123456);
209 insert into t1 values(12,123,1234,12345,123456);
210 echo insert into t1 values(234,4567,56789,1234,4321);
211 insert into t1 values(234,4567,56789,1234,4321);
212 echo insert into t1 values(-2147483648,1,101,212,2147483647);
213 insert into t1 values(-2147483648,1,101,212,2147483647);
214 echo insert into t1 values(101,12121,0,32123,78987);
215 insert into t1 values(101,12121,0,32123,78987);
216 echo insert into t1 values(12,100,1234,1111,1);
217 insert into t1 values(12,100,1234,1111,1);
218 echo select * from t1;
219 select * from t1;
220 echo select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
221 select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
222 echo select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
223 select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
224 echo select * from t1 where f1<-1 and f2>=1 or f3=101;
225 select * from t1 where f1<-1 and f2>=1 or f3=101;
226 echo select * from t1 where f1<-1 and f2>=1 or f3=1234;
227 select * from t1 where f1<-1 and f2>=1 or f3=1234;
228 echo select * from t1;
229 select * from t1;
230 echo update t1 set f1=22 where f1=11;
231 update t1 set f1=22 where f1=11;
232 echo select * from t1;
233 select * from t1;
234 echo update t1 set f1=11 where f1=22 and f2=222;
235 update t1 set f1=11 where f1=22 and f2=222;
236 echo select * from t1;
237 select * from t1;
238 echo update t1 set f1=10 where f1 in(234,10);
239 update t1 set f1=10 where f1 in(234,10);
240 echo select * from t1;
241 select * from t1;
242 echo update t1 set f2=100 where f1 in(12,10);
243 update t1 set f2=100 where f1 in(12,10);
244 echo select * from t1;
245 select * from t1;
246 echo update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
247 update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
248 echo select * from t1;
249 select * from t1;
250 echo update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
251 update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
252 echo select * from t1;
253 select * from t1;
254 echo update t1 set f2=101,f2=12121 where f3=0;
255 update t1 set f2=101,f2=12121 where f3=0;
256 echo select * from t1;
257 select * from t1;
258 echo update t1 set f1=2020202 where f1 in(-2147483647,2147483647) or f1>=7777;
259 update t1 set f1=2020202 where f1 in(-2147483647,2147483647) or f1>=7777;
260 echo select * from t1;
261 select * from t1;
262 echo update t1 set f1=987654 where f1 between 25 and 200 and f3>=4000;
263 update t1 set f1=987654 where f1 between 25 and 200 and f3>=4000;
264 echo select * from t1;
265 select * from t1;
266 echo delete from t1 where f1=-2147483648 or f3=1234;
267 delete from t1 where f1=-2147483648 or f3=1234;
268 echo select * from t1;
269 select * from t1;
270 echo delete from t1 where f1 between 25 and 6000000;
271 delete from t1 where f1 between 25 and 6000000;
272 echo select * from t1;
273 select * from t1;
274 echo delete from t1;
275 delete from t1;
276 echo select * from t1;
277 select * from t1;
278 echo drop table t1;
279 drop table t1;
282 echo "--Table having 5 fields f1,f2,f3,f4,f5(all int) index on t1(f1,f3,f2)--";
283 echo create table t1(f1 int,f2 int,f3 int,f4 int,f5 int);
284 create table t1(f1 int,f2 int,f3 int,f4 int,f5 int);
285 echo create index idx1 on t1(f1,f3,f2);
286 create index idx1 on t1(f1,f3,f2);
287 echo insert into t1 values(11,222,3333,44444,555555);
288 insert into t1 values(11,222,3333,44444,555555);
289 echo insert into t1 values(12,123,1234,12345,123456);
290 insert into t1 values(12,123,1234,12345,123456);
291 echo insert into t1 values(234,4567,56789,1234,4321);
292 insert into t1 values(234,4567,56789,1234,4321);
293 echo insert into t1 values(-2147483648,1,101,212,2147483647);
294 insert into t1 values(-2147483648,1,101,212,2147483647);
295 echo insert into t1 values(101,12121,0,32123,78987);
296 insert into t1 values(101,12121,0,32123,78987);
297 echo insert into t1 values(12,123,1234,1111,1);
298 insert into t1 values(12,123,1234,1111,1);
299 echo select * from t1;
300 select * from t1;
301 echo select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
302 select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
303 echo select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
304 select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
305 echo select * from t1 where f1<-1 and f2>=1 or f3=101;
306 select * from t1 where f1<-1 and f2>=1 or f3=101;
307 echo select * from t1 where f1<-1 and f2>=1 or f3=1234;
308 select * from t1 where f1<-1 and f2>=1 or f3=1234;
309 echo select * from t1;
310 select * from t1;
311 echo update t1 set f1=22 where f1=11;
312 update t1 set f1=22 where f1=11;
313 echo select * from t1;
314 select * from t1;
315 echo update t1 set f1=11 where f1=22 and f2=222;
316 update t1 set f1=11 where f1=22 and f2=222;
317 echo select * from t1;
318 select * from t1;
319 echo update t1 set f1=10 where f1 in(234,10);
320 update t1 set f1=10 where f1 in(234,10);
321 echo select * from t1;
322 select * from t1;
323 echo update t1 set f2=100 where f1 in(12,10);
324 update t1 set f2=100 where f1 in(12,10);
325 echo select * from t1;
326 select * from t1;
327 echo update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
328 update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
329 echo select * from t1;
330 select * from t1;
331 echo update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
332 update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
333 echo select * from t1;
334 select * from t1;
335 echo update t1 set f2=101,f2=12121 where f3=0;
336 update t1 set f2=101,f2=12121 where f3=0;
337 echo select * from t1;
338 select * from t1;
339 echo update t1 set f1=2020202 where f1 in(-2147483647,2147483647) and f2=1 and f3=101;
340 update t1 set f1=2020202 where f1 in(-2147483647,2147483647) and f2=1 and f3=101;
341 echo select * from t1;
342 select * from t1;
343 echo update t1 set f1=2020202 where f1 in(-2147483647,2147483647) or f1>=7777;
344 update t1 set f1=2020202 where f1 in(-2147483647,2147483647) or f1>=7777;
345 echo select * from t1;
346 select * from t1;
347 echo update t1 set f1=987654 where f1 between 25 and 200 and f3>=4000;
348 update t1 set f1=987654 where f1 between 25 and 200 and f3>=4000;
349 echo select * from t1;
350 select * from t1;
351 echo delete from t1 where f1=-2147483648 or f3=1234;
352 delete from t1 where f1=-2147483648 or f3=1234;
353 echo select * from t1;
354 select * from t1;
355 echo delete from t1 where f1 between 25 and 6000000;
356 delete from t1 where f1 between 25 and 6000000;
357 echo select * from t1;
358 select * from t1;
359 echo delete from t1;
360 delete from t1;
361 echo select * from t1;
362 select * from t1;
363 echo drop table t1;
364 drop table t1;
366 echo "--Table having 5 fields f1,f2,f3,f4,f5(all int) index on t1(f5,f1,f3,f2)--";
367 echo create table t1(f1 int,f2 int,f3 int,f4 int,f5 int);
368 create table t1(f1 int,f2 int,f3 int,f4 int,f5 int);
369 echo create index idx1 on t1(f5,f1,f3,f2);
370 create index idx1 on t1(f5,f1,f3,f2);
371 echo insert into t1 values(11,222,3333,44444,555555);
372 insert into t1 values(11,222,3333,44444,555555);
373 echo insert into t1 values(12,123,1234,12345,123456);
374 insert into t1 values(12,123,1234,12345,123456);
375 echo insert into t1 values(234,4567,56789,1234,4321);
376 insert into t1 values(234,4567,56789,1234,4321);
377 echo insert into t1 values(-2147483648,1,101,212,2147483647);
378 insert into t1 values(-2147483648,1,101,212,2147483647);
379 echo insert into t1 values(101,12121,0,32123,78987);
380 insert into t1 values(101,12121,0,32123,78987);
381 echo insert into t1 values(12,123,1234,1111,1);
382 insert into t1 values(12,123,1234,1111,1);
383 echo select * from t1;
384 select * from t1;
385 echo select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
386 select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
387 echo select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
388 select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
389 echo select * from t1 where f1<-1 and f2>=1 or f3=101;
390 select * from t1 where f1<-1 and f2>=1 or f3=101;
391 echo select * from t1 where f1<-1 and f2>=1 or f3=1234;
392 select * from t1 where f1<-1 and f2>=1 or f3=1234;
393 echo select * from t1;
394 select * from t1;
395 echo update t1 set f1=22 where f1=11;
396 update t1 set f1=22 where f1=11;
397 echo select * from t1;
398 select * from t1;
399 echo update t1 set f1=11 where f1=22 and f2=222;
400 update t1 set f1=11 where f1=22 and f2=222;
401 echo select * from t1;
402 select * from t1;
403 echo update t1 set f1=10 where f1 in(234,10);
404 update t1 set f1=10 where f1 in(234,10);
405 echo select * from t1;
406 select * from t1;
407 echo update t1 set f2=100 where f1 in(12,10);
408 update t1 set f2=100 where f1 in(12,10);
409 echo select * from t1;
410 select * from t1;
411 echo update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
412 update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
413 echo select * from t1;
414 select * from t1;
415 echo update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
416 update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
417 echo select * from t1;
418 select * from t1;
419 echo update t1 set f1=2020202 where f1 in(-2147483647,2147483647) and f2=1 and f3=101;
420 update t1 set f1=2020202 where f1 in(-2147483647,2147483647) and f2=1 and f3=101;
421 echo select * from t1;
422 select * from t1;
423 echo update t1 set f1=2020202 where f1 in(-2147483647,2147483647) or f1>=7777;
424 update t1 set f1=2020202 where f1 in(-2147483647,2147483647) or f1>=7777;
425 echo select * from t1;
426 select * from t1;
427 echo delete from t1 where f1=-2147483648 or f3=1234;
428 delete from t1 where f1=-2147483648 or f3=1234;
429 echo select * from t1;
430 select * from t1;
431 echo delete from t1 where f1 between 25 and 6000000;
432 delete from t1 where f1 between 25 and 6000000;
433 echo select * from t1;
434 select * from t1;
435 echo delete from t1;
436 delete from t1;
437 echo select * from t1;
438 select * from t1;
439 echo drop table t1;
440 drop table t1;