blob: e4438547d778d58285a05f9617ffa892abe102fd [file] [log] [blame]
drh9fa72072009-08-13 17:14:59 +00001# 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
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
drh320b3a72009-08-13 18:14:32 +000019# 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#
drh9fa72072009-08-13 17:14:59 +000027do_test whereB-1.1 {
28 db eval {
29 CREATE TABLE t1(x,y); -- affinity of t1.y is NONE
drh320b3a72009-08-13 18:14:32 +000030 INSERT INTO t1 VALUES(1,99);
drh9fa72072009-08-13 17:14:59 +000031
32 CREATE TABLE t2(a, b TEXT); -- affinity of t2.b is TEXT
33 CREATE INDEX t2b ON t2(b);
drh320b3a72009-08-13 18:14:32 +000034 INSERT INTO t2 VALUES(2,99);
drh9fa72072009-08-13 17:14:59 +000035
36 SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
37 }
drh320b3a72009-08-13 18:14:32 +000038} {1 2 0}
drh9fa72072009-08-13 17:14:59 +000039do_test whereB-1.2 {
40 db eval {
drh320b3a72009-08-13 18:14:32 +000041 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
drh9fa72072009-08-13 17:14:59 +000042 }
43} {}
44do_test whereB-1.3 {
45 db eval {
drh320b3a72009-08-13 18:14:32 +000046 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
drh9fa72072009-08-13 17:14:59 +000047 }
48} {}
49do_test whereB-1.4 {
50 db eval {
drh320b3a72009-08-13 18:14:32 +000051 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
drh9fa72072009-08-13 17:14:59 +000052 }
53} {}
54do_test whereB-1.100 {
55 db eval {
56 DROP INDEX t2b;
drh320b3a72009-08-13 18:14:32 +000057 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
drh9fa72072009-08-13 17:14:59 +000058 }
59} {}
60do_test whereB-1.101 {
61 db eval {
drh320b3a72009-08-13 18:14:32 +000062 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
drh9fa72072009-08-13 17:14:59 +000063 }
64} {}
65do_test whereB-1.102 {
66 db eval {
drh320b3a72009-08-13 18:14:32 +000067 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
drh9fa72072009-08-13 17:14:59 +000068 }
69} {}
70
drh320b3a72009-08-13 18:14:32 +000071# 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#
drh9fa72072009-08-13 17:14:59 +000079do_test whereB-2.1 {
80 db eval {
81 DROP TABLE t1;
82 DROP TABLE t2;
83
drh320b3a72009-08-13 18:14:32 +000084 CREATE TABLE t1(x, y TEXT); -- affinity of t1.y is TEXT
85 INSERT INTO t1 VALUES(1,99);
drh9fa72072009-08-13 17:14:59 +000086
drh320b3a72009-08-13 18:14:32 +000087 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
drh9fa72072009-08-13 17:14:59 +000088 CREATE INDEX t2b ON t2(b);
drh320b3a72009-08-13 18:14:32 +000089 INSERT INTO t2 VALUES(2,99);
drh9fa72072009-08-13 17:14:59 +000090
91 SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
92 }
drh320b3a72009-08-13 18:14:32 +000093} {1 2 0}
drh9fa72072009-08-13 17:14:59 +000094do_test whereB-2.2 {
95 db eval {
drh320b3a72009-08-13 18:14:32 +000096 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
drh9fa72072009-08-13 17:14:59 +000097 }
drh320b3a72009-08-13 18:14:32 +000098} {}
drh9fa72072009-08-13 17:14:59 +000099do_test whereB-2.3 {
100 db eval {
drh320b3a72009-08-13 18:14:32 +0000101 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
drh9fa72072009-08-13 17:14:59 +0000102 }
drh320b3a72009-08-13 18:14:32 +0000103} {}
drh9fa72072009-08-13 17:14:59 +0000104do_test whereB-2.4 {
105 db eval {
drh320b3a72009-08-13 18:14:32 +0000106 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
drh9fa72072009-08-13 17:14:59 +0000107 }
drh320b3a72009-08-13 18:14:32 +0000108} {}
drh9fa72072009-08-13 17:14:59 +0000109do_test whereB-2.100 {
110 db eval {
111 DROP INDEX t2b;
drh320b3a72009-08-13 18:14:32 +0000112 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
drh9fa72072009-08-13 17:14:59 +0000113 }
drh320b3a72009-08-13 18:14:32 +0000114} {}
drh9fa72072009-08-13 17:14:59 +0000115do_test whereB-2.101 {
116 db eval {
drh320b3a72009-08-13 18:14:32 +0000117 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
drh9fa72072009-08-13 17:14:59 +0000118 }
drh320b3a72009-08-13 18:14:32 +0000119} {}
drh9fa72072009-08-13 17:14:59 +0000120do_test whereB-2.102 {
121 db eval {
drh320b3a72009-08-13 18:14:32 +0000122 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
drh9fa72072009-08-13 17:14:59 +0000123 }
drh320b3a72009-08-13 18:14:32 +0000124} {}
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#
134do_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
drhc4fa5312009-08-13 19:54:26 +0000140 INSERT INTO t1 VALUES(1,99);
drh320b3a72009-08-13 18:14:32 +0000141
142 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE
143 CREATE INDEX t2b ON t2(b);
drhc4fa5312009-08-13 19:54:26 +0000144 INSERT INTO t2 VALUES(2,'99');
drh320b3a72009-08-13 18:14:32 +0000145
146 SELECT x, a, y=b FROM t1, t2;
147 }
drhc4fa5312009-08-13 19:54:26 +0000148} {1 2 0}
drh320b3a72009-08-13 18:14:32 +0000149do_test whereB-3.2 {
150 db eval {
151 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
152 }
153} {}
154do_test whereB-3.3 {
155 db eval {
156 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
157 }
158} {}
159do_test whereB-3.4 {
160 db eval {
161 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
162 }
163} {}
164do_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} {}
170do_test whereB-3.101 {
171 db eval {
172 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
173 }
174} {}
175do_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#
190do_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}
205do_test whereB-4.2 {
206 db eval {
207 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
208 }
209} {1 2 1}
210do_test whereB-4.3 {
211 db eval {
212 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
213 }
214} {1 2 1}
215do_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} {}
222do_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}
228do_test whereB-4.101 {
229 db eval {
230 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
231 }
232} {1 2 1}
233do_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#
251do_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}
266do_test whereB-5.2 {
267 db eval {
268 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
269 }
270} {1 2 1}
271do_test whereB-5.3 {
272 db eval {
273 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
274 }
275} {1 2 1}
276do_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} {}
283do_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}
289do_test whereB-5.101 {
290 db eval {
291 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
292 }
293} {1 2 1}
294do_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#
311do_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}
326do_test whereB-6.2 {
327 db eval {
328 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
329 }
330} {1 2 1}
331do_test whereB-6.3 {
332 db eval {
333 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
334 }
335} {1 2 1}
336do_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} {}
343do_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}
349do_test whereB-6.101 {
350 db eval {
351 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
352 }
353} {1 2 1}
354do_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#
371do_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}
386do_test whereB-7.2 {
387 db eval {
388 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
389 }
390} {1 2 1}
391do_test whereB-7.3 {
392 db eval {
393 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
394 }
395} {1 2 1}
396do_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} {}
403do_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}
409do_test whereB-7.101 {
410 db eval {
411 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
412 }
413} {1 2 1}
414do_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#
430do_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}
445do_test whereB-8.2 {
446 db eval {
447 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
448 }
449} {1 2 1}
450do_test whereB-8.3 {
451 db eval {
452 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
453 }
454} {1 2 1}
455do_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} {}
462do_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}
468do_test whereB-8.101 {
469 db eval {
470 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
471 }
472} {1 2 1}
473do_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#
489do_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}
504do_test whereB-9.2 {
505 db eval {
506 SELECT x, a, y=b FROM t1, t2 WHERE y=b;
507 }
508} {1 2 1}
509do_test whereB-9.3 {
510 db eval {
511 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
512 }
513} {1 2 1}
514do_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} {}
521do_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}
527do_test whereB-9.101 {
528 db eval {
529 SELECT x, a, y=b FROM t1, t2 WHERE b=y;
530 }
531} {1 2 1}
532do_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
drh9fa72072009-08-13 17:14:59 +0000540
541
542
543finish_test