test/sql/Predicate/exp.test002.ksh

   1 echo CREATE TABLE t1 (f1 INT , f2 VARCHAR (12));
   2 Statement Executed
   3 Statement Executed: Rows Affected = 1
   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 Executed: Rows Affected = 1
   9 Statement Executed: Rows Affected = 1
  10 echo select * from t1;
  11 ---------------------------------------------------------
  12         t1.f1   t1.f2
  13 ---------------------------------------------------------
  14         1       ABCD
  15         2       ABCDE
  16         3       ABCEF
  17         4       ABCCF
  18         5       ABCFF
  19         6       ABCDG
  20         7       ABDCG
  21
  22 echo select * from t1 where f2 like 'ABC%';
  23 ---------------------------------------------------------
  24         t1.f1   t1.f2
  25 ---------------------------------------------------------
  26         1       ABCD
  27         2       ABCDE
  28         3       ABCEF
  29         4       ABCCF
  30         5       ABCFF
  31         6       ABCDG
  32
  33 echo select * from t1 where f2 like 'ABCD%';
  34 ---------------------------------------------------------
  35         t1.f1   t1.f2
  36 ---------------------------------------------------------
  37         1       ABCD
  38         2       ABCDE
  39         6       ABCDG
  40
  41 echo select * from t1 where f2 like 'ABC%F';
  42 ---------------------------------------------------------
  43         t1.f1   t1.f2
  44 ---------------------------------------------------------
  45         3       ABCEF
  46         4       ABCCF
  47         5       ABCFF
  48
  49 echo select * from t1 where f2 like 'ABCD_';
  50 ---------------------------------------------------------
  51         t1.f1   t1.f2
  52 ---------------------------------------------------------
  53         2       ABCDE
  54         6       ABCDG
  55
  56 echo select * from t1 where f2 in ('ABCDE','ABCCF','ABCDG');
  57 ---------------------------------------------------------
  58         t1.f1   t1.f2
  59 ---------------------------------------------------------
  60         2       ABCDE
  61         4       ABCCF
  62         6       ABCDG
  63
  64 echo select * from t1 where f2 in ('ABCDE','ADCBF','ABCDG');
  65 ---------------------------------------------------------
  66         t1.f1   t1.f2
  67 ---------------------------------------------------------
  68         2       ABCDE
  69         6       ABCDG
  70
  71 echo select * from t1 where f2 in ('ABCDE','ADCBF',NULL);
  72 Statement prepare failed with error -19
  73 Statement Executed