test/tools/csql/exp.testnw030.ksh

   1 Network CSql
   2 echo CREATE TABLE T1(f1 INT AUTO_INCREMENT,f2 INT);
   3 Statement Executed
   4 echo INSERT INTO T1 values(100,1);
   5 Statement Executed: Rows Affected = 1
   6 echo INSERT INTO T1(f2) values(10);
   7 Statement Executed: Rows Affected = 1
   8 echo INSERT INTO T1(f2) values(30);
   9 Statement Executed: Rows Affected = 1
  10 echo INSERT INTO T1(f2) values(40);
  11 Statement Executed: Rows Affected = 1
  12 echo INSERT INTO T1(f1,f2) values(NULL,40);
  13 Statement Executed: Rows Affected = 1
  14 echo INSERT INTO T1(f1,f2) values(100,40);
  15 Statement execute failed with error -21
  16 echo SELECT * FROM T1;
  17 ---------------------------------------------------------
  18         T1.f1   T1.f2
  19 ---------------------------------------------------------
  20         100     1
  21         101     10
  22         102     30
  23         103     40
  24         104     40
  25
  26 echo CREATE TABLE T2(f1 INT AUTO_INCREMENT,f2 CHAR(10));
  27 Statement Executed
  28 echo INSERT INTO T2 values(10,'Papu');
  29 Statement Executed: Rows Affected = 1
  30 echo INSERT INTO T2(f2) values('Jitu');
  31 Statement Executed: Rows Affected = 1
  32 echo INSERT INTO T2(f2) values('kishor');
  33 Statement Executed: Rows Affected = 1
  34 echo INSERT INTO T2(f1,f2) values(NULL,'Nihar');
  35 Statement Executed: Rows Affected = 1
  36 echo INSERT INTO T2(f1,f2) values(100,'Suna');
  37 Statement Executed: Rows Affected = 1
  38 echo SELECT * FROM T2;
  39 ---------------------------------------------------------
  40         T2.f1   T2.f2
  41 ---------------------------------------------------------
  42         10      Papu
  43         11      Jitu
  44         12      kishor
  45         13      Nihar
  46         100     Suna
  47
  48 echo CREATE TABLE T3(f1 CHAR(10),f2 int auto_increment);
  49 Statement Executed
  50 echo INSERT INTO T3 values('Papu',10);
  51 Statement Executed: Rows Affected = 1
  52 echo INSERT INTO T3(f1) values('Jitu');
  53 Statement Executed: Rows Affected = 1
  54 echo INSERT INTO T3(f1) values('kishor');
  55 Statement Executed: Rows Affected = 1
  56 echo INSERT INTO T3(f1,f2) values('Nihar',NULL);
  57 Statement Executed: Rows Affected = 1
  58 echo INSERT INTO T3(f2,f1) values(100,NULL);
  59 Statement Executed: Rows Affected = 1
  60 echo INSERT INTO T3(f1,f2) values(NULL,NULL);
  61 Statement Executed: Rows Affected = 1
  62 echo SELECT * FROM T3;
  63 ---------------------------------------------------------
  64         T3.f1   T3.f2
  65 ---------------------------------------------------------
  66         Papu    10
  67         Jitu    11
  68         kishor  12
  69         Nihar   13
  70         NULL    100
  71         NULL    101
  72
  73 echo CREATE TABLE T4(f1 INT AUTO_INCREMENT,f2 INT);
  74 Statement Executed
  75 echo INSERT INTO T4 VALUES(1,2);
  76 Statement Executed: Rows Affected = 1
  77 echo INSERT INTO T4 VALUES(NULL,2);
  78 Statement Executed: Rows Affected = 1
  79 echo INSERT INTO T4 VALUES(NULL,3);
  80 Statement Executed: Rows Affected = 1
  81 echo INSERT INTO T4 VALUES(NULL,2);
  82 Statement Executed: Rows Affected = 1
  83 echo INSERT INTO T4 VALUES(NULL,3);
  84 Statement Executed: Rows Affected = 1
  85 echo SELECT * FROM T4;
  86 ---------------------------------------------------------
  87         T4.f1   T4.f2
  88 ---------------------------------------------------------
  89         1       2
  90         2       2
  91         3       3
  92         4       2
  93         5       3
  94
  95 echo UPDATE T4 SET f2=20 WHERE f2=2;
  96 Statement Executed: Rows Affected = 3
  97 echo SELECT * FROM T4;
  98 ---------------------------------------------------------
  99         T4.f1   T4.f2
 100 ---------------------------------------------------------
 101         1       20
 102         2       20
 103         3       3
 104         4       20
 105         5       3
 106
 107 echo UPDATE T4 SET f2=200,f1=0 WHERE f2=20;
 108 Statement prepare failed with error -33
 109 echo SELECT * FROM T4;
 110 ---------------------------------------------------------
 111         T4.f1   T4.f2
 112 ---------------------------------------------------------
 113         1       20
 114         2       20
 115         3       3
 116         4       20
 117         5       3
 118
 119 echo UPDATE T4 SET f2=300,f1=30 WHERE f1<=2;
 120 Statement prepare failed with error -33
 121 echo SELECT * FROM T4;
 122 ---------------------------------------------------------
 123         T4.f1   T4.f2
 124 ---------------------------------------------------------
 125         1       20
 126         2       20
 127         3       3
 128         4       20
 129         5       3
 130
 131 echo CREATE TABLE T5(f1 INT AUTO_INCREMENT,f2 INT NOT NULL);
 132 Statement Executed
 133 echo INSERT INTO T5 VALUES(NULL,2);
 134 Statement Executed: Rows Affected = 1
 135 echo INSERT INTO T5 VALUES(NULL,NULL);
 136 Statement execute failed with error -22
 137 echo INSERT INTO T5 VALUES(NULL,3);
 138 Statement Executed: Rows Affected = 1
 139 echo INSERT INTO T5 VALUES(NULL,2);
 140 Statement Executed: Rows Affected = 1
 141 echo INSERT INTO T5 VALUES(NULL,3);
 142 Statement Executed: Rows Affected = 1
 143 echo SELECT * FROM T5;
 144 ---------------------------------------------------------
 145         T5.f1   T5.f2
 146 ---------------------------------------------------------
 147         1       2
 148         3       3
 149         4       2
 150         5       3
 151
 152 echo DELETE FROM T5 WHERE f2=2;
 153 Statement Executed: Rows Affected = 2
 154 echo SELECT * FROM T5;
 155 ---------------------------------------------------------
 156         T5.f1   T5.f2
 157 ---------------------------------------------------------
 158         3       3
 159         5       3
 160
 161 echo ROLLBACK;
 162 echo SELECT * FROM T5;
 163 ---------------------------------------------------------
 164         T5.f1   T5.f2
 165 ---------------------------------------------------------
 166         3       3
 167         5       3
 168
 169 echo DELETE FROM T5 WHERE f1<=3 AND f1!=2;
 170 Statement Executed: Rows Affected = 1
 171 echo SELECT * FROM T5;
 172 ---------------------------------------------------------
 173         T5.f1   T5.f2
 174 ---------------------------------------------------------
 175         5       3
 176
 177 echo INSERT INTO T5 VALUES(3,30);
 178 Statement Executed: Rows Affected = 1
 179 echo SELECT * FROM T5;
 180 ---------------------------------------------------------
 181         T5.f1   T5.f2
 182 ---------------------------------------------------------
 183         3       30
 184         5       3
 185
 186 echo DELETE FROM T5;
 187 Statement Executed: Rows Affected = 2
 188 echo SELECT * FROM T5;
 189 ---------------------------------------------------------
 190         T5.f1   T5.f2
 191 ---------------------------------------------------------
 192
 193 echo CREATE TABLE T6(f1 INT AUTO_INCREMENT,f2 INT);
 194 Statement Executed
 195 echo INSERT INTO T6 VALUES(1,2);
 196 Statement Executed: Rows Affected = 1
 197 echo INSERT INTO T6 VALUES(NULL,2);
 198 Statement Executed: Rows Affected = 1
 199 echo INSERT INTO T6 VALUES(NULL,3);
 200 Statement Executed: Rows Affected = 1
 201 echo INSERT INTO T6 VALUES(NULL,2);
 202 Statement Executed: Rows Affected = 1
 203 echo INSERT INTO T6 VALUES(NULL,3);
 204 Statement Executed: Rows Affected = 1
 205 echo SELECT * FROM T6;
 206 ---------------------------------------------------------
 207         T6.f1   T6.f2
 208 ---------------------------------------------------------
 209         1       2
 210         2       2
 211         3       3
 212         4       2
 213         5       3
 214
 215 echo UPDATE T6 SET f2=20 WHERE f2=2;
 216 Statement Executed: Rows Affected = 3
 217 echo SELECT * FROM T6;
 218 ---------------------------------------------------------
 219         T6.f1   T6.f2
 220 ---------------------------------------------------------
 221         1       20
 222         2       20
 223         3       3
 224         4       20
 225         5       3
 226
 227 echo UPDATE T6 SET f2=200,f1=0 WHERE f2=20;
 228 Statement prepare failed with error -33
 229 echo SELECT * FROM T6;
 230 ---------------------------------------------------------
 231         T6.f1   T6.f2
 232 ---------------------------------------------------------
 233         1       20
 234         2       20
 235         3       3
 236         4       20
 237         5       3
 238
 239 echo UPDATE T6 SET f2=300,f1=30 WHERE f1<=2;
 240 Statement prepare failed with error -33
 241 echo SELECT * FROM T6;
 242 ---------------------------------------------------------
 243         T6.f1   T6.f2
 244 ---------------------------------------------------------
 245         1       20
 246         2       20
 247         3       3
 248         4       20
 249         5       3
 250
 251 echo DELETE FROM T6 WHERE f2=2;
 252 Statement Executed: Rows Affected = 0
 253 echo SELECT * FROM T6;
 254 ---------------------------------------------------------
 255         T6.f1   T6.f2
 256 ---------------------------------------------------------
 257         1       20
 258         2       20
 259         3       3
 260         4       20
 261         5       3
 262
 263 echo ROLLBACK;
 264 echo SELECT * FROM T6;
 265 ---------------------------------------------------------
 266         T6.f1   T6.f2
 267 ---------------------------------------------------------
 268         1       20
 269         2       20
 270         3       3
 271         4       20
 272         5       3
 273
 274 echo DELETE FROM T6 WHERE f1<=3 AND f1!=2;
 275 Statement Executed: Rows Affected = 2
 276 echo SELECT * FROM T6;
 277 ---------------------------------------------------------
 278         T6.f1   T6.f2
 279 ---------------------------------------------------------
 280         2       20
 281         4       20
 282         5       3
 283
 284 echo INSERT INTO T6 VALUES(3,30);
 285 Statement Executed: Rows Affected = 1
 286 echo SELECT * FROM T6;
 287 ---------------------------------------------------------
 288         T6.f1   T6.f2
 289 ---------------------------------------------------------
 290         3       30
 291         2       20
 292         4       20
 293         5       3
 294
 295 echo DELETE FROM T6;
 296 Statement Executed: Rows Affected = 4
 297 echo SELECT * FROM T6;
 298 ---------------------------------------------------------
 299         T6.f1   T6.f2
 300 ---------------------------------------------------------
 301
 302 echo CREATE TABLE T7(f1 INT AUTO_INCREMENT,f2 INT,f3 INT);
 303 Statement Executed
 304 echo INSERT INTO T7 VALUES(1,2,3);
 305 Statement Executed: Rows Affected = 1
 306 echo SELECT * FROM T7;
 307 ---------------------------------------------------------
 308         T7.f1   T7.f2   T7.f3
 309 ---------------------------------------------------------
 310         1       2       3
 311
 312 echo UPDATE T7 SET f3=30,f2=20,f1=0 where f1=1;
 313 Statement prepare failed with error -33
 314 echo UPDATE T7 SET f3=30,f2=20 where f1=1;
 315 Statement Executed: Rows Affected = 1
 316 echo SELECT * FROM T7;
 317 ---------------------------------------------------------
 318         T7.f1   T7.f2   T7.f3
 319 ---------------------------------------------------------
 320         1       20      30
 321
 322 echo INSERT INTO T7 VALUES(2,4,3);
 323 Statement Executed: Rows Affected = 1
 324 echo DELETE FROM T7 WHERE f3<=3;
 325 Statement Executed: Rows Affected = 1
 326 echo CREATE TABLE T9(f1 INT AUTO_INCREMENT,f2 INT,f3 INT);
 327 Statement Executed
 328 echo CREATE INDEX idx1 ON T9(f1,f2) UNIQUE;
 329 Statement Executed
 330 echo INSERT INTO T9 VALUES(1,2,3);
 331 Statement Executed: Rows Affected = 1
 332 echo SELECT * FROM T9;
 333 ---------------------------------------------------------
 334         T9.f1   T9.f2   T9.f3
 335 ---------------------------------------------------------
 336         1       2       3
 337
 338 echo UPDATE T9 SET f3=30,f2=20,f1=0 where f1=1;
 339 Statement prepare failed with error -21
 340 echo UPDATE T9 SET f3=30,f2=20 where f1=1;
 341 Statement prepare failed with error -21
 342 echo SELECT * FROM T9;
 343 ---------------------------------------------------------
 344         T9.f1   T9.f2   T9.f3
 345 ---------------------------------------------------------
 346         1       2       3
 347
 348 echo INSERT INTO T9 VALUES(2,4,3);
 349 Statement Executed: Rows Affected = 1
 350 echo DELETE FROM T9 WHERE f3<=3;
 351 Statement Executed: Rows Affected = 2
 352 echo CREATE TABLE T10(f1 INT AUTO_INCREMENT,f2 INT,f3 INT,PRIMARY KEY(f3,f1));
 353 Statement Executed
 354 echo INSERT INTO T10 VALUES(1,2,3);
 355 Statement Executed: Rows Affected = 1
 356 echo SELECT * FROM T10;
 357 ---------------------------------------------------------
 358         T10.f1  T10.f2  T10.f3
 359 ---------------------------------------------------------
 360         1       2       3
 361
 362 echo UPDATE T10 SET f3=30,f2=20,f1=0 where f1=1;
 363 Statement prepare failed with error -21
 364 echo UPDATE T10 SET f3=30,f2=20 where f1=1;
 365 Statement prepare failed with error -21
 366 echo SELECT * FROM T10;
 367 ---------------------------------------------------------
 368         T10.f1  T10.f2  T10.f3
 369 ---------------------------------------------------------
 370         1       2       3
 371
 372 echo INSERT INTO T10 VALUES(2,4,3);
 373 Statement Executed: Rows Affected = 1
 374 echo DELETE FROM T10 WHERE f3<=3;
 375 Statement Executed: Rows Affected = 2
 376 Statement Executed
 377 Statement Executed
 378 Statement Executed
 379 Statement Executed
 380 Statement Executed
 381 Statement Executed
 382 Statement Executed
 383 Statement Executed
 384 Statement Executed