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