wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2009 August 13
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
12 # focus of this file is testing WHERE clause conditions with
13 # subtle affinity issues.
14 #
15  
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18  
19 # For this set of tests:
20 #
21 # * t1.y holds an integer value with affinity NONE
22 # * t2.b holds a text value with affinity TEXT
23 #
24 # These values are not equal and because neither affinity is NUMERIC
25 # no type conversion occurs.
26 #
27 do_test whereB-1.1 {
28 db eval {
29 CREATE TABLE t1(x,y); -- affinity of t1.y is NONE
30 INSERT INTO t1 VALUES(1,99);
31  
32 CREATE TABLE t2(a, b TEXT); -- affinity of t2.b is TEXT
33 CREATE INDEX t2b ON t2(b);
34 INSERT INTO t2 VALUES(2,99);
35  
36 SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
37 }
38 } {1 2 0}
39 do_test whereB-1.2 {
40 db eval {
41 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
42 }
43 } {}
44 do_test whereB-1.3 {
45 db eval {
46 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
47 }
48 } {}
49 do_test whereB-1.4 {
50 db eval {
51 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
52 }
53 } {}
54 do_test whereB-1.100 {
55 db eval {
56 DROP INDEX t2b;
57 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
58 }
59 } {}
60 do_test whereB-1.101 {
61 db eval {
62 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
63 }
64 } {}
65 do_test whereB-1.102 {
66 db eval {
67 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
68 }
69 } {}
70  
71 # For this set of tests:
72 #
73 # * t1.y holds a text value with affinity TEXT
74 # * t2.b holds an integer value with affinity NONE
75 #
76 # These values are not equal and because neither affinity is NUMERIC
77 # no type conversion occurs.
78 #
79 do_test whereB-2.1 {
80 db eval {
81 DROP TABLE t1;
82 DROP TABLE t2;
83  
84 CREATE TABLE t1(x, y TEXT); -- affinity of t1.y is TEXT
85 INSERT INTO t1 VALUES(1,99);
86  
87 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
88 CREATE INDEX t2b ON t2(b);
89 INSERT INTO t2 VALUES(2,99);
90  
91 SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
92 }
93 } {1 2 0}
94 do_test whereB-2.2 {
95 db eval {
96 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
97 }
98 } {}
99 do_test whereB-2.3 {
100 db eval {
101 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
102 }
103 } {}
104 do_test whereB-2.4 {
105 db eval {
106 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
107 }
108 } {}
109 do_test whereB-2.100 {
110 db eval {
111 DROP INDEX t2b;
112 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
113 }
114 } {}
115 do_test whereB-2.101 {
116 db eval {
117 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
118 }
119 } {}
120 do_test whereB-2.102 {
121 db eval {
122 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
123 }
124 } {}
125  
126 # For this set of tests:
127 #
128 # * t1.y holds a text value with affinity NONE
129 # * t2.b holds an integer value with affinity NONE
130 #
131 # These values are not equal and because neither affinity is NUMERIC
132 # no type conversion occurs.
133 #
134 do_test whereB-3.1 {
135 db eval {
136 DROP TABLE t1;
137 DROP TABLE t2;
138  
139 CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE
140 INSERT INTO t1 VALUES(1,99);
141  
142 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
143 CREATE INDEX t2b ON t2(b);
144 INSERT INTO t2 VALUES(2,'99');
145  
146 SELECT x, a, y=b FROM t1, t2;
147 }
148 } {1 2 0}
149 do_test whereB-3.2 {
150 db eval {
151 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
152 }
153 } {}
154 do_test whereB-3.3 {
155 db eval {
156 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
157 }
158 } {}
159 do_test whereB-3.4 {
160 db eval {
161 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
162 }
163 } {}
164 do_test whereB-3.100 {
165 db eval {
166 DROP INDEX t2b;
167 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
168 }
169 } {}
170 do_test whereB-3.101 {
171 db eval {
172 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
173 }
174 } {}
175 do_test whereB-3.102 {
176 db eval {
177 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
178 }
179 } {}
180  
181  
182 # For this set of tests:
183 #
184 # * t1.y holds a text value with affinity NONE
185 # * t2.b holds an integer value with affinity NUMERIC
186 #
187 # Because t2.b has a numeric affinity, type conversion should occur
188 # and the two fields should be equal.
189 #
190 do_test whereB-4.1 {
191 db eval {
192 DROP TABLE t1;
193 DROP TABLE t2;
194  
195 CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE
196 INSERT INTO t1 VALUES(1,'99');
197  
198 CREATE TABLE t2(a, b NUMERIC); -- affinity of t2.b is NUMERIC
199 CREATE INDEX t2b ON t2(b);
200 INSERT INTO t2 VALUES(2,99);
201  
202 SELECT x, a, y=b FROM t1, t2;
203 }
204 } {1 2 1}
205 do_test whereB-4.2 {
206 db eval {
207 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
208 }
209 } {1 2 1}
210 do_test whereB-4.3 {
211 db eval {
212 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
213 }
214 } {1 2 1}
215 do_test whereB-4.4 {
216 # In this case the unary "+" operator removes the column affinity so
217 # the columns compare false
218 db eval {
219 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
220 }
221 } {}
222 do_test whereB-4.100 {
223 db eval {
224 DROP INDEX t2b;
225 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
226 }
227 } {1 2 1}
228 do_test whereB-4.101 {
229 db eval {
230 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
231 }
232 } {1 2 1}
233 do_test whereB-4.102 {
234 # In this case the unary "+" operator removes the column affinity so
235 # the columns compare false
236 db eval {
237 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
238 }
239 } {}
240  
241  
242  
243 # For this set of tests:
244 #
245 # * t1.y holds a text value with affinity NONE
246 # * t2.b holds an integer value with affinity INTEGER
247 #
248 # Because t2.b has a numeric affinity, type conversion should occur
249 # and the two fields should be equal.
250 #
251 do_test whereB-5.1 {
252 db eval {
253 DROP TABLE t1;
254 DROP TABLE t2;
255  
256 CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE
257 INSERT INTO t1 VALUES(1,'99');
258  
259 CREATE TABLE t2(a, b INT); -- affinity of t2.b is INTEGER
260 CREATE INDEX t2b ON t2(b);
261 INSERT INTO t2 VALUES(2,99);
262  
263 SELECT x, a, y=b FROM t1, t2;
264 }
265 } {1 2 1}
266 do_test whereB-5.2 {
267 db eval {
268 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
269 }
270 } {1 2 1}
271 do_test whereB-5.3 {
272 db eval {
273 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
274 }
275 } {1 2 1}
276 do_test whereB-5.4 {
277 # In this case the unary "+" operator removes the column affinity so
278 # the columns compare false
279 db eval {
280 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
281 }
282 } {}
283 do_test whereB-5.100 {
284 db eval {
285 DROP INDEX t2b;
286 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
287 }
288 } {1 2 1}
289 do_test whereB-5.101 {
290 db eval {
291 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
292 }
293 } {1 2 1}
294 do_test whereB-5.102 {
295 # In this case the unary "+" operator removes the column affinity so
296 # the columns compare false
297 db eval {
298 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
299 }
300 } {}
301  
302  
303 # For this set of tests:
304 #
305 # * t1.y holds a text value with affinity NONE
306 # * t2.b holds an integer value with affinity REAL
307 #
308 # Because t2.b has a numeric affinity, type conversion should occur
309 # and the two fields should be equal.
310 #
311 do_test whereB-6.1 {
312 db eval {
313 DROP TABLE t1;
314 DROP TABLE t2;
315  
316 CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE
317 INSERT INTO t1 VALUES(1,'99');
318  
319 CREATE TABLE t2(a, b REAL); -- affinity of t2.b is REAL
320 CREATE INDEX t2b ON t2(b);
321 INSERT INTO t2 VALUES(2,99.0);
322  
323 SELECT x, a, y=b FROM t1, t2;
324 }
325 } {1 2 1}
326 do_test whereB-6.2 {
327 db eval {
328 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
329 }
330 } {1 2 1}
331 do_test whereB-6.3 {
332 db eval {
333 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
334 }
335 } {1 2 1}
336 do_test whereB-6.4 {
337 # In this case the unary "+" operator removes the column affinity so
338 # the columns compare false
339 db eval {
340 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
341 }
342 } {}
343 do_test whereB-6.100 {
344 db eval {
345 DROP INDEX t2b;
346 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
347 }
348 } {1 2 1}
349 do_test whereB-6.101 {
350 db eval {
351 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
352 }
353 } {1 2 1}
354 do_test whereB-6.102 {
355 # In this case the unary "+" operator removes the column affinity so
356 # the columns compare false
357 db eval {
358 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
359 }
360 } {}
361  
362  
363 # For this set of tests:
364 #
365 # * t1.y holds an integer value with affinity NUMERIC
366 # * t2.b holds a text value with affinity NONE
367 #
368 # Because t1.y has a numeric affinity, type conversion should occur
369 # and the two fields should be equal.
370 #
371 do_test whereB-7.1 {
372 db eval {
373 DROP TABLE t1;
374 DROP TABLE t2;
375  
376 CREATE TABLE t1(x, y NUMERIC); -- affinity of t1.y is NUMERIC
377 INSERT INTO t1 VALUES(1,99);
378  
379 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
380 CREATE INDEX t2b ON t2(b);
381 INSERT INTO t2 VALUES(2,'99');
382  
383 SELECT x, a, y=b FROM t1, t2;
384 }
385 } {1 2 1}
386 do_test whereB-7.2 {
387 db eval {
388 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
389 }
390 } {1 2 1}
391 do_test whereB-7.3 {
392 db eval {
393 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
394 }
395 } {1 2 1}
396 do_test whereB-7.4 {
397 # In this case the unary "+" operator removes the column affinity so
398 # the columns compare false
399 db eval {
400 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
401 }
402 } {}
403 do_test whereB-7.100 {
404 db eval {
405 DROP INDEX t2b;
406 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
407 }
408 } {1 2 1}
409 do_test whereB-7.101 {
410 db eval {
411 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
412 }
413 } {1 2 1}
414 do_test whereB-7.102 {
415 # In this case the unary "+" operator removes the column affinity so
416 # the columns compare false
417 db eval {
418 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
419 }
420 } {}
421  
422 # For this set of tests:
423 #
424 # * t1.y holds an integer value with affinity INTEGER
425 # * t2.b holds a text value with affinity NONE
426 #
427 # Because t1.y has a numeric affinity, type conversion should occur
428 # and the two fields should be equal.
429 #
430 do_test whereB-8.1 {
431 db eval {
432 DROP TABLE t1;
433 DROP TABLE t2;
434  
435 CREATE TABLE t1(x, y INT); -- affinity of t1.y is INTEGER
436 INSERT INTO t1 VALUES(1,99);
437  
438 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
439 CREATE INDEX t2b ON t2(b);
440 INSERT INTO t2 VALUES(2,'99');
441  
442 SELECT x, a, y=b FROM t1, t2;
443 }
444 } {1 2 1}
445 do_test whereB-8.2 {
446 db eval {
447 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
448 }
449 } {1 2 1}
450 do_test whereB-8.3 {
451 db eval {
452 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
453 }
454 } {1 2 1}
455 do_test whereB-8.4 {
456 # In this case the unary "+" operator removes the column affinity so
457 # the columns compare false
458 db eval {
459 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
460 }
461 } {}
462 do_test whereB-8.100 {
463 db eval {
464 DROP INDEX t2b;
465 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
466 }
467 } {1 2 1}
468 do_test whereB-8.101 {
469 db eval {
470 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
471 }
472 } {1 2 1}
473 do_test whereB-8.102 {
474 # In this case the unary "+" operator removes the column affinity so
475 # the columns compare false
476 db eval {
477 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
478 }
479 } {}
480  
481 # For this set of tests:
482 #
483 # * t1.y holds an integer value with affinity REAL
484 # * t2.b holds a text value with affinity NONE
485 #
486 # Because t1.y has a numeric affinity, type conversion should occur
487 # and the two fields should be equal.
488 #
489 do_test whereB-9.1 {
490 db eval {
491 DROP TABLE t1;
492 DROP TABLE t2;
493  
494 CREATE TABLE t1(x, y REAL); -- affinity of t1.y is REAL
495 INSERT INTO t1 VALUES(1,99.0);
496  
497 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
498 CREATE INDEX t2b ON t2(b);
499 INSERT INTO t2 VALUES(2,'99');
500  
501 SELECT x, a, y=b FROM t1, t2;
502 }
503 } {1 2 1}
504 do_test whereB-9.2 {
505 db eval {
506 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
507 }
508 } {1 2 1}
509 do_test whereB-9.3 {
510 db eval {
511 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
512 }
513 } {1 2 1}
514 do_test whereB-9.4 {
515 # In this case the unary "+" operator removes the column affinity so
516 # the columns compare false
517 db eval {
518 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
519 }
520 } {}
521 do_test whereB-9.100 {
522 db eval {
523 DROP INDEX t2b;
524 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
525 }
526 } {1 2 1}
527 do_test whereB-9.101 {
528 db eval {
529 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
530 }
531 } {1 2 1}
532 do_test whereB-9.102 {
533 # In this case the unary "+" operator removes the column affinity so
534 # the columns compare false
535 db eval {
536 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
537 }
538 } {}
539  
540  
541  
542  
543 finish_test