test/tools/csql/exp.test132.ksh

   1 \e[?1034hCSQL>create table t1(f1 int,f2 int);
   2 Statement Executed
   3 CSQL>insert into t1 values(12,23);
   4 Statement Executed: Rows Affected = 1
   5 CSQL>insert into t1 values(13,24);
   6 Statement Executed: Rows Affected = 1
   7 CSQL>insert into t1 values(14,25);
   8 Statement Executed: Rows Affected = 1
   9 CSQL>insert into t1 values(15,26);
  10 Statement Executed: Rows Affected = 1
  11 CSQL>insert into t1 values(NULL,27);
  12 Statement Executed: Rows Affected = 1
  13 CSQL>insert into t1 values(NULL,28);
  14 Statement Executed: Rows Affected = 1
  15 CSQL>insert into t1 values(16,29);
  16 Statement Executed: Rows Affected = 1
  17 CSQL>insert into t1 values(12,NULL);
  18 Statement Executed: Rows Affected = 1
  19 CSQL>insert into t1 values(17,NULL);
  20 Statement Executed: Rows Affected = 1
  21 CSQL>insert into t1 values(NULL,31);
  22 Statement Executed: Rows Affected = 1
  23 CSQL>insert into t1 values(12,32);
  24 Statement Executed: Rows Affected = 1
  25 CSQL>select * from t1;
  26 ---------------------------------------------------------
  27         t1.f1   t1.f2
  28 ---------------------------------------------------------
  29         12      23
  30         13      24
  31         14      25
  32         15      26
  33         NULL    27
  34         NULL    28
  35         16      29
  36         12      NULL
  37         17      NULL
  38         NULL    31
  39         12      32
  40
  41 CSQL>select * from t1 where f1 is null;
  42 ---------------------------------------------------------
  43         t1.f1   t1.f2
  44 ---------------------------------------------------------
  45         NULL    27
  46         NULL    28
  47         NULL    31
  48
  49 CSQL>select * from t1 where f1 is not null;
  50 ---------------------------------------------------------
  51         t1.f1   t1.f2
  52 ---------------------------------------------------------
  53         12      23
  54         13      24
  55         14      25
  56         15      26
  57         16      29
  58         12      NULL
  59         17      NULL
  60         12      32
  61
  62 CSQL>select * from t1 where f2 is null;
  63 ---------------------------------------------------------
  64         t1.f1   t1.f2
  65 ---------------------------------------------------------
  66         12      NULL
  67         17      NULL
  68
  69 CSQL>select * from t1 where f2 is not null;
  70 ---------------------------------------------------------
  71         t1.f1   t1.f2
  72 ---------------------------------------------------------
  73         12      23
  74         13      24
  75         14      25
  76         15      26
  77         NULL    27
  78         NULL    28
  79         16      29
  80         NULL    31
  81         12      32
  82
  83 CSQL>select * from t1 where f1 is null or f2 is not null;
  84 ---------------------------------------------------------
  85         t1.f1   t1.f2
  86 ---------------------------------------------------------
  87         12      23
  88         13      24
  89         14      25
  90         15      26
  91         NULL    27
  92         NULL    28
  93         16      29
  94         NULL    31
  95         12      32
  96
  97 CSQL>select * from t1 where f2 is null or f1 is not null;
  98 ---------------------------------------------------------
  99         t1.f1   t1.f2
 100 ---------------------------------------------------------
 101         12      23
 102         13      24
 103         14      25
 104         15      26
 105         16      29
 106         12      NULL
 107         17      NULL
 108         12      32
 109
 110 CSQL>drop table t1;
 111 Statement Executed
 112 CSQL>
 113 Statement prepare failed with error -19
 114 CSQL>