test/tools/csql/exp.test042.ksh

   1 echo binary data type testing with primary key;
   2 echo create table t1 (f1 int, f2 binary(4) DEFAULT 'abcd12' ,primary key(f2));
   3 Statement Executed
   4 Statement Executed: Rows Affected = 1
   5 Statement Executed: Rows Affected = 1
   6 Statement Executed: Rows Affected = 1
   7 Statement Executed: Rows Affected = 1
   8 Statement execute failed with error -21
   9 Statement Executed: Rows Affected = 1
  10 Statement Executed: Rows Affected = 1
  11 echo select * from t1;
  12 ---------------------------------------------------------
  13         t1.f1   t1.f2
  14 ---------------------------------------------------------
  15         10      12FE
  16         10      ABCD12
  17         11      245AF
  18         12      2423FE
  19         -2147483648     DC23
  20         2147483647      323A34
  21
  22 echo select * from t1 where f2 = '12fe';
  23 ---------------------------------------------------------
  24         t1.f1   t1.f2
  25 ---------------------------------------------------------
  26         10      12FE
  27
  28 echo select * from t1 where f2 != '323a34' AND f1 = 13;
  29 ---------------------------------------------------------
  30         t1.f1   t1.f2
  31 ---------------------------------------------------------
  32
  33 echo select * from t1 where f1 = 12 AND f2 >= '245af';
  34 ---------------------------------------------------------
  35         t1.f1   t1.f2
  36 ---------------------------------------------------------
  37         Illegal Operator:Not Supported for Binary
  38 Illegal Operator:Not Supported for Binary
  39 Illegal Operator:Not Supported for Binary
  40 Illegal Operator:Not Supported for Binary
  41 Illegal Operator:Not Supported for Binary
  42 Illegal Operator:Not Supported for Binary
  43
  44 echo select * from t1 where f1 = 12 OR  f2 > 'dc23';
  45 ---------------------------------------------------------
  46         t1.f1   t1.f2
  47 ---------------------------------------------------------
  48         10      ABCD12
  49         11      245AF
  50         12      2423FE
  51         2147483647      323A34
  52
  53 echo select * from t1 where f1 = 12 OR f2 < 'dc23';
  54 ---------------------------------------------------------
  55         t1.f1   t1.f2
  56 ---------------------------------------------------------
  57         10      12FE
  58         12      2423FE
  59
  60 echo select * from t1 where f1 = 12 OR f2 <> '245af';
  61 ---------------------------------------------------------
  62         t1.f1   t1.f2
  63 ---------------------------------------------------------
  64         10      12FE
  65         10      ABCD12
  66         12      2423FE
  67         -2147483648     DC23
  68         2147483647      323A34
  69
  70 echo select * from t1 where NOT f2 ='12fe';
  71 ---------------------------------------------------------
  72         t1.f1   t1.f2
  73 ---------------------------------------------------------
  74         10      ABCD12
  75         11      245AF
  76         12      2423FE
  77         -2147483648     DC23
  78         2147483647      323A34
  79
  80 echo select * from t1 where f2 != '12fe';
  81 ---------------------------------------------------------
  82         t1.f1   t1.f2
  83 ---------------------------------------------------------
  84         10      ABCD12
  85         11      245AF
  86         12      2423FE
  87         -2147483648     DC23
  88         2147483647      323A34
  89
  90 echo select * from t1 where NOT  f2 != '12fe';
  91 ---------------------------------------------------------
  92         t1.f1   t1.f2
  93 ---------------------------------------------------------
  94         10      12FE
  95
  96 echo select * from t1 where f1 = f2;
  97 ---------------------------------------------------------
  98         t1.f1   t1.f2
  99 ---------------------------------------------------------
 100
 101 echo select * from t1 where f2 in ('245af','12fe');
 102 ---------------------------------------------------------
 103         t1.f1   t1.f2
 104 ---------------------------------------------------------
 105         10      12FE
 106         11      245AF
 107
 108 echo select * from t1 where f2 between '00000000' and 'ffffffff';
 109 ---------------------------------------------------------
 110         t1.f1   t1.f2
 111 ---------------------------------------------------------
 112         Illegal Operator:Not Supported for Binary
 113 Illegal Operator:Not Supported for Binary
 114 Illegal Operator:Not Supported for Binary
 115 Illegal Operator:Not Supported for Binary
 116 Illegal Operator:Not Supported for Binary
 117 Illegal Operator:Not Supported for Binary
 118 Illegal Operator:Not Supported for Binary
 119 Illegal Operator:Not Supported for Binary
 120 Illegal Operator:Not Supported for Binary
 121 Illegal Operator:Not Supported for Binary
 122 Illegal Operator:Not Supported for Binary
 123 Illegal Operator:Not Supported for Binary
 124
 125 echo select * from t1 where f2 between '122' and '12a2';
 126 ---------------------------------------------------------
 127         t1.f1   t1.f2
 128 ---------------------------------------------------------
 129         Illegal Operator:Not Supported for Binary
 130 Illegal Operator:Not Supported for Binary
 131 Illegal Operator:Not Supported for Binary
 132 Illegal Operator:Not Supported for Binary
 133 Illegal Operator:Not Supported for Binary
 134 Illegal Operator:Not Supported for Binary
 135 Illegal Operator:Not Supported for Binary
 136 Illegal Operator:Not Supported for Binary
 137 Illegal Operator:Not Supported for Binary
 138 Illegal Operator:Not Supported for Binary
 139 Illegal Operator:Not Supported for Binary
 140 Illegal Operator:Not Supported for Binary
 141
 142 echo update t1 set f2 = '435f3' where f1 != 13 and  f2='323a34' ;
 143 Statement prepare failed with error -21
 144 ---------------------------------------------------------
 145         t1.f1   t1.f2
 146 ---------------------------------------------------------
 147         10      12FE
 148         10      ABCD12
 149         11      245AF
 150         12      2423FE
 151         -2147483648     DC23
 152         2147483647      323A34
 153
 154 echo delete from t1 where f2 = '12fe';
 155 Statement Executed: Rows Affected = 1
 156 ---------------------------------------------------------
 157         t1.f1   t1.f2
 158 ---------------------------------------------------------
 159         10      ABCD12
 160         11      245AF
 161         12      2423FE
 162         -2147483648     DC23
 163         2147483647      323A34
 164
 165 Statement Executed