wasCSharpSQLite – Blame information for rev 7
?pathlinks?
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 |