wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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.5 2009/07/01 16:12:08 danielk1977 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