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