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