1 # 2007 June 8

     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.  The

    12 # focus of this file is testing NULL comparisons in the WHERE clause.

    13 # See ticket #2404.

    14 #

    15 # $Id: where5.test,v 1.2 2007/06/08 08:43:10 drh Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # Build some test data

    21 #

    22 do_test where5-1.0 {

    23   execsql {

    24     CREATE TABLE t1(x TEXT);

    25     CREATE TABLE t2(x INTEGER);

    26     CREATE TABLE t3(x INTEGER PRIMARY KEY);

    27     INSERT INTO t1 VALUES(-1);

    28     INSERT INTO t1 VALUES(0);

    29     INSERT INTO t1 VALUES(1);

    30     INSERT INTO t2 SELECT * FROM t1;

    31     INSERT INTO t3 SELECT * FROM t2;

    32   }

    33   execsql {

    34     SELECT * FROM t1 WHERE x<0

    35   }

    36 } {-1}

    37 do_test where5-1.1 {

    38   execsql {

    39     SELECT * FROM t1 WHERE x<=0

    40   }

    41 } {-1 0}

    42 do_test where5-1.2 {

    43   execsql {

    44     SELECT * FROM t1 WHERE x=0

    45   }

    46 } {0}

    47 do_test where5-1.3 {

    48   execsql {

    49     SELECT * FROM t1 WHERE x>=0

    50   }

    51 } {0 1}

    52 do_test where5-1.4 {

    53   execsql {

    54     SELECT * FROM t1 WHERE x>0

    55   }

    56 } {1}

    57 do_test where5-1.5 {

    58   execsql {

    59     SELECT * FROM t1 WHERE x<>0

    60   }

    61 } {-1 1}

    62 do_test where5-1.6 {

    63   execsql {

    64     SELECT * FROM t1 WHERE x<NULL

    65   }

    66 } {}

    67 do_test where5-1.7 {

    68   execsql {

    69     SELECT * FROM t1 WHERE x<=NULL

    70   }

    71 } {}

    72 do_test where5-1.8 {

    73   execsql {

    74     SELECT * FROM t1 WHERE x=NULL

    75   }

    76 } {}

    77 do_test where5-1.9 {

    78   execsql {

    79     SELECT * FROM t1 WHERE x>=NULL

    80   }

    81 } {}

    82 do_test where5-1.10 {

    83   execsql {

    84     SELECT * FROM t1 WHERE x>NULL

    85   }

    86 } {}

    87 do_test where5-1.11 {

    88   execsql {

    89     SELECT * FROM t1 WHERE x!=NULL

    90   }

    91 } {}

    92 do_test where5-1.12 {

    93   execsql {

    94     SELECT * FROM t1 WHERE x IS NULL

    95   }

    96 } {}

    97 do_test where5-1.13 {

    98   execsql {

    99     SELECT * FROM t1 WHERE x IS NOT NULL

   100   }

   101 } {-1 0 1}

   102 

   103 

   104 do_test where5-2.0 {

   105   execsql {

   106     SELECT * FROM t2 WHERE x<0

   107   }

   108 } {-1}

   109 do_test where5-2.1 {

   110   execsql {

   111     SELECT * FROM t2 WHERE x<=0

   112   }

   113 } {-1 0}

   114 do_test where5-2.2 {

   115   execsql {

   116     SELECT * FROM t2 WHERE x=0

   117   }

   118 } {0}

   119 do_test where5-2.3 {

   120   execsql {

   121     SELECT * FROM t2 WHERE x>=0

   122   }

   123 } {0 1}

   124 do_test where5-2.4 {

   125   execsql {

   126     SELECT * FROM t2 WHERE x>0

   127   }

   128 } {1}

   129 do_test where5-2.5 {

   130   execsql {

   131     SELECT * FROM t2 WHERE x<>0

   132   }

   133 } {-1 1}

   134 do_test where5-2.6 {

   135   execsql {

   136     SELECT * FROM t2 WHERE x<NULL

   137   }

   138 } {}

   139 do_test where5-2.7 {

   140   execsql {

   141     SELECT * FROM t2 WHERE x<=NULL

   142   }

   143 } {}

   144 do_test where5-2.8 {

   145   execsql {

   146     SELECT * FROM t2 WHERE x=NULL

   147   }

   148 } {}

   149 do_test where5-2.9 {

   150   execsql {

   151     SELECT * FROM t2 WHERE x>=NULL

   152   }

   153 } {}

   154 do_test where5-2.10 {

   155   execsql {

   156     SELECT * FROM t2 WHERE x>NULL

   157   }

   158 } {}

   159 do_test where5-2.11 {

   160   execsql {

   161     SELECT * FROM t2 WHERE x!=NULL

   162   }

   163 } {}

   164 do_test where5-2.12 {

   165   execsql {

   166     SELECT * FROM t2 WHERE x IS NULL

   167   }

   168 } {}

   169 do_test where5-2.13 {

   170   execsql {

   171     SELECT * FROM t2 WHERE x IS NOT NULL

   172   }

   173 } {-1 0 1}

   174 

   175 

   176 do_test where5-3.0 {

   177   execsql {

   178     SELECT * FROM t3 WHERE x<0

   179   }

   180 } {-1}

   181 do_test where5-3.1 {

   182   execsql {

   183     SELECT * FROM t3 WHERE x<=0

   184   }

   185 } {-1 0}

   186 do_test where5-3.2 {

   187   execsql {

   188     SELECT * FROM t3 WHERE x=0

   189   }

   190 } {0}

   191 do_test where5-3.3 {

   192   execsql {

   193     SELECT * FROM t3 WHERE x>=0

   194   }

   195 } {0 1}

   196 do_test where5-3.4 {

   197   execsql {

   198     SELECT * FROM t3 WHERE x>0

   199   }

   200 } {1}

   201 do_test where5-3.5 {

   202   execsql {

   203     SELECT * FROM t3 WHERE x<>0

   204   }

   205 } {-1 1}

   206 do_test where5-3.6 {

   207   execsql {

   208     SELECT * FROM t3 WHERE x<NULL

   209   }

   210 } {}

   211 do_test where5-3.7 {

   212   execsql {

   213     SELECT * FROM t3 WHERE x<=NULL

   214   }

   215 } {}

   216 do_test where5-3.8 {

   217   execsql {

   218     SELECT * FROM t3 WHERE x=NULL

   219   }

   220 } {}

   221 do_test where5-3.9 {

   222   execsql {

   223     SELECT * FROM t3 WHERE x>=NULL

   224   }

   225 } {}

   226 do_test where5-3.10 {

   227   execsql {

   228     SELECT * FROM t3 WHERE x>NULL

   229   }

   230 } {}

   231 do_test where5-3.11 {

   232   execsql {

   233     SELECT * FROM t3 WHERE x!=NULL

   234   }

   235 } {}

   236 do_test where5-3.12 {

   237   execsql {

   238     SELECT * FROM t3 WHERE x IS NULL

   239   }

   240 } {}

   241 do_test where5-3.13 {

   242   execsql {

   243     SELECT * FROM t3 WHERE x IS NOT NULL

   244   }

   245 } {-1 0 1}

   246 

   247 do_test where5-4.0 {

   248   execsql {

   249     SELECT x<NULL FROM t3

   250   }

   251 } {{} {} {}}

   252 do_test where5-4.1 {

   253   execsql {

   254     SELECT x<=NULL FROM t3

   255   }

   256 } {{} {} {}}

   257 do_test where5-4.2 {

   258   execsql {

   259     SELECT x==NULL FROM t3

   260   }

   261 } {{} {} {}}

   262 do_test where5-4.3 {

   263   execsql {

   264     SELECT x>NULL FROM t3

   265   }

   266 } {{} {} {}}

   267 do_test where5-4.4 {

   268   execsql {

   269     SELECT x>=NULL FROM t3

   270   }

   271 } {{} {} {}}

   272 do_test where5-4.5 {

   273   execsql {

   274     SELECT x!=NULL FROM t3

   275   }

   276 } {{} {} {}}

   277 do_test where5-4.6 {

   278   execsql {

   279     SELECT x IS NULL FROM t3

   280   }

   281 } {0 0 0}

   282 do_test where5-4.7 {

   283   execsql {

   284     SELECT x IS NOT NULL FROM t3

   285   }

   286 } {1 1 1}

   287 

   288 finish_test