wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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 |