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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to whereB.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