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 289
