SQLite

Check-in [6851c517ac]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional test cases and evidence marks for the empty RHS bug on the IN operator - ticket [80e031a00f45dca877]
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6851c517ac7f80538aa5f3a1994fa1921c0ec3f4
User & Date: drh 2010-07-14 19:31:08.000
Context
2010-07-14
20:23
The expression list on the RHS of an IN operator can no longer be empty because an empty expression list is now optimized out by changes in check-in [c288ac644d0bf]. Therefore add ALWAYS() macros around tests for the expression list being non-empty. (check-in: fd130ae56c user: drh tags: trunk)
19:31
Additional test cases and evidence marks for the empty RHS bug on the IN operator - ticket [80e031a00f45dca877] (check-in: 6851c517ac user: drh tags: trunk)
18:24
Make the result of an IN or NOT IN expression with an empty set on the right-hand side always either false or true, respectively, even if the left-hand side is NULL. Ticket [80e031a00f45dc] (check-in: c288ac644d user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/tkt-80e031a00f.test.
︙︙
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

#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/malloc_common.tcl















do_execsql_test tkt-80e031a00f.1 {SELECT 1 IN ()} 0


do_execsql_test tkt-80e031a00f.2 {SELECT 1 NOT IN ()} 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.9 {
  CREATE TABLE t3(z INT UNIQUE);
  SELECT 1 IN t3;
} 0

do_execsql_test tkt-80e031a00f.13 {SELECT 1 NOT IN t3} 1





























































do_execsql_test tkt-80e031a00f.14 {SELECT null IN t3} 0

do_execsql_test tkt-80e031a00f.15 {SELECT null NOT IN t3} 1













































































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
>

>
>
















|



>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



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

#

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