test/tools/csql/exp.test088.ksh

   1 echo "<<<<<<<<<<<<<<<<<<<<<<<< UPDATE t1 SET f2=NULL >>>>>>>>>>>>>>>>>>>>>>>>>";
   2 echo create table t1(f1 int not null,f2 int,f3 int);
   3 Statement Executed
   4 echo insert into t1 values(1,2,3);
   5 Statement Executed: Rows Affected = 1
   6 echo insert into t1 values(2,NULL,4);
   7 Statement Executed: Rows Affected = 1
   8 echo insert into t1 values(3,4,NULL);
   9 Statement Executed: Rows Affected = 1
  10 echo insert into t1 values(10,20,30);
  11 Statement Executed: Rows Affected = 1
  12 echo insert into t1 values(11,NULL,NULL);
  13 Statement Executed: Rows Affected = 1
  14 echo commit;
  15 echo select * from t1;
  16 ---------------------------------------------------------
  17         t1.f1   t1.f2   t1.f3
  18 ---------------------------------------------------------
  19         1       2       3
  20         2       NULL    4
  21         3       4       NULL
  22         10      20      30
  23         11      NULL    NULL
  24
  25 echo update t1 set f2=NULL;
  26 Statement Executed: Rows Affected = 5
  27 echo commit;
  28 echo select * from t1;
  29 ---------------------------------------------------------
  30         t1.f1   t1.f2   t1.f3
  31 ---------------------------------------------------------
  32         1       NULL    3
  33         2       NULL    4
  34         3       NULL    NULL
  35         10      NULL    30
  36         11      NULL    NULL
  37
  38 echo drop table t1;
  39 Statement Executed
  40 echo "<<<<<<<<<<<<<<<<<<<<<<<< UPDATE t1 SET f1=NULL >>>>>>>>>>>>>>>>>>>>>>>>>";
  41 echo create table t1(f1 int not null,f2 int,f3 int);
  42 Statement Executed
  43 echo insert into t1 values(1,2,3);
  44 Statement Executed: Rows Affected = 1
  45 echo insert into t1 values(2,NULL,4);
  46 Statement Executed: Rows Affected = 1
  47 echo insert into t1 values(3,4,NULL);
  48 Statement Executed: Rows Affected = 1
  49 echo insert into t1 values(10,20,30);
  50 Statement Executed: Rows Affected = 1
  51 echo insert into t1 values(11,NULL,NULL);
  52 Statement Executed: Rows Affected = 1
  53 echo commit;
  54 echo select * from t1;
  55 ---------------------------------------------------------
  56         t1.f1   t1.f2   t1.f3
  57 ---------------------------------------------------------
  58         1       2       3
  59         2       NULL    4
  60         3       4       NULL
  61         10      20      30
  62         11      NULL    NULL
  63
  64 echo update t1 set f1=NULL;
  65 Statement prepare failed with error -22
  66 echo commit;
  67 echo select * from t1;
  68 ---------------------------------------------------------
  69         t1.f1   t1.f2   t1.f3
  70 ---------------------------------------------------------
  71         1       2       3
  72         2       NULL    4
  73         3       4       NULL
  74         10      20      30
  75         11      NULL    NULL
  76
  77 echo drop table t1;
  78 Statement Executed
  79 echo "<<<<<<<<<<<<<<<<<<<<<< UPDATE t1 SET f1=f1+f2+f3 >>>>>>>>>>>>>>>>>>>>>>>";
  80 echo create table t1(f1 int not null,f2 int,f3 int);
  81 Statement Executed
  82 echo insert into t1 values(1,2,3);
  83 Statement Executed: Rows Affected = 1
  84 echo insert into t1 values(2,NULL,4);
  85 Statement Executed: Rows Affected = 1
  86 echo insert into t1 values(3,4,NULL);
  87 Statement Executed: Rows Affected = 1
  88 echo insert into t1 values(10,20,30);
  89 Statement Executed: Rows Affected = 1
  90 echo insert into t1 values(11,NULL,NULL);
  91 Statement Executed: Rows Affected = 1
  92 echo commit;
  93 echo select * from t1;
  94 ---------------------------------------------------------
  95         t1.f1   t1.f2   t1.f3
  96 ---------------------------------------------------------
  97         1       2       3
  98         2       NULL    4
  99         3       4       NULL
 100         10      20      30
 101         11      NULL    NULL
 102
 103 echo update t1 set f1=f1+f2+f3;
 104 Statement execute failed with error -22
 105 echo commit;
 106 echo select * from t1;
 107 ---------------------------------------------------------
 108         t1.f1   t1.f2   t1.f3
 109 ---------------------------------------------------------
 110         1       2       3
 111         2       NULL    4
 112         3       4       NULL
 113         10      20      30
 114         11      NULL    NULL
 115
 116 echo drop table t1;
 117 Statement Executed
 118 echo "<<<<<<<<<<<<<<<<<< UPDATE t1 SET f2=0 WHERE f3>=1 >>>>>>>>>>>>>>>>>>>>>>";
 119 echo create table t1(f1 int not null,f2 int,f3 int);
 120 Statement Executed
 121 echo insert into t1 values(1,2,3);
 122 Statement Executed: Rows Affected = 1
 123 echo insert into t1 values(2,NULL,4);
 124 Statement Executed: Rows Affected = 1
 125 echo insert into t1 values(3,4,NULL);
 126 Statement Executed: Rows Affected = 1
 127 echo insert into t1 values(10,20,30);
 128 Statement Executed: Rows Affected = 1
 129 echo insert into t1 values(11,NULL,NULL);
 130 Statement Executed: Rows Affected = 1
 131 echo commit;
 132 echo select * from t1;
 133 ---------------------------------------------------------
 134         t1.f1   t1.f2   t1.f3
 135 ---------------------------------------------------------
 136         1       2       3
 137         2       NULL    4
 138         3       4       NULL
 139         10      20      30
 140         11      NULL    NULL
 141
 142 echo UPDATE t1 SET f2=0 where f3>=1;
 143 Statement Executed: Rows Affected = 3
 144 echo commit;
 145 echo select * from t1;
 146 ---------------------------------------------------------
 147         t1.f1   t1.f2   t1.f3
 148 ---------------------------------------------------------
 149         1       0       3
 150         2       0       4
 151         3       4       NULL
 152         10      0       30
 153         11      NULL    NULL
 154
 155 echo drop table t1;
 156 Statement Executed
 157 echo "<<<<<<<<<<<<<<<<<<< DELETE FROM t1 WHERE f3>=4 >>>>>>>>>>>>>>>>>>>>>>>>>";
 158 echo create table t1(f1 int not null,f2 int,f3 int);
 159 Statement Executed
 160 echo insert into t1 values(1,2,3);
 161 Statement Executed: Rows Affected = 1
 162 echo insert into t1 values(2,NULL,4);
 163 Statement Executed: Rows Affected = 1
 164 echo insert into t1 values(3,4,NULL);
 165 Statement Executed: Rows Affected = 1
 166 echo insert into t1 values(10,20,30);
 167 Statement Executed: Rows Affected = 1
 168 echo insert into t1 values(11,NULL,NULL);
 169 Statement Executed: Rows Affected = 1
 170 echo commit;
 171 echo select * from t1;
 172 ---------------------------------------------------------
 173         t1.f1   t1.f2   t1.f3
 174 ---------------------------------------------------------
 175         1       2       3
 176         2       NULL    4
 177         3       4       NULL
 178         10      20      30
 179         11      NULL    NULL
 180
 181 echo DELETE FROM t1 WHERE f3>=4;
 182 Statement Executed: Rows Affected = 2
 183 echo commit;
 184 echo select * from t1;
 185 ---------------------------------------------------------
 186         t1.f1   t1.f2   t1.f3
 187 ---------------------------------------------------------
 188         1       2       3
 189         3       4       NULL
 190         11      NULL    NULL
 191
 192 echo drop table t1;
 193 Statement Executed