Annotation of embedaddon/sqlite3/test/tkt-80e031a00f.test, revision 1.1.1.1
1.1 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to tkt-80e031a00f.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test