?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
adding test scripts
[csql.git] / test / tools / csql / exp.test034a.ksh
blob8e38f4ce8962233aa7c9f3c237da226a8fe5889f
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 Statement Executed
4 echo create index idx1 on t1(f1);
5 Statement Executed
6 echo insert into t1 values(10,2147483647);
7 Statement Executed: Rows Affected = 1
8 echo insert into t1 values(21,-2147483648);
9 Statement Executed: Rows Affected = 1
10 echo insert into t1 values(32,1000000000);
11 Statement Executed: Rows Affected = 1
12 echo insert into t1 values(123,999999999);
13 Statement Executed: Rows Affected = 1
14 echo insert into t1 values(-2147483648,0);
15 Statement Executed: Rows Affected = 1
16 echo insert into t1 values(2147483647,-2147483648);
17 Statement Executed: Rows Affected = 1
18 echo insert into t1 values(123,9);
19 Statement Executed: Rows Affected = 1
20 echo select * from t1;
21 ---------------------------------------------------------
22 t1.f1 t1.f2
23 ---------------------------------------------------------
24 10 2147483647
25 21 -2147483648
26 32 1000000000
27 123 999999999
28 -2147483648 0
29 2147483647 -2147483648
30 123 9
31
32 echo select * from t1 where f1>-5;
33 ---------------------------------------------------------
34 t1.f1 t1.f2
35 ---------------------------------------------------------
36 10 2147483647
37 21 -2147483648
38 32 1000000000
39 123 999999999
40 2147483647 -2147483648
41 123 9
42
43 echo select * from t1 where f1<-10;
44 ---------------------------------------------------------
45 t1.f1 t1.f2
46 ---------------------------------------------------------
47 -2147483648 0
48
49 echo select f1 from t1 where f1 in (10,30,123);
50 ---------------------------------------------------------
51 f1
52 ---------------------------------------------------------
53 10
54 123
55 123
56
57 echo select * from t1;
58 ---------------------------------------------------------
59 t1.f1 t1.f2
60 ---------------------------------------------------------
61 10 2147483647
62 21 -2147483648
63 32 1000000000
64 123 999999999
65 -2147483648 0
66 2147483647 -2147483648
67 123 9
68
69 echo select * from t1 where f1 between 25 and 6000000;
70 ---------------------------------------------------------
71 t1.f1 t1.f2
72 ---------------------------------------------------------
73 32 1000000000
74 123 999999999
75 123 9
76
77 echo select * from t1 where f1 between 6000000 and 25;
78 ---------------------------------------------------------
79 t1.f1 t1.f2
80 ---------------------------------------------------------
81
82 echo update t1 set f1=25 where f1=21;
83 Statement Executed: Rows Affected = 1
84 echo select * from t1;
85 ---------------------------------------------------------
86 t1.f1 t1.f2
87 ---------------------------------------------------------
88 10 2147483647
89 25 -2147483648
90 32 1000000000
91 123 999999999
92 -2147483648 0
93 2147483647 -2147483648
94 123 9
95
96 echo update t1 set f1=21 where f1=25 and f2=-2147483648;
97 Statement Executed: Rows Affected = 1
98 echo select * from t1;
99 ---------------------------------------------------------
100 t1.f1 t1.f2
101 ---------------------------------------------------------
102 10 2147483647
103 21 -2147483648
104 32 1000000000
105 123 999999999
106 -2147483648 0
107 2147483647 -2147483648
108 123 9
109
110 echo update t1 set f1=1010101 where f1 in(32,10,40);
111 Statement Executed: Rows Affected = 2
112 echo select * from t1;
113 ---------------------------------------------------------
114 t1.f1 t1.f2
115 ---------------------------------------------------------
116 1010101 2147483647
117 21 -2147483648
118 1010101 1000000000
119 123 999999999
120 -2147483648 0
121 2147483647 -2147483648
122 123 9
123
124 echo update t1 set f1=7777 where f1=1010101 and f2 in(1000000000,999999999);
125 Statement Executed: Rows Affected = 1
126 echo select * from t1;
127 ---------------------------------------------------------
128 t1.f1 t1.f2
129 ---------------------------------------------------------
130 1010101 2147483647
131 21 -2147483648
132 7777 1000000000
133 123 999999999
134 -2147483648 0
135 2147483647 -2147483648
136 123 9
137
138 echo update t1 set f1=2020202 where f2 in(1000000000,999999999) and f1>=7777;
139 Statement Executed: Rows Affected = 1
140 echo select * from t1;
141 ---------------------------------------------------------
142 t1.f1 t1.f2
143 ---------------------------------------------------------
144 1010101 2147483647
145 21 -2147483648
146 2020202 1000000000
147 123 999999999
148 -2147483648 0
149 2147483647 -2147483648
150 123 9
151
152 echo update t1 set f1=987654 where f1 between 25 and 200;
153 Statement Executed: Rows Affected = 2
154 echo select * from t1;
155 ---------------------------------------------------------
156 t1.f1 t1.f2
157 ---------------------------------------------------------
158 1010101 2147483647
159 21 -2147483648
160 2020202 1000000000
161 987654 999999999
162 -2147483648 0
163 2147483647 -2147483648
164 987654 9
165
166 echo delete from t1 where f1=2020202;
167 Statement Executed: Rows Affected = 1
168 echo select * from t1;
169 ---------------------------------------------------------
170 t1.f1 t1.f2
171 ---------------------------------------------------------
172 1010101 2147483647
173 21 -2147483648
174 987654 999999999
175 -2147483648 0
176 2147483647 -2147483648
177 987654 9
178
179 echo delete from t1 where f1 between 25 and 6000000;
180 Statement Executed: Rows Affected = 3
181 echo select * from t1;
182 ---------------------------------------------------------
183 t1.f1 t1.f2
184 ---------------------------------------------------------
185 21 -2147483648
186 -2147483648 0
187 2147483647 -2147483648
188
189 echo delete from t1;
190 Statement Executed: Rows Affected = 3
191 echo select * from t1;
192 ---------------------------------------------------------
193 t1.f1 t1.f2
194 ---------------------------------------------------------
195
196 echo drop table t1;
197 Statement Executed
198 echo "--Table having 5 fields f1,f2,f3,f4,f5(Different int) index on t1(f1,f2)--";
199 echo create table t1(f1 bigint,f2 integer,f3 int,f4 integer,f5 bigint);
200 Statement Executed
201 echo create index idx1 on t1(f1,f2);
202 Statement Executed
203 echo insert into t1 values(11,222,3333,44444,555555);
204 Statement Executed: Rows Affected = 1
205 echo insert into t1 values(12,123,1234,12345,123456);
206 Statement Executed: Rows Affected = 1
207 echo insert into t1 values(234,4567,56789,1234,4321);
208 Statement Executed: Rows Affected = 1
209 echo insert into t1 values(-2147483648,1,101,212,2147483647);
210 Statement Executed: Rows Affected = 1
211 echo insert into t1 values(101,12121,0,32123,78987);
212 Statement Executed: Rows Affected = 1
213 echo insert into t1 values(12,123,1000,1111,1);
214 Statement Executed: Rows Affected = 1
215 echo select * from t1;
216 ---------------------------------------------------------
217 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
218 ---------------------------------------------------------
219 11 222 3333 44444 555555
220 12 123 1234 12345 123456
221 234 4567 56789 1234 4321
222 -2147483648 1 101 212 2147483647
223 101 12121 0 32123 78987
224 12 123 1000 1111 1
225
226 echo select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
227 ---------------------------------------------------------
228 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
229 ---------------------------------------------------------
230 11 222 3333 44444 555555
231
232 echo select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
233 ---------------------------------------------------------
234 f1 f3 f5
235 ---------------------------------------------------------
236 11 3333 555555
237 12 1234 123456
238 234 56789 4321
239 -2147483648 101 2147483647
240 101 0 78987
241
242 echo select * from t1 where f1<-1 and f2>=1 or f3=101;
243 ---------------------------------------------------------
244 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
245 ---------------------------------------------------------
246 -2147483648 1 101 212 2147483647
247
248 echo select * from t1 where f1<-1 and f2>=1 or f3=1234;
249 ---------------------------------------------------------
250 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
251 ---------------------------------------------------------
252 12 123 1234 12345 123456
253 -2147483648 1 101 212 2147483647
254
255 echo select * from t1;
256 ---------------------------------------------------------
257 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
258 ---------------------------------------------------------
259 11 222 3333 44444 555555
260 12 123 1234 12345 123456
261 234 4567 56789 1234 4321
262 -2147483648 1 101 212 2147483647
263 101 12121 0 32123 78987
264 12 123 1000 1111 1
265
266 echo select * from t1 where f1 between 25 and 6000000;
267 ---------------------------------------------------------
268 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
269 ---------------------------------------------------------
270 234 4567 56789 1234 4321
271 101 12121 0 32123 78987
272
273 echo select * from t1 where f1 between 6000000 and 25;
274 ---------------------------------------------------------
275 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
276 ---------------------------------------------------------
277
278 echo update t1 set f1=22 where f1=11;
279 Statement Executed: Rows Affected = 1
280 echo select * from t1;
281 ---------------------------------------------------------
282 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
283 ---------------------------------------------------------
284 22 222 3333 44444 555555
285 12 123 1234 12345 123456
286 234 4567 56789 1234 4321
287 -2147483648 1 101 212 2147483647
288 101 12121 0 32123 78987
289 12 123 1000 1111 1
290
291 echo update t1 set f1=11 where f1=22 and f2=222;
292 Statement Executed: Rows Affected = 1
293 echo select * from t1;
294 ---------------------------------------------------------
295 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
296 ---------------------------------------------------------
297 11 222 3333 44444 555555
298 12 123 1234 12345 123456
299 234 4567 56789 1234 4321
300 -2147483648 1 101 212 2147483647
301 101 12121 0 32123 78987
302 12 123 1000 1111 1
303
304 echo update t1 set f1=10 where f1 in(234,10);
305 Statement Executed: Rows Affected = 1
306 echo select * from t1;
307 ---------------------------------------------------------
308 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
309 ---------------------------------------------------------
310 11 222 3333 44444 555555
311 12 123 1234 12345 123456
312 10 4567 56789 1234 4321
313 -2147483648 1 101 212 2147483647
314 101 12121 0 32123 78987
315 12 123 1000 1111 1
316
317 echo update t1 set f2=100 where f1 in(12,10);
318 Statement Executed: Rows Affected = 3
319 echo select * from t1;
320 ---------------------------------------------------------
321 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
322 ---------------------------------------------------------
323 11 222 3333 44444 555555
324 12 100 1234 12345 123456
325 10 100 56789 1234 4321
326 -2147483648 1 101 212 2147483647
327 101 12121 0 32123 78987
328 12 100 1000 1111 1
329
330 echo update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
331 Statement Executed: Rows Affected = 1
332 echo select * from t1;
333 ---------------------------------------------------------
334 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
335 ---------------------------------------------------------
336 11 222 3333 44444 555555
337 12 100 1234 12345 123456
338 10 222 56789 1234 4321
339 -2147483648 1 101 212 2147483647
340 101 12121 0 32123 78987
341 12 100 1000 1111 1
342
343 echo update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
344 Statement Executed: Rows Affected = 5
345 echo select * from t1;
346 ---------------------------------------------------------
347 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
348 ---------------------------------------------------------
349 11 5 3333 44444 555555
350 12 5 1234 12345 123456
351 10 5 56789 1234 4321
352 -2147483648 5 101 212 2147483647
353 101 12121 0 32123 78987
354 12 5 1000 1111 1
355
356 echo update t1 set f2=101,f2=12121 where f3=0;
357 Statement Executed: Rows Affected = 1
358 echo select * from t1;
359 ---------------------------------------------------------
360 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
361 ---------------------------------------------------------
362 11 5 3333 44444 555555
363 12 5 1234 12345 123456
364 10 5 56789 1234 4321
365 -2147483648 5 101 212 2147483647
366 101 12121 0 32123 78987
367 12 5 1000 1111 1
368
369 echo update t1 set f1=2020202 where f1 in(-2147483648,2147483647) or f1>=7777;
370 Statement Executed: Rows Affected = 1
371 echo select * from t1;
372 ---------------------------------------------------------
373 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
374 ---------------------------------------------------------
375 11 5 3333 44444 555555
376 12 5 1234 12345 123456
377 10 5 56789 1234 4321
378 2020202 5 101 212 2147483647
379 101 12121 0 32123 78987
380 12 5 1000 1111 1
381
382 echo update t1 set f1=987654 where f1 between 25 and 200;
383 Statement Executed: Rows Affected = 1
384 echo select * from t1;
385 ---------------------------------------------------------
386 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
387 ---------------------------------------------------------
388 11 5 3333 44444 555555
389 12 5 1234 12345 123456
390 10 5 56789 1234 4321
391 2020202 5 101 212 2147483647
392 987654 12121 0 32123 78987
393 12 5 1000 1111 1
394
395 echo delete from t1 where f1=2020202 and f5=2147483647;
396 Statement Executed: Rows Affected = 1
397 echo select * from t1;
398 ---------------------------------------------------------
399 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
400 ---------------------------------------------------------
401 11 5 3333 44444 555555
402 12 5 1234 12345 123456
403 10 5 56789 1234 4321
404 987654 12121 0 32123 78987
405 12 5 1000 1111 1
406
407 echo delete from t1 where f1 between 25 and 6000000;
408 Statement Executed: Rows Affected = 1
409 echo select * from t1;
410 ---------------------------------------------------------
411 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
412 ---------------------------------------------------------
413 11 5 3333 44444 555555
414 12 5 1234 12345 123456
415 10 5 56789 1234 4321
416 12 5 1000 1111 1
417
418 echo delete from t1;
419 Statement Executed: Rows Affected = 4
420 echo select * from t1;
421 ---------------------------------------------------------
422 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
423 ---------------------------------------------------------
424
425 echo drop table t1;
426 Statement Executed
427 echo "--Table having 5 fields f1,f2,f3,f4,f5(Different int) index on t1(f1,f3)--";
428 echo create table t1(f1 bigint,f2 integer,f3 int,f4 integer,f5 bigint);
429 Statement Executed
430 echo create index idx1 on t1(f1,f3);
431 Statement Executed
432 echo insert into t1 values(11,222,3333,44444,555555);
433 Statement Executed: Rows Affected = 1
434 echo insert into t1 values(12,123,1234,12345,123456);
435 Statement Executed: Rows Affected = 1
436 echo insert into t1 values(234,4567,56789,1234,4321);
437 Statement Executed: Rows Affected = 1
438 echo insert into t1 values(-2147483648,1,101,212,2147483647);
439 Statement Executed: Rows Affected = 1
440 echo insert into t1 values(101,12121,0,32123,78987);
441 Statement Executed: Rows Affected = 1
442 echo insert into t1 values(12,100,1234,1111,1);
443 Statement Executed: Rows Affected = 1
444 echo select * from t1;
445 ---------------------------------------------------------
446 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
447 ---------------------------------------------------------
448 11 222 3333 44444 555555
449 12 123 1234 12345 123456
450 234 4567 56789 1234 4321
451 -2147483648 1 101 212 2147483647
452 101 12121 0 32123 78987
453 12 100 1234 1111 1
454
455 echo select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
456 ---------------------------------------------------------
457 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
458 ---------------------------------------------------------
459 11 222 3333 44444 555555
460
461 echo select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
462 ---------------------------------------------------------
463 f1 f3 f5
464 ---------------------------------------------------------
465 11 3333 555555
466 12 1234 123456
467 234 56789 4321
468 -2147483648 101 2147483647
469 101 0 78987
470
471 echo select * from t1 where f1<-1 and f2>=1 or f3=101;
472 ---------------------------------------------------------
473 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
474 ---------------------------------------------------------
475 -2147483648 1 101 212 2147483647
476
477 echo select * from t1 where f1<-1 and f2>=1 or f3=1234;
478 ---------------------------------------------------------
479 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
480 ---------------------------------------------------------
481 12 123 1234 12345 123456
482 -2147483648 1 101 212 2147483647
483 12 100 1234 1111 1
484
485 echo select * from t1;
486 ---------------------------------------------------------
487 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
488 ---------------------------------------------------------
489 11 222 3333 44444 555555
490 12 123 1234 12345 123456
491 234 4567 56789 1234 4321
492 -2147483648 1 101 212 2147483647
493 101 12121 0 32123 78987
494 12 100 1234 1111 1
495
496 echo select * from t1 where f1 between 25 and 6000000;
497 ---------------------------------------------------------
498 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
499 ---------------------------------------------------------
500 234 4567 56789 1234 4321
501 101 12121 0 32123 78987
502
503 echo select * from t1 where f1 between 6000000 and 25;
504 ---------------------------------------------------------
505 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
506 ---------------------------------------------------------
507
508 echo update t1 set f1=22 where f1=11;
509 Statement Executed: Rows Affected = 1
510 echo select * from t1;
511 ---------------------------------------------------------
512 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
513 ---------------------------------------------------------
514 22 222 3333 44444 555555
515 12 123 1234 12345 123456
516 234 4567 56789 1234 4321
517 -2147483648 1 101 212 2147483647
518 101 12121 0 32123 78987
519 12 100 1234 1111 1
520
521 echo update t1 set f1=11 where f1=22 and f2=222;
522 Statement Executed: Rows Affected = 1
523 echo select * from t1;
524 ---------------------------------------------------------
525 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
526 ---------------------------------------------------------
527 11 222 3333 44444 555555
528 12 123 1234 12345 123456
529 234 4567 56789 1234 4321
530 -2147483648 1 101 212 2147483647
531 101 12121 0 32123 78987
532 12 100 1234 1111 1
533
534 echo update t1 set f1=10 where f1 in(234,10);
535 Statement Executed: Rows Affected = 1
536 echo select * from t1;
537 ---------------------------------------------------------
538 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
539 ---------------------------------------------------------
540 11 222 3333 44444 555555
541 12 123 1234 12345 123456
542 10 4567 56789 1234 4321
543 -2147483648 1 101 212 2147483647
544 101 12121 0 32123 78987
545 12 100 1234 1111 1
546
547 echo update t1 set f2=100 where f1 in(12,10);
548 Statement Executed: Rows Affected = 3
549 echo select * from t1;
550 ---------------------------------------------------------
551 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
552 ---------------------------------------------------------
553 11 222 3333 44444 555555
554 12 100 1234 12345 123456
555 10 100 56789 1234 4321
556 -2147483648 1 101 212 2147483647
557 101 12121 0 32123 78987
558 12 100 1234 1111 1
559
560 echo update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
561 Statement Executed: Rows Affected = 1
562 echo select * from t1;
563 ---------------------------------------------------------
564 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
565 ---------------------------------------------------------
566 11 222 3333 44444 555555
567 12 100 1234 12345 123456
568 10 222 56789 1234 4321
569 -2147483648 1 101 212 2147483647
570 101 12121 0 32123 78987
571 12 100 1234 1111 1
572
573 echo update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
574 Statement Executed: Rows Affected = 5
575 echo select * from t1;
576 ---------------------------------------------------------
577 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
578 ---------------------------------------------------------
579 11 5 3333 44444 555555
580 12 5 1234 12345 123456
581 10 5 56789 1234 4321
582 -2147483648 5 101 212 2147483647
583 101 12121 0 32123 78987
584 12 5 1234 1111 1
585
586 echo update t1 set f2=101,f2=12121 where f3=0;
587 Statement Executed: Rows Affected = 1
588 echo select * from t1;
589 ---------------------------------------------------------
590 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
591 ---------------------------------------------------------
592 11 5 3333 44444 555555
593 12 5 1234 12345 123456
594 10 5 56789 1234 4321
595 -2147483648 5 101 212 2147483647
596 101 12121 0 32123 78987
597 12 5 1234 1111 1
598
599 echo update t1 set f1=2020202 where f1 in(-2147483648,2147483647) or f1>=7777;
600 Statement Executed: Rows Affected = 1
601 echo select * from t1;
602 ---------------------------------------------------------
603 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
604 ---------------------------------------------------------
605 11 5 3333 44444 555555
606 12 5 1234 12345 123456
607 10 5 56789 1234 4321
608 2020202 5 101 212 2147483647
609 101 12121 0 32123 78987
610 12 5 1234 1111 1
611
612 echo update t1 set f1=987654 where f1 between 25 and 200 and f3>=4000;
613 Statement Executed: Rows Affected = 0
614 echo select * from t1;
615 ---------------------------------------------------------
616 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
617 ---------------------------------------------------------
618 11 5 3333 44444 555555
619 12 5 1234 12345 123456
620 10 5 56789 1234 4321
621 2020202 5 101 212 2147483647
622 101 12121 0 32123 78987
623 12 5 1234 1111 1
624
625 echo delete from t1 where f1=-2147483648 or f3=1234;
626 Statement Executed: Rows Affected = 2
627 echo select * from t1;
628 ---------------------------------------------------------
629 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
630 ---------------------------------------------------------
631 11 5 3333 44444 555555
632 10 5 56789 1234 4321
633 2020202 5 101 212 2147483647
634 101 12121 0 32123 78987
635
636 echo delete from t1 where f1 between 25 and 6000000;
637 Statement Executed: Rows Affected = 2
638 echo select * from t1;
639 ---------------------------------------------------------
640 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
641 ---------------------------------------------------------
642 11 5 3333 44444 555555
643 10 5 56789 1234 4321
644
645 echo delete from t1;
646 Statement Executed: Rows Affected = 2
647 echo select * from t1;
648 ---------------------------------------------------------
649 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
650 ---------------------------------------------------------
651
652 echo drop table t1;
653 Statement Executed
654 echo "--Table having 5 fields f1,f2,f3,f4,f5(Different int) index on t1(f1,f3,f2)--";
655 echo create table t1(f1 bigint,f2 integer,f3 int,f4 integer,f5 bigint);
656 Statement Executed
657 echo create index idx1 on t1(f1,f3,f2);
658 Statement Executed
659 echo insert into t1 values(11,222,3333,44444,555555);
660 Statement Executed: Rows Affected = 1
661 echo insert into t1 values(12,123,1234,12345,123456);
662 Statement Executed: Rows Affected = 1
663 echo insert into t1 values(234,4567,56789,1234,4321);
664 Statement Executed: Rows Affected = 1
665 echo insert into t1 values(-2147483648,1,101,212,2147483647);
666 Statement Executed: Rows Affected = 1
667 echo insert into t1 values(101,12121,0,32123,78987);
668 Statement Executed: Rows Affected = 1
669 echo insert into t1 values(12,123,1234,1111,1);
670 Statement Executed: Rows Affected = 1
671 echo select * from t1;
672 ---------------------------------------------------------
673 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
674 ---------------------------------------------------------
675 11 222 3333 44444 555555
676 12 123 1234 12345 123456
677 234 4567 56789 1234 4321
678 -2147483648 1 101 212 2147483647
679 101 12121 0 32123 78987
680 12 123 1234 1111 1
681
682 echo select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
683 ---------------------------------------------------------
684 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
685 ---------------------------------------------------------
686 11 222 3333 44444 555555
687
688 echo select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
689 ---------------------------------------------------------
690 f1 f3 f5
691 ---------------------------------------------------------
692 11 3333 555555
693 12 1234 123456
694 234 56789 4321
695 -2147483648 101 2147483647
696 101 0 78987
697
698 echo select * from t1 where f1<-1 and f2>=1 or f3=101;
699 ---------------------------------------------------------
700 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
701 ---------------------------------------------------------
702 -2147483648 1 101 212 2147483647
703
704 echo select * from t1 where f1<-1 and f2>=1 or f3=1234;
705 ---------------------------------------------------------
706 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
707 ---------------------------------------------------------
708 12 123 1234 12345 123456
709 -2147483648 1 101 212 2147483647
710 12 123 1234 1111 1
711
712 echo select * from t1;
713 ---------------------------------------------------------
714 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
715 ---------------------------------------------------------
716 11 222 3333 44444 555555
717 12 123 1234 12345 123456
718 234 4567 56789 1234 4321
719 -2147483648 1 101 212 2147483647
720 101 12121 0 32123 78987
721 12 123 1234 1111 1
722
723 echo select * from t1 where f1 between 25 and 6000000;
724 ---------------------------------------------------------
725 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
726 ---------------------------------------------------------
727 234 4567 56789 1234 4321
728 101 12121 0 32123 78987
729
730 echo select * from t1 where f1 between 6000000 and 25;
731 ---------------------------------------------------------
732 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
733 ---------------------------------------------------------
734
735 echo update t1 set f1=22 where f1=11;
736 Statement Executed: Rows Affected = 1
737 echo select * from t1;
738 ---------------------------------------------------------
739 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
740 ---------------------------------------------------------
741 22 222 3333 44444 555555
742 12 123 1234 12345 123456
743 234 4567 56789 1234 4321
744 -2147483648 1 101 212 2147483647
745 101 12121 0 32123 78987
746 12 123 1234 1111 1
747
748 echo update t1 set f1=11 where f1=22 and f2=222;
749 Statement Executed: Rows Affected = 1
750 echo select * from t1;
751 ---------------------------------------------------------
752 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
753 ---------------------------------------------------------
754 11 222 3333 44444 555555
755 12 123 1234 12345 123456
756 234 4567 56789 1234 4321
757 -2147483648 1 101 212 2147483647
758 101 12121 0 32123 78987
759 12 123 1234 1111 1
760
761 echo update t1 set f1=10 where f1 in(234,10);
762 Statement Executed: Rows Affected = 1
763 echo select * from t1;
764 ---------------------------------------------------------
765 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
766 ---------------------------------------------------------
767 11 222 3333 44444 555555
768 12 123 1234 12345 123456
769 10 4567 56789 1234 4321
770 -2147483648 1 101 212 2147483647
771 101 12121 0 32123 78987
772 12 123 1234 1111 1
773
774 echo update t1 set f2=100 where f1 in(12,10);
775 Statement Executed: Rows Affected = 3
776 echo select * from t1;
777 ---------------------------------------------------------
778 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
779 ---------------------------------------------------------
780 11 222 3333 44444 555555
781 12 100 1234 12345 123456
782 10 100 56789 1234 4321
783 -2147483648 1 101 212 2147483647
784 101 12121 0 32123 78987
785 12 100 1234 1111 1
786
787 echo update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
788 Statement Executed: Rows Affected = 1
789 echo select * from t1;
790 ---------------------------------------------------------
791 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
792 ---------------------------------------------------------
793 11 222 3333 44444 555555
794 12 100 1234 12345 123456
795 10 222 56789 1234 4321
796 -2147483648 1 101 212 2147483647
797 101 12121 0 32123 78987
798 12 100 1234 1111 1
799
800 echo update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
801 Statement Executed: Rows Affected = 5
802 echo select * from t1;
803 ---------------------------------------------------------
804 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
805 ---------------------------------------------------------
806 11 5 3333 44444 555555
807 12 5 1234 12345 123456
808 10 5 56789 1234 4321
809 -2147483648 5 101 212 2147483647
810 101 12121 0 32123 78987
811 12 5 1234 1111 1
812
813 echo update t1 set f2=101,f2=12121 where f3=0;
814 Statement Executed: Rows Affected = 1
815 echo select * from t1;
816 ---------------------------------------------------------
817 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
818 ---------------------------------------------------------
819 11 5 3333 44444 555555
820 12 5 1234 12345 123456
821 10 5 56789 1234 4321
822 -2147483648 5 101 212 2147483647
823 101 12121 0 32123 78987
824 12 5 1234 1111 1
825
826 echo update t1 set f1=2020202 where f1 in(-2147483647,2147483647) and f2=5 and f3=101;
827 Statement Executed: Rows Affected = 0
828 echo select * from t1;
829 ---------------------------------------------------------
830 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
831 ---------------------------------------------------------
832 11 5 3333 44444 555555
833 12 5 1234 12345 123456
834 10 5 56789 1234 4321
835 -2147483648 5 101 212 2147483647
836 101 12121 0 32123 78987
837 12 5 1234 1111 1
838
839 echo update t1 set f1=2020202 where f1 in(-2147483648,2147483647) or f1>=7777;
840 Statement Executed: Rows Affected = 1
841 echo select * from t1;
842 ---------------------------------------------------------
843 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
844 ---------------------------------------------------------
845 11 5 3333 44444 555555
846 12 5 1234 12345 123456
847 10 5 56789 1234 4321
848 2020202 5 101 212 2147483647
849 101 12121 0 32123 78987
850 12 5 1234 1111 1
851
852 echo update t1 set f1=987654 where f1 between 25 and 200 and f3>=4000;
853 Statement Executed: Rows Affected = 0
854 echo select * from t1;
855 ---------------------------------------------------------
856 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
857 ---------------------------------------------------------
858 11 5 3333 44444 555555
859 12 5 1234 12345 123456
860 10 5 56789 1234 4321
861 2020202 5 101 212 2147483647
862 101 12121 0 32123 78987
863 12 5 1234 1111 1
864
865 echo delete from t1 where f1=-2147483648 or f3=1234;
866 Statement Executed: Rows Affected = 2
867 echo select * from t1;
868 ---------------------------------------------------------
869 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
870 ---------------------------------------------------------
871 11 5 3333 44444 555555
872 10 5 56789 1234 4321
873 2020202 5 101 212 2147483647
874 101 12121 0 32123 78987
875
876 echo delete from t1 where f1 between 25 and 6000000;
877 Statement Executed: Rows Affected = 2
878 echo select * from t1;
879 ---------------------------------------------------------
880 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
881 ---------------------------------------------------------
882 11 5 3333 44444 555555
883 10 5 56789 1234 4321
884
885 echo delete from t1;
886 Statement Executed: Rows Affected = 2
887 echo select * from t1;
888 ---------------------------------------------------------
889 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
890 ---------------------------------------------------------
891
892 echo drop table t1;
893 Statement Executed
894 echo "--Table having 5 fields f1,f2,f3,f4,f5(Different int) index on t1(f5,f1,f3,f2)--";
895 echo create table t1(f1 bigint,f2 integer,f3 int,f4 integer,f5 bigint);
896 Statement Executed
897 echo create index idx1 on t1(f5,f1,f3,f2);
898 Statement Executed
899 echo insert into t1 values(11,222,3333,44444,555555);
900 Statement Executed: Rows Affected = 1
901 echo insert into t1 values(12,123,1234,12345,123456);
902 Statement Executed: Rows Affected = 1
903 echo insert into t1 values(234,4567,56789,1234,4321);
904 Statement Executed: Rows Affected = 1
905 echo insert into t1 values(-2147483648,1,101,212,2147483647);
906 Statement Executed: Rows Affected = 1
907 echo insert into t1 values(101,12121,0,32123,78987);
908 Statement Executed: Rows Affected = 1
909 echo insert into t1 values(12,123,1234,1111,1);
910 Statement Executed: Rows Affected = 1
911 echo select * from t1;
912 ---------------------------------------------------------
913 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
914 ---------------------------------------------------------
915 11 222 3333 44444 555555
916 12 123 1234 12345 123456
917 234 4567 56789 1234 4321
918 -2147483648 1 101 212 2147483647
919 101 12121 0 32123 78987
920 12 123 1234 1111 1
921
922 echo select * from t1 where f1>0 and f2<=222 and f3=3333 and f4>44443 and f5!=555550;
923 ---------------------------------------------------------
924 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
925 ---------------------------------------------------------
926 11 222 3333 44444 555555
927
928 echo select f1,f3,f5 from t1 where f1=234 or f5>4000 or f3>=50000;
929 ---------------------------------------------------------
930 f1 f3 f5
931 ---------------------------------------------------------
932 11 3333 555555
933 12 1234 123456
934 234 56789 4321
935 -2147483648 101 2147483647
936 101 0 78987
937
938 echo select * from t1 where f1<-1 and f2>=1 or f3=101;
939 ---------------------------------------------------------
940 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
941 ---------------------------------------------------------
942 -2147483648 1 101 212 2147483647
943
944 echo select * from t1 where f1<-1 and f2>=1 or f3=1234;
945 ---------------------------------------------------------
946 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
947 ---------------------------------------------------------
948 12 123 1234 12345 123456
949 -2147483648 1 101 212 2147483647
950 12 123 1234 1111 1
951
952 echo select * from t1;
953 ---------------------------------------------------------
954 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
955 ---------------------------------------------------------
956 11 222 3333 44444 555555
957 12 123 1234 12345 123456
958 234 4567 56789 1234 4321
959 -2147483648 1 101 212 2147483647
960 101 12121 0 32123 78987
961 12 123 1234 1111 1
962
963 echo select * from t1 where f1 between 25 and 6000000;
964 ---------------------------------------------------------
965 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
966 ---------------------------------------------------------
967 234 4567 56789 1234 4321
968 101 12121 0 32123 78987
969
970 echo select * from t1 where f1 between 6000000 and 25;
971 ---------------------------------------------------------
972 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
973 ---------------------------------------------------------
974
975 echo update t1 set f1=22 where f1=11;
976 Statement Executed: Rows Affected = 1
977 echo select * from t1;
978 ---------------------------------------------------------
979 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
980 ---------------------------------------------------------
981 22 222 3333 44444 555555
982 12 123 1234 12345 123456
983 234 4567 56789 1234 4321
984 -2147483648 1 101 212 2147483647
985 101 12121 0 32123 78987
986 12 123 1234 1111 1
987
988 echo update t1 set f1=11 where f1=22 and f2=222;
989 Statement Executed: Rows Affected = 1
990 echo select * from t1;
991 ---------------------------------------------------------
992 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
993 ---------------------------------------------------------
994 11 222 3333 44444 555555
995 12 123 1234 12345 123456
996 234 4567 56789 1234 4321
997 -2147483648 1 101 212 2147483647
998 101 12121 0 32123 78987
999 12 123 1234 1111 1
1000
1001 echo update t1 set f1=10 where f1 in(234,10);
1002 Statement Executed: Rows Affected = 1
1003 echo select * from t1;
1004 ---------------------------------------------------------
1005 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
1006 ---------------------------------------------------------
1007 11 222 3333 44444 555555
1008 12 123 1234 12345 123456
1009 10 4567 56789 1234 4321
1010 -2147483648 1 101 212 2147483647
1011 101 12121 0 32123 78987
1012 12 123 1234 1111 1
1013
1014 echo update t1 set f2=100 where f1 in(12,10);
1015 Statement Executed: Rows Affected = 3
1016 echo select * from t1;
1017 ---------------------------------------------------------
1018 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
1019 ---------------------------------------------------------
1020 11 222 3333 44444 555555
1021 12 100 1234 12345 123456
1022 10 100 56789 1234 4321
1023 -2147483648 1 101 212 2147483647
1024 101 12121 0 32123 78987
1025 12 100 1234 1111 1
1026
1027 echo update t1 set f2=222 where f3=56789 and f2 in(100,4567) and f4 between 100 and 20000;
1028 Statement Executed: Rows Affected = 1
1029 echo select * from t1;
1030 ---------------------------------------------------------
1031 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
1032 ---------------------------------------------------------
1033 11 222 3333 44444 555555
1034 12 100 1234 12345 123456
1035 10 222 56789 1234 4321
1036 -2147483648 1 101 212 2147483647
1037 101 12121 0 32123 78987
1038 12 100 1234 1111 1
1039
1040 echo update t1 set f2=5 where f3=56789 or f2 in(123,222) or f4 between 100 and 20000;
1041 Statement Executed: Rows Affected = 5
1042 echo select * from t1;
1043 ---------------------------------------------------------
1044 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
1045 ---------------------------------------------------------
1046 11 5 3333 44444 555555
1047 12 5 1234 12345 123456
1048 10 5 56789 1234 4321
1049 -2147483648 5 101 212 2147483647
1050 101 12121 0 32123 78987
1051 12 5 1234 1111 1
1052
1053 echo update t1 set f1=2020202 where f1 in(-2147483647,2147483647) and f2=5 and f3=101;
1054 Statement Executed: Rows Affected = 0
1055 echo select * from t1;
1056 ---------------------------------------------------------
1057 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
1058 ---------------------------------------------------------
1059 11 5 3333 44444 555555
1060 12 5 1234 12345 123456
1061 10 5 56789 1234 4321
1062 -2147483648 5 101 212 2147483647
1063 101 12121 0 32123 78987
1064 12 5 1234 1111 1
1065
1066 echo update t1 set f1=2020202 where f1 in(-2147483648,2147483647) or f1>=7777;
1067 Statement Executed: Rows Affected = 1
1068 echo select * from t1;
1069 ---------------------------------------------------------
1070 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
1071 ---------------------------------------------------------
1072 11 5 3333 44444 555555
1073 12 5 1234 12345 123456
1074 10 5 56789 1234 4321
1075 2020202 5 101 212 2147483647
1076 101 12121 0 32123 78987
1077 12 5 1234 1111 1
1078
1079 echo delete from t1 where f1=-2147483648 or f3=1234;
1080 Statement Executed: Rows Affected = 2
1081 echo select * from t1;
1082 ---------------------------------------------------------
1083 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
1084 ---------------------------------------------------------
1085 11 5 3333 44444 555555
1086 10 5 56789 1234 4321
1087 2020202 5 101 212 2147483647
1088 101 12121 0 32123 78987
1089
1090 echo delete from t1 where f1 between 25 and 6000000;
1091 Statement Executed: Rows Affected = 2
1092 echo select * from t1;
1093 ---------------------------------------------------------
1094 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
1095 ---------------------------------------------------------
1096 11 5 3333 44444 555555
1097 10 5 56789 1234 4321
1098
1099 echo delete from t1;
1100 Statement Executed: Rows Affected = 2
1101 echo select * from t1;
1102 ---------------------------------------------------------
1103 t1.f1 t1.f2 t1.f3 t1.f4 t1.f5
1104 ---------------------------------------------------------
1105
1106 echo drop table t1;
1107 Statement Executed
1108 Statement execute failed with error -4
Main memory database SQL engine (cvs import)