1 # 2010 July 14 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. Specifically, 12 # it tests that ticket [80e031a00f45dca877ed92b225209cfa09280f4f] has been 13 # resolved. That ticket is about IN and NOT IN operators with empty-set 14 # right-hand sides. Such expressions should always return TRUE or FALSE 15 # even if the left-hand side is NULL. 16 # 17 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 source $testdir/lock_common.tcl 21 source $testdir/malloc_common.tcl 22 23 # EVIDENCE-OF: R-58875-56087 The IN and NOT IN operators take a single 24 # scalar operand on the left and a vector operand on the right formed by 25 # an explicit list of zero or more scalars or by a single subquery. 26 # 27 # EVIDENCE-OF: R-52275-55503 When the right operand is an empty set, the 28 # result of IN is false and the result of NOT IN is true, regardless of 29 # the left operand and even if the left operand is NULL. 30 # 31 # EVIDENCE-OF: R-13595-45863 Note that SQLite allows the parenthesized 32 # list of scalar values on the right-hand side of an IN or NOT IN 33 # operator to be an empty list but most other SQL database database 34 # engines and the SQL92 standard require the list to contain at least 35 # one element. 36 # 37 do_execsql_test tkt-80e031a00f.1 {SELECT 1 IN ()} 0 38 do_execsql_test tkt-80e031a00f.1b {SELECT 1 IN (2)} 0 39 do_execsql_test tkt-80e031a00f.1c {SELECT 1 IN (2,3,4,5,6,7,8,9)} 0 40 do_execsql_test tkt-80e031a00f.2 {SELECT 1 NOT IN ()} 1 41 do_execsql_test tkt-80e031a00f.2b {SELECT 1 NOT IN (2)} 1 42 do_execsql_test tkt-80e031a00f.2c {SELECT 1 NOT IN (2,3,4,5,6,7,8,9)} 1 43 do_execsql_test tkt-80e031a00f.3 {SELECT null IN ()} 0 44 do_execsql_test tkt-80e031a00f.4 {SELECT null NOT IN ()} 1 45 do_execsql_test tkt-80e031a00f.5 { 46 CREATE TABLE t1(x); 47 SELECT 1 IN t1; 48 } 0 49 do_execsql_test tkt-80e031a00f.6 {SELECT 1 NOT IN t1} 1 50 do_execsql_test tkt-80e031a00f.7 {SELECT null IN t1} 0 51 do_execsql_test tkt-80e031a00f.8 {SELECT null NOT IN t1} 1 52 do_execsql_test tkt-80e031a00f.9 { 53 CREATE TABLE t2(y INTEGER PRIMARY KEY); 54 SELECT 1 IN t2; 55 } 0 56 do_execsql_test tkt-80e031a00f.10 {SELECT 1 NOT IN t2} 1 57 do_execsql_test tkt-80e031a00f.11 {SELECT null IN t2} 0 58 do_execsql_test tkt-80e031a00f.12 {SELECT null NOT IN t2} 1 59 do_execsql_test tkt-80e031a00f.13 { 60 CREATE TABLE t3(z INT UNIQUE); 61 SELECT 1 IN t3; 62 } 0 63 do_execsql_test tkt-80e031a00f.14 {SELECT 1 NOT IN t3} 1 64 do_execsql_test tkt-80e031a00f.15 {SELECT null IN t3} 0 65 do_execsql_test tkt-80e031a00f.16 {SELECT null NOT IN t3} 1 66 do_execsql_test tkt-80e031a00f.17 {SELECT 1 IN (SELECT x+y FROM t1, t2)} 0 67 do_execsql_test tkt-80e031a00f.18 {SELECT 1 NOT IN (SELECT x+y FROM t1,t2)} 1 68 do_execsql_test tkt-80e031a00f.19 {SELECT null IN (SELECT x+y FROM t1,t2)} 0 69 do_execsql_test tkt-80e031a00f.20 {SELECT null NOT IN (SELECT x+y FROM t1,t2)} 1 70 do_execsql_test tkt-80e031a00f.21 {SELECT 1.23 IN ()} 0 71 do_execsql_test tkt-80e031a00f.22 {SELECT 1.23 NOT IN ()} 1 72 do_execsql_test tkt-80e031a00f.23 {SELECT 1.23 IN t1} 0 73 do_execsql_test tkt-80e031a00f.24 {SELECT 1.23 NOT IN t1} 1 74 do_execsql_test tkt-80e031a00f.25 {SELECT 'hello' IN ()} 0 75 do_execsql_test tkt-80e031a00f.26 {SELECT 'hello' NOT IN ()} 1 76 do_execsql_test tkt-80e031a00f.27 {SELECT 'hello' IN t1} 0 77 do_execsql_test tkt-80e031a00f.28 {SELECT 'hello' NOT IN t1} 1 78 do_execsql_test tkt-80e031a00f.29 {SELECT x'303132' IN ()} 0 79 do_execsql_test tkt-80e031a00f.30 {SELECT x'303132' NOT IN ()} 1 80 do_execsql_test tkt-80e031a00f.31 {SELECT x'303132' IN t1} 0 81 do_execsql_test tkt-80e031a00f.32 {SELECT x'303132' NOT IN t1} 1 82 83 # EVIDENCE-OF: R-50221-42915 The result of an IN or NOT IN operator is 84 # determined by the following matrix: Left operand is NULL Right operand 85 # contains NULL Right operand is an empty set Left operand found within 86 # right operand Result of IN operator Result of NOT IN operator no no no 87 # no false true does not matter no yes no false true no does not matter 88 # no yes true false no yes no no NULL NULL yes does not matter no does 89 # not matter NULL NULL 90 # 91 # Row 1: 92 do_execsql_test tkt-80e031a00f.100 {SELECT 1 IN (2,3,4)} 0 93 do_execsql_test tkt-80e031a00f.101 {SELECT 1 NOT IN (2,3,4)} 1 94 do_execsql_test tkt-80e031a00f.102 {SELECT 'a' IN ('b','c','d')} 0 95 do_execsql_test tkt-80e031a00f.103 {SELECT 'a' NOT IN ('b','c','d')} 1 96 do_test tkt-80e031a00f.104 { 97 db eval { 98 CREATE TABLE t4(a UNIQUE); 99 CREATE TABLE t5(b INTEGER PRIMARY KEY); 100 CREATE TABLE t6(c); 101 INSERT INTO t4 VALUES(2); 102 INSERT INTO t4 VALUES(3); 103 INSERT INTO t4 VALUES(4); 104 INSERT INTO t5 SELECT * FROM t4; 105 INSERT INTO t6 SELECT * FROM t4; 106 CREATE TABLE t4n(a UNIQUE); 107 CREATE TABLE t6n(c); 108 INSERT INTO t4n SELECT * FROM t4; 109 INSERT INTO t4n VALUES(null); 110 INSERT INTO t6n SELECT * FROM t4n; 111 CREATE TABLE t7(a UNIQUE); 112 CREATE TABLE t8(c); 113 INSERT INTO t7 VALUES('b'); 114 INSERT INTO t7 VALUES('c'); 115 INSERT INTO t7 VALUES('d'); 116 INSERT INTO t8 SELECT * FROM t7; 117 CREATE TABLE t7n(a UNIQUE); 118 CREATE TABLE t8n(c); 119 INSERT INTO t7n SELECT * FROM t7; 120 INSERT INTO t7n VALUES(null); 121 INSERT INTO t8n SELECT * FROM t7n; 122 } 123 execsql {SELECT 1 IN t4} 124 } 0 125 do_execsql_test tkt-80e031a00f.105 {SELECT 1 NOT IN t4} 1 126 do_execsql_test tkt-80e031a00f.106 {SELECT 1 IN t5} 0 127 do_execsql_test tkt-80e031a00f.107 {SELECT 1 NOT IN t5} 1 128 do_execsql_test tkt-80e031a00f.108 {SELECT 1 IN t6} 0 129 do_execsql_test tkt-80e031a00f.109 {SELECT 1 NOT IN t6} 1 130 do_execsql_test tkt-80e031a00f.110 {SELECT 'a' IN t7} 0 131 do_execsql_test tkt-80e031a00f.111 {SELECT 'a' NOT IN t7} 1 132 do_execsql_test tkt-80e031a00f.112 {SELECT 'a' IN t8} 0 133 do_execsql_test tkt-80e031a00f.113 {SELECT 'a' NOT IN t8} 1 134 # 135 # Row 2 is tested by cases 1-32 above. 136 # Row 3: 137 do_execsql_test tkt-80e031a00f.300 {SELECT 2 IN (2,3,4,null)} 1 138 do_execsql_test tkt-80e031a00f.301 {SELECT 3 NOT IN (2,3,4,null)} 0 139 do_execsql_test tkt-80e031a00f.302 {SELECT 4 IN (2,3,4)} 1 140 do_execsql_test tkt-80e031a00f.303 {SELECT 2 NOT IN (2,3,4)} 0 141 do_execsql_test tkt-80e031a00f.304 {SELECT 'b' IN ('b','c','d')} 1 142 do_execsql_test tkt-80e031a00f.305 {SELECT 'c' NOT IN ('b','c','d')} 0 143 do_execsql_test tkt-80e031a00f.306 {SELECT 'd' IN ('b','c',null,'d')} 1 144 do_execsql_test tkt-80e031a00f.307 {SELECT 'b' NOT IN (null,'b','c','d')} 0 145 do_execsql_test tkt-80e031a00f.308 {SELECT 2 IN t4} 1 146 do_execsql_test tkt-80e031a00f.309 {SELECT 3 NOT IN t4} 0 147 do_execsql_test tkt-80e031a00f.310 {SELECT 4 IN t4n} 1 148 do_execsql_test tkt-80e031a00f.311 {SELECT 2 NOT IN t4n} 0 149 do_execsql_test tkt-80e031a00f.312 {SELECT 2 IN t5} 1 150 do_execsql_test tkt-80e031a00f.313 {SELECT 3 NOT IN t5} 0 151 do_execsql_test tkt-80e031a00f.314 {SELECT 2 IN t6} 1 152 do_execsql_test tkt-80e031a00f.315 {SELECT 3 NOT IN t6} 0 153 do_execsql_test tkt-80e031a00f.316 {SELECT 4 IN t6n} 1 154 do_execsql_test tkt-80e031a00f.317 {SELECT 2 NOT IN t6n} 0 155 do_execsql_test tkt-80e031a00f.318 {SELECT 'b' IN t7} 1 156 do_execsql_test tkt-80e031a00f.319 {SELECT 'c' NOT IN t7} 0 157 do_execsql_test tkt-80e031a00f.320 {SELECT 'c' IN t7n} 1 158 do_execsql_test tkt-80e031a00f.321 {SELECT 'd' NOT IN t7n} 0 159 do_execsql_test tkt-80e031a00f.322 {SELECT 'b' IN t8} 1 160 do_execsql_test tkt-80e031a00f.323 {SELECT 'c' NOT IN t8} 0 161 do_execsql_test tkt-80e031a00f.324 {SELECT 'c' IN t8n} 1 162 do_execsql_test tkt-80e031a00f.325 {SELECT 'd' NOT IN t8n} 0 163 # 164 # Row 4: 165 do_execsql_test tkt-80e031a00f.400 {SELECT 1 IN (2,3,4,null)} {{}} 166 do_execsql_test tkt-80e031a00f.401 {SELECT 1 NOT IN (2,3,4,null)} {{}} 167 do_execsql_test tkt-80e031a00f.402 {SELECT 'a' IN ('b','c',null,'d')} {{}} 168 do_execsql_test tkt-80e031a00f.403 {SELECT 'a' NOT IN (null,'b','c','d')} {{}} 169 do_execsql_test tkt-80e031a00f.404 {SELECT 1 IN t4n} {{}} 170 do_execsql_test tkt-80e031a00f.405 {SELECT 5 NOT IN t4n} {{}} 171 do_execsql_test tkt-80e031a00f.406 {SELECT 6 IN t6n} {{}} 172 do_execsql_test tkt-80e031a00f.407 {SELECT 7 NOT IN t6n} {{}} 173 do_execsql_test tkt-80e031a00f.408 {SELECT 'a' IN t7n} {{}} 174 do_execsql_test tkt-80e031a00f.409 {SELECT 'e' NOT IN t7n} {{}} 175 do_execsql_test tkt-80e031a00f.410 {SELECT 'f' IN t8n} {{}} 176 do_execsql_test tkt-80e031a00f.411 {SELECT 'g' NOT IN t8n} {{}} 177 # 178 # Row 5: 179 do_execsql_test tkt-80e031a00f.500 {SELECT null IN (2,3,4,null)} {{}} 180 do_execsql_test tkt-80e031a00f.501 {SELECT null NOT IN (2,3,4,null)} {{}} 181 do_execsql_test tkt-80e031a00f.502 {SELECT null IN (2,3,4)} {{}} 182 do_execsql_test tkt-80e031a00f.503 {SELECT null NOT IN (2,3,4)} {{}} 183 do_execsql_test tkt-80e031a00f.504 {SELECT null IN ('b','c','d')} {{}} 184 do_execsql_test tkt-80e031a00f.505 {SELECT null NOT IN ('b','c','d')} {{}} 185 do_execsql_test tkt-80e031a00f.506 {SELECT null IN ('b','c',null,'d')} {{}} 186 do_execsql_test tkt-80e031a00f.507 {SELECT null NOT IN (null,'b','c','d')} {{}} 187 do_execsql_test tkt-80e031a00f.508 {SELECT null IN t4} {{}} 188 do_execsql_test tkt-80e031a00f.509 {SELECT null NOT IN t4} {{}} 189 do_execsql_test tkt-80e031a00f.510 {SELECT null IN t4n} {{}} 190 do_execsql_test tkt-80e031a00f.511 {SELECT null NOT IN t4n} {{}} 191 do_execsql_test tkt-80e031a00f.512 {SELECT null IN t5} {{}} 192 do_execsql_test tkt-80e031a00f.513 {SELECT null NOT IN t5} {{}} 193 do_execsql_test tkt-80e031a00f.514 {SELECT null IN t6} {{}} 194 do_execsql_test tkt-80e031a00f.515 {SELECT null NOT IN t6} {{}} 195 do_execsql_test tkt-80e031a00f.516 {SELECT null IN t6n} {{}} 196 do_execsql_test tkt-80e031a00f.517 {SELECT null NOT IN t6n} {{}} 197 do_execsql_test tkt-80e031a00f.518 {SELECT null IN t7} {{}} 198 do_execsql_test tkt-80e031a00f.519 {SELECT null NOT IN t7} {{}} 199 do_execsql_test tkt-80e031a00f.520 {SELECT null IN t7n} {{}} 200 do_execsql_test tkt-80e031a00f.521 {SELECT null NOT IN t7n} {{}} 201 do_execsql_test tkt-80e031a00f.522 {SELECT null IN t8} {{}} 202 do_execsql_test tkt-80e031a00f.523 {SELECT null NOT IN t8} {{}} 203 do_execsql_test tkt-80e031a00f.524 {SELECT null IN t8n} {{}} 204 do_execsql_test tkt-80e031a00f.525 {SELECT null NOT IN t8n} {{}} 205 206 finish_test 207
