test/tools/csql/exp.test094.ksh

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