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