test/null.test

   1 # 2001 September 15
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 # This file implements regression tests for SQLite library.
  12 #
  13 # This file implements tests for proper treatment of the special
  14 # value NULL.
  15 #
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19
  20 # Create a table and some data to work with.
  21 #
  22 do_test null-1.0 {
  23   execsql {
  24     begin;
  25     create table t1(a,b,c);
  26     insert into t1 values(1,0,0);
  27     insert into t1 values(2,0,1);
  28     insert into t1 values(3,1,0);
  29     insert into t1 values(4,1,1);
  30     insert into t1 values(5,null,0);
  31     insert into t1 values(6,null,1);
  32     insert into t1 values(7,null,null);
  33     commit;
  34     select * from t1;
  35   }
  36 } {1 0 0 2 0 1 3 1 0 4 1 1 5 {} 0 6 {} 1 7 {} {}}
  37
  38 # Check for how arithmetic expressions handle NULL
  39 #
  40 do_test null-1.1 {
  41   execsql {
  42     select ifnull(a+b,99) from t1;
  43   }
  44 } {1 2 4 5 99 99 99}
  45 do_test null-1.2 {
  46   execsql {
  47     select ifnull(b*c,99) from t1;
  48   }
  49 } {0 0 0 1 99 99 99}
  50
  51 # Check to see how the CASE expression handles NULL values.  The
  52 # first WHEN for which the test expression is TRUE is selected.
  53 # FALSE and UNKNOWN test expressions are skipped.
  54 #
  55 do_test null-2.1 {
  56   execsql {
  57     select ifnull(case when b<>0 then 1 else 0 end, 99) from t1;
  58   }
  59 } {0 0 1 1 0 0 0}
  60 do_test null-2.2 {
  61   execsql {
  62     select ifnull(case when not b<>0 then 1 else 0 end, 99) from t1;
  63   }
  64 } {1 1 0 0 0 0 0}
  65 do_test null-2.3 {
  66   execsql {
  67     select ifnull(case when b<>0 and c<>0 then 1 else 0 end, 99) from t1;
  68   }
  69 } {0 0 0 1 0 0 0}
  70 do_test null-2.4 {
  71   execsql {
  72     select ifnull(case when not (b<>0 and c<>0) then 1 else 0 end, 99) from t1;
  73   }
  74 } {1 1 1 0 1 0 0}
  75 do_test null-2.5 {
  76   execsql {
  77     select ifnull(case when b<>0 or c<>0 then 1 else 0 end, 99) from t1;
  78   }
  79 } {0 1 1 1 0 1 0}
  80 do_test null-2.6 {
  81   execsql {
  82     select ifnull(case when not (b<>0 or c<>0) then 1 else 0 end, 99) from t1;
  83   }
  84 } {1 0 0 0 0 0 0}
  85 do_test null-2.7 {
  86   execsql {
  87     select ifnull(case b when c then 1 else 0 end, 99) from t1;
  88   }
  89 } {1 0 0 1 0 0 0}
  90 do_test null-2.8 {
  91   execsql {
  92     select ifnull(case c when b then 1 else 0 end, 99) from t1;
  93   }
  94 } {1 0 0 1 0 0 0}
  95
  96 # Check to see that NULL values are ignored in aggregate functions.
  97 #
  98 do_test null-3.1 {
  99   execsql {
 100     select count(*), count(b), count(c), sum(b), sum(c),
 101            avg(b), avg(c), min(b), max(b) from t1;
 102   }
 103 } {7 4 6 2 3 0.5 0.5 0 1}
 104
 105 # The sum of zero entries is a NULL, but the total of zero entries is 0.
 106 #
 107 do_test null-3.2 {
 108   execsql {
 109     SELECT sum(b), total(b) FROM t1 WHERE b<0
 110   }
 111 } {{} 0.0}
 112
 113 # Check to see how WHERE clauses handle NULL values.  A NULL value
 114 # is the same as UNKNOWN.  The WHERE clause should only select those
 115 # rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
 116 #
 117 do_test null-4.1 {
 118   execsql {
 119     select a from t1 where b<10
 120   }
 121 } {1 2 3 4}
 122 do_test null-4.2 {
 123   execsql {
 124     select a from t1 where not b>10
 125   }
 126 } {1 2 3 4}
 127 do_test null-4.3 {
 128   execsql {
 129     select a from t1 where b<10 or c=1;
 130   }
 131 } {1 2 3 4 6}
 132 do_test null-4.4 {
 133   execsql {
 134     select a from t1 where b<10 and c=1;
 135   }
 136 } {2 4}
 137 do_test null-4.5 {
 138   execsql {
 139     select a from t1 where not (b<10 and c=1);
 140   }
 141 } {1 3 5}
 142
 143 # The DISTINCT keyword on a SELECT statement should treat NULL values
 144 # as distinct
 145 #
 146 do_test null-5.1 {
 147   execsql {
 148     select distinct b from t1 order by b;
 149   }
 150 } {{} 0 1}
 151
 152 # A UNION to two queries should treat NULL values
 153 # as distinct.
 154 #
 155 # (Later:)  We also take this opportunity to test the ability
 156 # of an ORDER BY clause to bind to either SELECT of a UNION.
 157 # The left-most SELECT is preferred.  In standard SQL, only
 158 # the left SELECT can be used.  The ability to match an ORDER
 159 # BY term to the right SELECT is an SQLite extension.
 160 #
 161 ifcapable compound {
 162   do_test null-6.1 {
 163     execsql {
 164       select b from t1 union select c from t1 order by b;
 165     }
 166   } {{} 0 1}
 167   do_test null-6.2 {
 168     execsql {
 169       select b from t1 union select c from t1 order by 1;
 170     }
 171   } {{} 0 1}
 172   do_test null-6.3 {
 173     execsql {
 174       select b from t1 union select c from t1 order by t1.b;
 175     }
 176   } {{} 0 1}
 177   do_test null-6.4 {
 178     execsql {
 179       select b from t1 union select c from t1 order by main.t1.b;
 180     }
 181   } {{} 0 1}
 182   do_test null-6.5 {
 183     catchsql {
 184       select b from t1 union select c from t1 order by t1.a;
 185     }
 186   } {1 {1st ORDER BY term does not match any column in the result set}}
 187   do_test null-6.6 {
 188     catchsql {
 189       select b from t1 union select c from t1 order by main.t1.a;
 190     }
 191   } {1 {1st ORDER BY term does not match any column in the result set}}
 192 } ;# ifcapable compound
 193
 194 # The UNIQUE constraint only applies to non-null values
 195 #
 196 ifcapable conflict {
 197 do_test null-7.1 {
 198     execsql {
 199       create table t2(a, b unique on conflict ignore);
 200       insert into t2 values(1,1);
 201       insert into t2 values(2,null);
 202       insert into t2 values(3,null);
 203       insert into t2 values(4,1);
 204       select a from t2;
 205     }
 206   } {1 2 3}
 207   do_test null-7.2 {
 208     execsql {
 209       create table t3(a, b, c, unique(b,c) on conflict ignore);
 210       insert into t3 values(1,1,1);
 211       insert into t3 values(2,null,1);
 212       insert into t3 values(3,null,1);
 213       insert into t3 values(4,1,1);
 214       select a from t3;
 215     }
 216   } {1 2 3}
 217 }
 218
 219 # Ticket #461 - Make sure nulls are handled correctly when doing a
 220 # lookup using an index.
 221 #
 222 do_test null-8.1 {
 223   execsql {
 224     CREATE TABLE t4(x,y);
 225     INSERT INTO t4 VALUES(1,11);
 226     INSERT INTO t4 VALUES(2,NULL);
 227     SELECT x FROM t4 WHERE y=NULL;
 228   }
 229 } {}
 230 ifcapable subquery {
 231   do_test null-8.2 {
 232     execsql {
 233       SELECT x FROM t4 WHERE y IN (33,NULL);
 234     }
 235   } {}
 236 }
 237 do_test null-8.3 {
 238   execsql {
 239     SELECT x FROM t4 WHERE y<33 ORDER BY x;
 240   }
 241 } {1}
 242 do_test null-8.4 {
 243   execsql {
 244     SELECT x FROM t4 WHERE y>6 ORDER BY x;
 245   }
 246 } {1}
 247 do_test null-8.5 {
 248   execsql {
 249     SELECT x FROM t4 WHERE y!=33 ORDER BY x;
 250   }
 251 } {1}
 252 do_test null-8.11 {
 253   execsql {
 254     CREATE INDEX t4i1 ON t4(y);
 255     SELECT x FROM t4 WHERE y=NULL;
 256   }
 257 } {}
 258 ifcapable subquery {
 259   do_test null-8.12 {
 260     execsql {
 261       SELECT x FROM t4 WHERE y IN (33,NULL);
 262     }
 263   } {}
 264 }
 265 do_test null-8.13 {
 266   execsql {
 267     SELECT x FROM t4 WHERE y<33 ORDER BY x;
 268   }
 269 } {1}
 270 do_test null-8.14 {
 271   execsql {
 272     SELECT x FROM t4 WHERE y>6 ORDER BY x;
 273   }
 274 } {1}
 275 do_test null-8.15 {
 276   execsql {
 277     SELECT x FROM t4 WHERE y!=33 ORDER BY x;
 278   }
 279 } {1}
 280
 281 do_execsql_test null-9.1 {
 282   CREATE TABLE t5(a, b, c);
 283   CREATE UNIQUE INDEX t5ab ON t5(a, b);
 284
 285   INSERT INTO t5 VALUES(1, NULL, 'one');
 286   INSERT INTO t5 VALUES(1, NULL, 'i');
 287   INSERT INTO t5 VALUES(NULL, 'x', 'two');
 288   INSERT INTO t5 VALUES(NULL, 'x', 'ii');
 289 }
 290
 291 do_execsql_test null-9.2 {
 292   SELECT * FROM t5 WHERE a = 1 AND b IS NULL;
 293 } {1 {} one 1 {} i}
 294
 295 do_execsql_test null-9.3 {
 296   SELECT * FROM t5 WHERE a IS NULL AND b = 'x';
 297 } {{} x two {} x ii}
 298
 299
 300 finish_test