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 

   282 

   283 finish_test