1: # 2002 May 24
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.
12: #
13: # This file implements tests for joins, including outer joins involving
14: # virtual tables. The test cases in this file are copied from the file
15: # join.test, and some of the comments still reflect that.
16: #
17: # $Id: vtab6.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
18:
19: set testdir [file dirname $argv0]
20: source $testdir/tester.tcl
21:
22: ifcapable !vtab {
23: finish_test
24: return
25: }
26:
27: register_echo_module [sqlite3_connection_pointer db]
28:
29: execsql {
30: CREATE TABLE real_t1(a,b,c);
31: CREATE TABLE real_t2(b,c,d);
32: CREATE TABLE real_t3(c,d,e);
33: CREATE TABLE real_t4(d,e,f);
34: CREATE TABLE real_t5(a INTEGER PRIMARY KEY);
35: CREATE TABLE real_t6(a INTEGER);
36: CREATE TABLE real_t7 (x, y);
37: CREATE TABLE real_t8 (a integer primary key, b);
38: CREATE TABLE real_t9(a INTEGER PRIMARY KEY, b);
39: CREATE TABLE real_t10(x INTEGER PRIMARY KEY, y);
40: CREATE TABLE real_t11(p INTEGER PRIMARY KEY, q);
41: CREATE TABLE real_t12(a,b);
42: CREATE TABLE real_t13(b,c);
43: CREATE TABLE real_t21(a,b,c);
44: CREATE TABLE real_t22(p,q);
45: }
46: foreach t [list t1 t2 t3 t4 t5 t6 t7 t8 t9 t10 t11 t12 t13 t21 t22] {
47: execsql "CREATE VIRTUAL TABLE $t USING echo(real_$t)"
48: }
49:
50: do_test vtab6-1.1 {
51: execsql {
52: INSERT INTO t1 VALUES(1,2,3);
53: INSERT INTO t1 VALUES(2,3,4);
54: INSERT INTO t1 VALUES(3,4,5);
55: SELECT * FROM t1;
56: }
57: } {1 2 3 2 3 4 3 4 5}
58: do_test vtab6-1.2 {
59: execsql {
60: INSERT INTO t2 VALUES(1,2,3);
61: INSERT INTO t2 VALUES(2,3,4);
62: INSERT INTO t2 VALUES(3,4,5);
63: SELECT * FROM t2;
64: }
65: } {1 2 3 2 3 4 3 4 5}
66:
67: do_test vtab6-1.3 {
68: execsql2 {
69: SELECT * FROM t1 NATURAL JOIN t2;
70: }
71: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
72: do_test vtab6-1.3.1 {
73: execsql2 {
74: SELECT * FROM t2 NATURAL JOIN t1;
75: }
76: } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
77: do_test vtab6-1.3.2 {
78: execsql2 {
79: SELECT * FROM t2 AS x NATURAL JOIN t1;
80: }
81: } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
82: do_test vtab6-1.3.3 {
83: execsql2 {
84: SELECT * FROM t2 NATURAL JOIN t1 AS y;
85: }
86: } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
87: do_test vtab6-1.3.4 {
88: execsql {
89: SELECT b FROM t1 NATURAL JOIN t2;
90: }
91: } {2 3}
92: do_test vtab6-1.4.1 {
93: execsql2 {
94: SELECT * FROM t1 INNER JOIN t2 USING(b,c);
95: }
96: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
97: do_test vtab6-1.4.2 {
98: execsql2 {
99: SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c);
100: }
101: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
102: do_test vtab6-1.4.3 {
103: execsql2 {
104: SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c);
105: }
106: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
107: do_test vtab6-1.4.4 {
108: execsql2 {
109: SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c);
110: }
111: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
112: do_test vtab6-1.4.5 {
113: execsql {
114: SELECT b FROM t1 JOIN t2 USING(b);
115: }
116: } {2 3}
117: do_test vtab6-1.5 {
118: execsql2 {
119: SELECT * FROM t1 INNER JOIN t2 USING(b);
120: }
121: } {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5}
122: do_test vtab6-1.6 {
123: execsql2 {
124: SELECT * FROM t1 INNER JOIN t2 USING(c);
125: }
126: } {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5}
127: do_test vtab6-1.7 {
128: execsql2 {
129: SELECT * FROM t1 INNER JOIN t2 USING(c,b);
130: }
131: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
132:
133: do_test vtab6-1.8 {
134: execsql {
135: SELECT * FROM t1 NATURAL CROSS JOIN t2;
136: }
137: } {1 2 3 4 2 3 4 5}
138: do_test vtab6-1.9 {
139: execsql {
140: SELECT * FROM t1 CROSS JOIN t2 USING(b,c);
141: }
142: } {1 2 3 4 2 3 4 5}
143: do_test vtab6-1.10 {
144: execsql {
145: SELECT * FROM t1 NATURAL INNER JOIN t2;
146: }
147: } {1 2 3 4 2 3 4 5}
148: do_test vtab6-1.11 {
149: execsql {
150: SELECT * FROM t1 INNER JOIN t2 USING(b,c);
151: }
152: } {1 2 3 4 2 3 4 5}
153: do_test vtab6-1.12 {
154: execsql {
155: SELECT * FROM t1 natural inner join t2;
156: }
157: } {1 2 3 4 2 3 4 5}
158:
159: ifcapable subquery {
160: do_test vtab6-1.13 {
161: execsql2 {
162: SELECT * FROM t1 NATURAL JOIN
163: (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3
164: }
165: } {a 1 b 2 c 3 d 4 e 5}
166: do_test vtab6-1.14 {
167: execsql2 {
168: SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx'
169: NATURAL JOIN t1
170: }
171: } {c 3 d 4 e 5 a 1 b 2}
172: }
173:
174: do_test vtab6-1.15 {
175: execsql {
176: INSERT INTO t3 VALUES(2,3,4);
177: INSERT INTO t3 VALUES(3,4,5);
178: INSERT INTO t3 VALUES(4,5,6);
179: SELECT * FROM t3;
180: }
181: } {2 3 4 3 4 5 4 5 6}
182: do_test vtab6-1.16 {
183: execsql {
184: SELECT * FROM t1 natural join t2 natural join t3;
185: }
186: } {1 2 3 4 5 2 3 4 5 6}
187: do_test vtab6-1.17 {
188: execsql2 {
189: SELECT * FROM t1 natural join t2 natural join t3;
190: }
191: } {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6}
192: do_test vtab6-1.18 {
193: execsql {
194: INSERT INTO t4 VALUES(2,3,4);
195: INSERT INTO t4 VALUES(3,4,5);
196: INSERT INTO t4 VALUES(4,5,6);
197: SELECT * FROM t4;
198: }
199: } {2 3 4 3 4 5 4 5 6}
200: do_test vtab6-1.19.1 {
201: execsql {
202: SELECT * FROM t1 natural join t2 natural join t4;
203: }
204: } {1 2 3 4 5 6}
205: do_test vtab6-1.19.2 {
206: execsql2 {
207: SELECT * FROM t1 natural join t2 natural join t4;
208: }
209: } {a 1 b 2 c 3 d 4 e 5 f 6}
210: do_test vtab6-1.20 {
211: execsql {
212: SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1
213: }
214: } {1 2 3 4 5}
215:
216: do_test vtab6-2.1 {
217: execsql {
218: SELECT * FROM t1 NATURAL LEFT JOIN t2;
219: }
220: } {1 2 3 4 2 3 4 5 3 4 5 {}}
221: do_test vtab6-2.2 {
222: execsql {
223: SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
224: }
225: } {1 2 3 {} 2 3 4 1 3 4 5 2}
226: do_test vtab6-2.3 {
227: catchsql {
228: SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
229: }
230: } {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
231: do_test vtab6-2.4 {
232: execsql {
233: SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
234: }
235: } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
236: do_test vtab6-2.5 {
237: execsql {
238: SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
239: }
240: } {2 3 4 {} {} {} 3 4 5 1 2 3}
241: do_test vtab6-2.6 {
242: execsql {
243: SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1
244: }
245: } {1 2 3 {} {} {} 2 3 4 {} {} {}}
246:
247: do_test vtab6-3.1 {
248: catchsql {
249: SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;
250: }
251: } {1 {a NATURAL join may not have an ON or USING clause}}
252: do_test vtab6-3.2 {
253: catchsql {
254: SELECT * FROM t1 NATURAL JOIN t2 USING(b);
255: }
256: } {1 {a NATURAL join may not have an ON or USING clause}}
257: do_test vtab6-3.3 {
258: catchsql {
259: SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b);
260: }
261: } {1 {cannot have both ON and USING clauses in the same join}}
262: do_test vtab6-3.4 {
263: catchsql {
264: SELECT * FROM t1 JOIN t2 USING(a);
265: }
266: } {1 {cannot join using column a - column not present in both tables}}
267: do_test vtab6-3.5 {
268: catchsql { SELECT * FROM t1 USING(a) }
269: } {1 {a JOIN clause is required before USING}}
270: do_test vtab6-3.6 {
271: catchsql {
272: SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
273: }
274: } {1 {no such column: t3.a}}
275: do_test vtab6-3.7 {
276: catchsql {
277: SELECT * FROM t1 INNER OUTER JOIN t2;
278: }
279: } {1 {unknown or unsupported join type: INNER OUTER}}
280: do_test vtab6-3.7 {
281: catchsql {
282: SELECT * FROM t1 LEFT BOGUS JOIN t2;
283: }
284: } {1 {unknown or unsupported join type: LEFT BOGUS}}
285:
286: do_test vtab6-4.1 {
287: execsql {
288: BEGIN;
289: INSERT INTO t6 VALUES(NULL);
290: INSERT INTO t6 VALUES(NULL);
291: INSERT INTO t6 SELECT * FROM t6;
292: INSERT INTO t6 SELECT * FROM t6;
293: INSERT INTO t6 SELECT * FROM t6;
294: INSERT INTO t6 SELECT * FROM t6;
295: INSERT INTO t6 SELECT * FROM t6;
296: INSERT INTO t6 SELECT * FROM t6;
297: COMMIT;
298: }
299: execsql {
300: SELECT * FROM t6 NATURAL JOIN t5;
301: }
302: } {}
303: do_test vtab6-4.2 {
304: execsql {
305: SELECT * FROM t6, t5 WHERE t6.a<t5.a;
306: }
307: } {}
308: do_test vtab6-4.3 {
309: execsql {
310: SELECT * FROM t6, t5 WHERE t6.a>t5.a;
311: }
312: } {}
313: do_test vtab6-4.4 {
314: execsql {
315: UPDATE t6 SET a='xyz';
316: SELECT * FROM t6 NATURAL JOIN t5;
317: }
318: } {}
319: do_test vtab6-4.6 {
320: execsql {
321: SELECT * FROM t6, t5 WHERE t6.a<t5.a;
322: }
323: } {}
324: do_test vtab6-4.7 {
325: execsql {
326: SELECT * FROM t6, t5 WHERE t6.a>t5.a;
327: }
328: } {}
329: do_test vtab6-4.8 {
330: execsql {
331: UPDATE t6 SET a=1;
332: SELECT * FROM t6 NATURAL JOIN t5;
333: }
334: } {}
335: do_test vtab6-4.9 {
336: execsql {
337: SELECT * FROM t6, t5 WHERE t6.a<t5.a;
338: }
339: } {}
340: do_test vtab6-4.10 {
341: execsql {
342: SELECT * FROM t6, t5 WHERE t6.a>t5.a;
343: }
344: } {}
345:
346: # A test for ticket #247.
347: #
348: do_test vtab6-7.1 {
349: execsql {
350: INSERT INTO t7 VALUES ("pa1", 1);
351: INSERT INTO t7 VALUES ("pa2", NULL);
352: INSERT INTO t7 VALUES ("pa3", NULL);
353: INSERT INTO t7 VALUES ("pa4", 2);
354: INSERT INTO t7 VALUES ("pa30", 131);
355: INSERT INTO t7 VALUES ("pa31", 130);
356: INSERT INTO t7 VALUES ("pa28", NULL);
357:
358: INSERT INTO t8 VALUES (1, "pa1");
359: INSERT INTO t8 VALUES (2, "pa4");
360: INSERT INTO t8 VALUES (3, NULL);
361: INSERT INTO t8 VALUES (4, NULL);
362: INSERT INTO t8 VALUES (130, "pa31");
363: INSERT INTO t8 VALUES (131, "pa30");
364:
365: SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
366: }
367: } {1 999 999 2 131 130 999}
368:
369: # Make sure a left join where the right table is really a view that
370: # is itself a join works right. Ticket #306.
371: #
372: ifcapable view {
373: do_test vtab6-8.1 {
374: execsql {
375: BEGIN;
376: INSERT INTO t9 VALUES(1,11);
377: INSERT INTO t9 VALUES(2,22);
378: INSERT INTO t10 VALUES(1,2);
379: INSERT INTO t10 VALUES(3,3);
380: INSERT INTO t11 VALUES(2,111);
381: INSERT INTO t11 VALUES(3,333);
382: CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p;
383: COMMIT;
384: SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );
385: }
386: } {1 11 1 111 2 22 {} {}}
387: ifcapable subquery {
388: do_test vtab6-8.2 {
389: execsql {
390: SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)
391: ON( a=x);
392: }
393: } {1 11 1 111 2 22 {} {}}
394: }
395: do_test vtab6-8.3 {
396: execsql {
397: SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );
398: }
399: } {1 111 1 11 3 333 {} {}}
400: } ;# ifcapable view
401:
402: # Ticket #350 describes a scenario where LEFT OUTER JOIN does not
403: # function correctly if the right table in the join is really
404: # subquery.
405: #
406: # To test the problem, we generate the same LEFT OUTER JOIN in two
407: # separate selects but with on using a subquery and the other calling
408: # the table directly. Then connect the two SELECTs using an EXCEPT.
409: # Both queries should generate the same results so the answer should
410: # be an empty set.
411: #
412: ifcapable compound {
413: do_test vtab6-9.1 {
414: execsql {
415: BEGIN;
416: INSERT INTO t12 VALUES(1,11);
417: INSERT INTO t12 VALUES(2,22);
418: INSERT INTO t13 VALUES(22,222);
419: COMMIT;
420: }
421: } {}
422:
423: ifcapable subquery {
424: do_test vtab6-9.1.1 {
425: execsql {
426: SELECT * FROM t12 NATURAL LEFT JOIN t13
427: EXCEPT
428: SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);
429: }
430: } {}
431: }
432: ifcapable view {
433: do_test vtab6-9.2 {
434: execsql {
435: CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;
436: SELECT * FROM t12 NATURAL LEFT JOIN t13
437: EXCEPT
438: SELECT * FROM t12 NATURAL LEFT JOIN v13;
439: }
440: } {}
441: } ;# ifcapable view
442: } ;# ifcapable compound
443:
444: ifcapable subquery {
445: do_test vtab6-10.1 {
446: execsql {
447: CREATE INDEX i22 ON real_t22(q);
448: SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=
449: (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);
450: }
451: } {}
452: } ;# ifcapable subquery
453:
454: do_test vtab6-11.1.0 {
455: execsql {
456: CREATE TABLE ab_r(a, b);
457: CREATE TABLE bc_r(b, c);
458:
459: CREATE VIRTUAL TABLE ab USING echo(ab_r);
460: CREATE VIRTUAL TABLE bc USING echo(bc_r);
461:
462: INSERT INTO ab VALUES(1, 2);
463: INSERT INTO bc VALUES(2, 3);
464: }
465: } {}
466:
467: do_test vtab6-11.1.1 {
468: execsql {
469: SELECT a, b, c FROM ab NATURAL JOIN bc;
470: }
471: } {1 2 3}
472: do_test vtab6-11.1.2 {
473: execsql {
474: SELECT a, b, c FROM bc NATURAL JOIN ab;
475: }
476: } {1 2 3}
477:
478: set ::echo_module_cost 1.0
479:
480: do_test vtab6-11.1.3 {
481: execsql {
482: SELECT a, b, c FROM ab NATURAL JOIN bc;
483: }
484: } {1 2 3}
485: do_test vtab6-11.1.4 {
486: execsql {
487: SELECT a, b, c FROM bc NATURAL JOIN ab;
488: }
489: } {1 2 3}
490:
491:
492: do_test vtab6-11.2.0 {
493: execsql {
494: CREATE INDEX ab_i ON ab_r(b);
495: CREATE INDEX bc_i ON bc_r(b);
496: }
497: } {}
498:
499: unset ::echo_module_cost
500:
501: do_test vtab6-11.2.1 {
502: execsql {
503: SELECT a, b, c FROM ab NATURAL JOIN bc;
504: }
505: } {1 2 3}
506: do_test vtab6-11.2.2 {
507: execsql {
508: SELECT a, b, c FROM bc NATURAL JOIN ab;
509: }
510: } {1 2 3}
511:
512: set ::echo_module_cost 1.0
513:
514: do_test vtab6-11.2.3 {
515: execsql {
516: SELECT a, b, c FROM ab NATURAL JOIN bc;
517: }
518: } {1 2 3}
519: do_test vtab6-11.2.4 {
520: execsql {
521: SELECT a, b, c FROM bc NATURAL JOIN ab;
522: }
523: } {1 2 3}
524:
525: unset ::echo_module_cost
526: db close
527: sqlite3 db test.db
528: register_echo_module [sqlite3_connection_pointer db]
529:
530: do_test vtab6-11.3.1 {
531: execsql {
532: SELECT a, b, c FROM ab NATURAL JOIN bc;
533: }
534: } {1 2 3}
535:
536: do_test vtab6-11.3.2 {
537: execsql {
538: SELECT a, b, c FROM bc NATURAL JOIN ab;
539: }
540: } {1 2 3}
541:
542: set ::echo_module_cost 1.0
543:
544: do_test vtab6-11.3.3 {
545: execsql {
546: SELECT a, b, c FROM ab NATURAL JOIN bc;
547: }
548: } {1 2 3}
549: do_test vtab6-11.3.4 {
550: execsql {
551: SELECT a, b, c FROM bc NATURAL JOIN ab;
552: }
553: } {1 2 3}
554:
555: unset ::echo_module_cost
556:
557: set ::echo_module_ignore_usable 1
558: db cache flush
559:
560: do_test vtab6-11.4.1 {
561: catchsql {
562: SELECT a, b, c FROM ab NATURAL JOIN bc;
563: }
564: } {1 {table bc: xBestIndex returned an invalid plan}}
565: do_test vtab6-11.4.2 {
566: catchsql {
567: SELECT a, b, c FROM bc NATURAL JOIN ab;
568: }
569: } {1 {table ab: xBestIndex returned an invalid plan}}
570:
571: unset ::echo_module_ignore_usable
572:
573: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to vtab6.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