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 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Build some test data 21# 22do_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} 37do_test where5-1.1 { 38 execsql { 39 SELECT * FROM t1 WHERE x<=0 40 } 41} {-1 0} 42do_test where5-1.2 { 43 execsql { 44 SELECT * FROM t1 WHERE x=0 45 } 46} {0} 47do_test where5-1.3 { 48 execsql { 49 SELECT * FROM t1 WHERE x>=0 50 } 51} {0 1} 52do_test where5-1.4 { 53 execsql { 54 SELECT * FROM t1 WHERE x>0 55 } 56} {1} 57do_test where5-1.5 { 58 execsql { 59 SELECT * FROM t1 WHERE x<>0 60 } 61} {-1 1} 62do_test where5-1.6 { 63 execsql { 64 SELECT * FROM t1 WHERE x<NULL 65 } 66} {} 67do_test where5-1.7 { 68 execsql { 69 SELECT * FROM t1 WHERE x<=NULL 70 } 71} {} 72do_test where5-1.8 { 73 execsql { 74 SELECT * FROM t1 WHERE x=NULL 75 } 76} {} 77do_test where5-1.9 { 78 execsql { 79 SELECT * FROM t1 WHERE x>=NULL 80 } 81} {} 82do_test where5-1.10 { 83 execsql { 84 SELECT * FROM t1 WHERE x>NULL 85 } 86} {} 87do_test where5-1.11 { 88 execsql { 89 SELECT * FROM t1 WHERE x!=NULL 90 } 91} {} 92do_test where5-1.12 { 93 execsql { 94 SELECT * FROM t1 WHERE x IS NULL 95 } 96} {} 97do_test where5-1.13 { 98 execsql { 99 SELECT * FROM t1 WHERE x IS NOT NULL 100 } 101} {-1 0 1} 102 103 104do_test where5-2.0 { 105 execsql { 106 SELECT * FROM t2 WHERE x<0 107 } 108} {-1} 109do_test where5-2.1 { 110 execsql { 111 SELECT * FROM t2 WHERE x<=0 112 } 113} {-1 0} 114do_test where5-2.2 { 115 execsql { 116 SELECT * FROM t2 WHERE x=0 117 } 118} {0} 119do_test where5-2.3 { 120 execsql { 121 SELECT * FROM t2 WHERE x>=0 122 } 123} {0 1} 124do_test where5-2.4 { 125 execsql { 126 SELECT * FROM t2 WHERE x>0 127 } 128} {1} 129do_test where5-2.5 { 130 execsql { 131 SELECT * FROM t2 WHERE x<>0 132 } 133} {-1 1} 134do_test where5-2.6 { 135 execsql { 136 SELECT * FROM t2 WHERE x<NULL 137 } 138} {} 139do_test where5-2.7 { 140 execsql { 141 SELECT * FROM t2 WHERE x<=NULL 142 } 143} {} 144do_test where5-2.8 { 145 execsql { 146 SELECT * FROM t2 WHERE x=NULL 147 } 148} {} 149do_test where5-2.9 { 150 execsql { 151 SELECT * FROM t2 WHERE x>=NULL 152 } 153} {} 154do_test where5-2.10 { 155 execsql { 156 SELECT * FROM t2 WHERE x>NULL 157 } 158} {} 159do_test where5-2.11 { 160 execsql { 161 SELECT * FROM t2 WHERE x!=NULL 162 } 163} {} 164do_test where5-2.12 { 165 execsql { 166 SELECT * FROM t2 WHERE x IS NULL 167 } 168} {} 169do_test where5-2.13 { 170 execsql { 171 SELECT * FROM t2 WHERE x IS NOT NULL 172 } 173} {-1 0 1} 174 175 176do_test where5-3.0 { 177 execsql { 178 SELECT * FROM t3 WHERE x<0 179 } 180} {-1} 181do_test where5-3.1 { 182 execsql { 183 SELECT * FROM t3 WHERE x<=0 184 } 185} {-1 0} 186do_test where5-3.2 { 187 execsql { 188 SELECT * FROM t3 WHERE x=0 189 } 190} {0} 191do_test where5-3.3 { 192 execsql { 193 SELECT * FROM t3 WHERE x>=0 194 } 195} {0 1} 196do_test where5-3.4 { 197 execsql { 198 SELECT * FROM t3 WHERE x>0 199 } 200} {1} 201do_test where5-3.5 { 202 execsql { 203 SELECT * FROM t3 WHERE x<>0 204 } 205} {-1 1} 206do_test where5-3.6 { 207 execsql { 208 SELECT * FROM t3 WHERE x<NULL 209 } 210} {} 211do_test where5-3.7 { 212 execsql { 213 SELECT * FROM t3 WHERE x<=NULL 214 } 215} {} 216do_test where5-3.8 { 217 execsql { 218 SELECT * FROM t3 WHERE x=NULL 219 } 220} {} 221do_test where5-3.9 { 222 execsql { 223 SELECT * FROM t3 WHERE x>=NULL 224 } 225} {} 226do_test where5-3.10 { 227 execsql { 228 SELECT * FROM t3 WHERE x>NULL 229 } 230} {} 231do_test where5-3.11 { 232 execsql { 233 SELECT * FROM t3 WHERE x!=NULL 234 } 235} {} 236do_test where5-3.12 { 237 execsql { 238 SELECT * FROM t3 WHERE x IS NULL 239 } 240} {} 241do_test where5-3.13 { 242 execsql { 243 SELECT * FROM t3 WHERE x IS NOT NULL 244 } 245} {-1 0 1} 246 247do_test where5-4.0 { 248 execsql { 249 SELECT x<NULL FROM t3 250 } 251} {{} {} {}} 252do_test where5-4.1 { 253 execsql { 254 SELECT x<=NULL FROM t3 255 } 256} {{} {} {}} 257do_test where5-4.2 { 258 execsql { 259 SELECT x==NULL FROM t3 260 } 261} {{} {} {}} 262do_test where5-4.3 { 263 execsql { 264 SELECT x>NULL FROM t3 265 } 266} {{} {} {}} 267do_test where5-4.4 { 268 execsql { 269 SELECT x>=NULL FROM t3 270 } 271} {{} {} {}} 272do_test where5-4.5 { 273 execsql { 274 SELECT x!=NULL FROM t3 275 } 276} {{} {} {}} 277do_test where5-4.6 { 278 execsql { 279 SELECT x IS NULL FROM t3 280 } 281} {0 0 0} 282do_test where5-4.7 { 283 execsql { 284 SELECT x IS NOT NULL FROM t3 285 } 286} {1 1 1} 287 288finish_test 289