wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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.7 2007/02/23 03:00:45 drh 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 |