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