1: # 2001 September 15
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. The focus
12: # of this file is testing the interaction of manifest types, type affinity
13: # and comparison expressions.
14: #
15: # $Id: types2.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: # Tests in this file are organized roughly as follows:
21: #
22: # types2-1.*: The '=' operator in the absence of an index.
23: # types2-2.*: The '=' operator implemented using an index.
24: # types2-3.*: The '<' operator implemented using an index.
25: # types2-4.*: The '>' operator in the absence of an index.
26: # types2-5.*: The 'IN(x, y...)' operator in the absence of an index.
27: # types2-6.*: The 'IN(x, y...)' operator with an index.
28: # types2-7.*: The 'IN(SELECT...)' operator in the absence of an index.
29: # types2-8.*: The 'IN(SELECT...)' operator with an index.
30: #
31: # All tests test the operators using literals and columns, but no
32: # other types of expressions. All expressions except columns are
33: # handled similarly in the implementation.
34:
35: execsql {
36: CREATE TABLE t1(
37: i1 INTEGER,
38: i2 INTEGER,
39: n1 NUMERIC,
40: n2 NUMERIC,
41: t1 TEXT,
42: t2 TEXT,
43: o1 BLOB,
44: o2 BLOB
45: );
46: INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
47: }
48:
49: proc test_bool {testname vars expr res} {
50: if { $vars != "" } {
51: execsql "UPDATE t1 SET $vars"
52: }
53:
54: foreach {t e r} [list $testname $expr $res] {}
55:
56: do_test $t.1 "execsql {SELECT $e FROM t1}" $r
57: do_test $t.2 "execsql {SELECT 1 FROM t1 WHERE $expr}" [expr $r?"1":""]
58: do_test $t.3 "execsql {SELECT 1 FROM t1 WHERE NOT ($e)}" [expr $r?"":"1"]
59: }
60:
61: # Compare literals against literals. This should always use a numeric
62: # comparison.
63: #
64: # Changed by ticket #805: Use no affinity for literal comparisons.
65: #
66: test_bool types2-1.1 "" {500 = 500.0} 1
67: test_bool types2-1.2 "" {'500' = 500.0} 0
68: test_bool types2-1.3 "" {500 = '500.0'} 0
69: test_bool types2-1.4 "" {'500' = '500.0'} 0
70:
71: # Compare literals against a column with TEXT affinity
72: test_bool types2-1.5 {t1=500} {500 = t1} 1
73: test_bool types2-1.6 {t1=500} {'500' = t1} 1
74: test_bool types2-1.7 {t1=500} {500.0 = t1} 0
75: test_bool types2-1.8 {t1=500} {'500.0' = t1} 0
76: test_bool types2-1.9 {t1='500'} {500 = t1} 1
77: test_bool types2-1.10 {t1='500'} {'500' = t1} 1
78: test_bool types2-1.11 {t1='500'} {500.0 = t1} 0
79: test_bool types2-1.12 {t1='500'} {'500.0' = t1} 0
80:
81: # Compare literals against a column with NUMERIC affinity
82: test_bool types2-1.13 {n1=500} {500 = n1} 1
83: test_bool types2-1.14 {n1=500} {'500' = n1} 1
84: test_bool types2-1.15 {n1=500} {500.0 = n1} 1
85: test_bool types2-1.16 {n1=500} {'500.0' = n1} 1
86: test_bool types2-1.17 {n1='500'} {500 = n1} 1
87: test_bool types2-1.18 {n1='500'} {'500' = n1} 1
88: test_bool types2-1.19 {n1='500'} {500.0 = n1} 1
89: test_bool types2-1.20 {n1='500'} {'500.0' = n1} 1
90:
91: # Compare literals against a column with affinity NONE
92: test_bool types2-1.21 {o1=500} {500 = o1} 1
93: test_bool types2-1.22 {o1=500} {'500' = o1} 0
94: test_bool types2-1.23 {o1=500} {500.0 = o1} 1
95: test_bool types2-1.24 {o1=500} {'500.0' = o1} 0
96: test_bool types2-1.25 {o1='500'} {500 = o1} 0
97: test_bool types2-1.26 {o1='500'} {'500' = o1} 1
98: test_bool types2-1.27 {o1='500'} {500.0 = o1} 0
99: test_bool types2-1.28 {o1='500'} {'500.0' = o1} 0
100:
101: set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
102: # 1 2 3 4 5 6 7 8 9 10 11 12
103:
104: execsql {
105: CREATE TABLE t2(i INTEGER, n NUMERIC, t TEXT, o XBLOBY);
106: CREATE INDEX t2i1 ON t2(i);
107: CREATE INDEX t2i2 ON t2(n);
108: CREATE INDEX t2i3 ON t2(t);
109: CREATE INDEX t2i4 ON t2(o);
110: }
111: foreach v $vals {
112: execsql "INSERT INTO t2 VALUES($v, $v, $v, $v);"
113: }
114:
115: proc test_boolset {testname where set} {
116: set ::tb_sql "SELECT rowid FROM t2 WHERE $where"
117: do_test $testname {
118: lsort -integer [execsql $::tb_sql]
119: } $set
120: }
121:
122: test_boolset types2-2.1 {i = 10} {1 2 3 4}
123: test_boolset types2-2.2 {i = 10.0} {1 2 3 4}
124: test_boolset types2-2.3 {i = '10'} {1 2 3 4}
125: test_boolset types2-2.4 {i = '10.0'} {1 2 3 4}
126:
127: test_boolset types2-2.5 {n = 20} {5 6 7 8}
128: test_boolset types2-2.6 {n = 20.0} {5 6 7 8}
129: test_boolset types2-2.7 {n = '20'} {5 6 7 8}
130: test_boolset types2-2.8 {n = '20.0'} {5 6 7 8}
131:
132: test_boolset types2-2.9 {t = 20} {5 7}
133: test_boolset types2-2.10 {t = 20.0} {6 8}
134: test_boolset types2-2.11 {t = '20'} {5 7}
135: test_boolset types2-2.12 {t = '20.0'} {6 8}
136:
137: test_boolset types2-2.10 {o = 30} {9 10}
138: test_boolset types2-2.11 {o = 30.0} {9 10}
139: test_boolset types2-2.12 {o = '30'} 11
140: test_boolset types2-2.13 {o = '30.0'} 12
141:
142: test_boolset types2-3.1 {i < 20} {1 2 3 4}
143: test_boolset types2-3.2 {i < 20.0} {1 2 3 4}
144: test_boolset types2-3.3 {i < '20'} {1 2 3 4}
145: test_boolset types2-3.4 {i < '20.0'} {1 2 3 4}
146:
147: test_boolset types2-3.1 {n < 20} {1 2 3 4}
148: test_boolset types2-3.2 {n < 20.0} {1 2 3 4}
149: test_boolset types2-3.3 {n < '20'} {1 2 3 4}
150: test_boolset types2-3.4 {n < '20.0'} {1 2 3 4}
151:
152: test_boolset types2-3.1 {t < 20} {1 2 3 4}
153: test_boolset types2-3.2 {t < 20.0} {1 2 3 4 5 7}
154: test_boolset types2-3.3 {t < '20'} {1 2 3 4}
155: test_boolset types2-3.4 {t < '20.0'} {1 2 3 4 5 7}
156:
157: test_boolset types2-3.1 {o < 20} {1 2}
158: test_boolset types2-3.2 {o < 20.0} {1 2}
159: test_boolset types2-3.3 {o < '20'} {1 2 3 4 5 6 9 10}
160: test_boolset types2-3.3 {o < '20.0'} {1 2 3 4 5 6 7 9 10}
161:
162: # Compare literals against literals (always a numeric comparison).
163: # Change (by ticket #805): No affinity in comparisons
164: test_bool types2-4.1 "" {500 > 60.0} 1
165: test_bool types2-4.2 "" {'500' > 60.0} 1
166: test_bool types2-4.3 "" {500 > '60.0'} 0
167: test_bool types2-4.4 "" {'500' > '60.0'} 0
168:
169: # Compare literals against a column with TEXT affinity
170: test_bool types2-4.5 {t1=500.0} {t1 > 500} 1
171: test_bool types2-4.6 {t1=500.0} {t1 > '500' } 1
172: test_bool types2-4.7 {t1=500.0} {t1 > 500.0 } 0
173: test_bool types2-4.8 {t1=500.0} {t1 > '500.0' } 0
174: test_bool types2-4.9 {t1='500.0'} {t1 > 500 } 1
175: test_bool types2-4.10 {t1='500.0'} {t1 > '500' } 1
176: test_bool types2-4.11 {t1='500.0'} {t1 > 500.0 } 0
177: test_bool types2-4.12 {t1='500.0'} {t1 > '500.0' } 0
178:
179: # Compare literals against a column with NUMERIC affinity
180: test_bool types2-4.13 {n1=400} {500 > n1} 1
181: test_bool types2-4.14 {n1=400} {'500' > n1} 1
182: test_bool types2-4.15 {n1=400} {500.0 > n1} 1
183: test_bool types2-4.16 {n1=400} {'500.0' > n1} 1
184: test_bool types2-4.17 {n1='400'} {500 > n1} 1
185: test_bool types2-4.18 {n1='400'} {'500' > n1} 1
186: test_bool types2-4.19 {n1='400'} {500.0 > n1} 1
187: test_bool types2-4.20 {n1='400'} {'500.0' > n1} 1
188:
189: # Compare literals against a column with affinity NONE
190: test_bool types2-4.21 {o1=500} {500 > o1} 0
191: test_bool types2-4.22 {o1=500} {'500' > o1} 1
192: test_bool types2-4.23 {o1=500} {500.0 > o1} 0
193: test_bool types2-4.24 {o1=500} {'500.0' > o1} 1
194: test_bool types2-4.25 {o1='500'} {500 > o1} 0
195: test_bool types2-4.26 {o1='500'} {'500' > o1} 0
196: test_bool types2-4.27 {o1='500'} {500.0 > o1} 0
197: test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1
198:
199: ifcapable subquery {
200: # types2-5.* - The 'IN (x, y....)' operator with no index.
201: #
202: # Compare literals against literals (no affinity applied)
203: test_bool types2-5.1 {} {(NULL IN ('10.0', 20)) ISNULL} 1
204: test_bool types2-5.2 {} {10 IN ('10.0', 20)} 0
205: test_bool types2-5.3 {} {'10' IN ('10.0', 20)} 0
206: test_bool types2-5.4 {} {10 IN (10.0, 20)} 1
207: test_bool types2-5.5 {} {'10.0' IN (10, 20)} 0
208:
209: # Compare literals against a column with TEXT affinity
210: test_bool types2-5.6 {t1='10.0'} {t1 IN (10.0, 20)} 1
211: test_bool types2-5.7 {t1='10.0'} {t1 IN (10, 20)} 0
212: test_bool types2-5.8 {t1='10'} {t1 IN (10.0, 20)} 0
213: test_bool types2-5.9 {t1='10'} {t1 IN (20, '10.0')} 0
214: test_bool types2-5.10 {t1=10} {t1 IN (20, '10')} 1
215:
216: # Compare literals against a column with NUMERIC affinity
217: test_bool types2-5.11 {n1='10.0'} {n1 IN (10.0, 20)} 1
218: test_bool types2-5.12 {n1='10.0'} {n1 IN (10, 20)} 1
219: test_bool types2-5.13 {n1='10'} {n1 IN (10.0, 20)} 1
220: test_bool types2-5.14 {n1='10'} {n1 IN (20, '10.0')} 1
221: test_bool types2-5.15 {n1=10} {n1 IN (20, '10')} 1
222:
223: # Compare literals against a column with affinity NONE
224: test_bool types2-5.16 {o1='10.0'} {o1 IN (10.0, 20)} 0
225: test_bool types2-5.17 {o1='10.0'} {o1 IN (10, 20)} 0
226: test_bool types2-5.18 {o1='10'} {o1 IN (10.0, 20)} 0
227: test_bool types2-5.19 {o1='10'} {o1 IN (20, '10.0')} 0
228: test_bool types2-5.20 {o1=10} {o1 IN (20, '10')} 0
229: test_bool types2-5.21 {o1='10.0'} {o1 IN (10, 20, '10.0')} 1
230: test_bool types2-5.22 {o1='10'} {o1 IN (10.0, 20, '10')} 1
231: test_bool types2-5.23 {o1=10} {n1 IN (20, '10', 10)} 1
232:
233: # Ticket #2248: Comparisons of strings literals that look like
234: # numbers.
235: test_bool types2-5.24 {} {'1' IN ('1')} 1
236: test_bool types2-5.25 {} {'2' IN (2)} 0
237: test_bool types2-5.26 {} {3 IN ('3')} 0
238: test_bool types2-5.27 {} {4 IN (4)} 1
239:
240: # The affinity of columns on the right side of IN(...) is ignored.
241: # All values in the expression list are treated as ordinary expressions,
242: # even if they are columns with affinity.
243: test_bool types2-5.30 {t1='10'} {10 IN (5,t1,'abc')} 0
244: test_bool types2-5.31 {t1='10'} {10 IN ('abc',t1,5)} 0
245: test_bool types2-5.32 {t1='010'} {10 IN (5,t1,'abc')} 0
246: test_bool types2-5.33 {t1='010'} {10 IN ('abc',t1,5)} 0
247: test_bool types2-5.34 {t1='10'} {'10' IN (5,t1,'abc')} 1
248: test_bool types2-5.35 {t1='10'} {'10' IN ('abc',t1,5)} 1
249: test_bool types2-5.36 {t1='010'} {'10' IN (5,t1,'abc')} 0
250: test_bool types2-5.37 {t1='010'} {'10' IN ('abc',t1,5)} 0
251:
252: # Columns on both the left and right of IN(...). Only the column
253: # on the left matters. The all values on the right are treated like
254: # expressions.
255: test_bool types2-5.40 {t1='10',n1=10} {t1 IN (5,n1,11)} 1
256: test_bool types2-5.41 {t1='010',n1=10} {t1 IN (5,n1,11)} 0
257: test_bool types2-5.42 {t1='10',n1=10} {n1 IN (5,t1,11)} 1
258: test_bool types2-5.43 {t1='010',n1=10} {n1 IN (5,t1,11)} 1
259: }
260:
261: # Tests named types2-6.* use the same infrastructure as the types2-2.*
262: # tests. The contents of the vals array is repeated here for easy
263: # reference.
264: #
265: # set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
266: # 1 2 3 4 5 6 7 8 9 10 11 12
267:
268: ifcapable subquery {
269: test_boolset types2-6.1 {o IN ('10', 30)} {3 9 10}
270: test_boolset types2-6.2 {o IN (20.0, 30.0)} {5 6 9 10}
271: test_boolset types2-6.3 {t IN ('10', 30)} {1 3 9 11}
272: test_boolset types2-6.4 {t IN (20.0, 30.0)} {6 8 10 12}
273: test_boolset types2-6.5 {n IN ('10', 30)} {1 2 3 4 9 10 11 12}
274: test_boolset types2-6.6 {n IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}
275: test_boolset types2-6.7 {i IN ('10', 30)} {1 2 3 4 9 10 11 12}
276: test_boolset types2-6.8 {i IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}
277:
278: # Also test than IN(x, y, z) works on a rowid:
279: test_boolset types2-6.9 {rowid IN (1, 6, 10)} {1 6 10}
280: }
281:
282: # Tests types2-7.* concentrate on expressions of the form
283: # "x IN (SELECT...)" with no index.
284: execsql {
285: CREATE TABLE t3(i INTEGER, n NUMERIC, t TEXT, o BLOB);
286: INSERT INTO t3 VALUES(1, 1, 1, 1);
287: INSERT INTO t3 VALUES(2, 2, 2, 2);
288: INSERT INTO t3 VALUES(3, 3, 3, 3);
289: INSERT INTO t3 VALUES('1', '1', '1', '1');
290: INSERT INTO t3 VALUES('1.0', '1.0', '1.0', '1.0');
291: }
292:
293: ifcapable subquery {
294: test_bool types2-7.1 {i1=1} {i1 IN (SELECT i FROM t3)} 1
295: test_bool types2-7.2 {i1='2.0'} {i1 IN (SELECT i FROM t3)} 1
296: test_bool types2-7.3 {i1='2.0'} {i1 IN (SELECT n FROM t3)} 1
297: test_bool types2-7.4 {i1='2.0'} {i1 IN (SELECT t FROM t3)} 1
298: test_bool types2-7.5 {i1='2.0'} {i1 IN (SELECT o FROM t3)} 1
299:
300: test_bool types2-7.6 {n1=1} {n1 IN (SELECT n FROM t3)} 1
301: test_bool types2-7.7 {n1='2.0'} {n1 IN (SELECT i FROM t3)} 1
302: test_bool types2-7.8 {n1='2.0'} {n1 IN (SELECT n FROM t3)} 1
303: test_bool types2-7.9 {n1='2.0'} {n1 IN (SELECT t FROM t3)} 1
304: test_bool types2-7.10 {n1='2.0'} {n1 IN (SELECT o FROM t3)} 1
305:
306: test_bool types2-7.6 {t1=1} {t1 IN (SELECT t FROM t3)} 1
307: test_bool types2-7.7 {t1='2.0'} {t1 IN (SELECT t FROM t3)} 0
308: test_bool types2-7.8 {t1='2.0'} {t1 IN (SELECT n FROM t3)} 1
309: test_bool types2-7.9 {t1='2.0'} {t1 IN (SELECT i FROM t3)} 1
310: test_bool types2-7.10 {t1='2.0'} {t1 IN (SELECT o FROM t3)} 0
311: test_bool types2-7.11 {t1='1.0'} {t1 IN (SELECT t FROM t3)} 1
312: test_bool types2-7.12 {t1='1.0'} {t1 IN (SELECT o FROM t3)} 1
313:
314: test_bool types2-7.13 {o1=2} {o1 IN (SELECT o FROM t3)} 1
315: test_bool types2-7.14 {o1='2'} {o1 IN (SELECT o FROM t3)} 0
316: test_bool types2-7.15 {o1='2'} {o1 IN (SELECT o||'' FROM t3)} 1
317: }
318:
319: # set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
320: # 1 2 3 4 5 6 7 8 9 10 11 12
321: execsql {
322: CREATE TABLE t4(i INTEGER, n NUMERIC, t VARCHAR(20), o LARGE BLOB);
323: INSERT INTO t4 VALUES(10, 20, 20, 30);
324: }
325: ifcapable subquery {
326: test_boolset types2-8.1 {i IN (SELECT i FROM t4)} {1 2 3 4}
327: test_boolset types2-8.2 {n IN (SELECT i FROM t4)} {1 2 3 4}
328: test_boolset types2-8.3 {t IN (SELECT i FROM t4)} {1 2 3 4}
329: test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4}
330: test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8}
331: test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8}
332: test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7}
333: test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7}
334: test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12}
335: test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12}
336: test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {9 11}
337: test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10}
338: }
339:
340: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to types2.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