000001 # 2002 May 24
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library.
000012 #
000013 # This file implements tests for joins, including outer joins involving
000014 # virtual tables. The test cases in this file are copied from the file
000015 # join.test, and some of the comments still reflect that.
000016 #
000017 # $Id: vtab6.test,v 1.5 2009/07/01 16:12:08 danielk1977 Exp $
000018
000019 set testdir [file dirname $argv0]
000020 source $testdir/tester.tcl
000021
000022 ifcapable !vtab {
000023 finish_test
000024 return
000025 }
000026
000027 register_echo_module [sqlite3_connection_pointer db]
000028
000029 execsql {
000030 CREATE TABLE real_t1(a,b,c);
000031 CREATE TABLE real_t2(b,c,d);
000032 CREATE TABLE real_t3(c,d,e);
000033 CREATE TABLE real_t4(d,e,f);
000034 CREATE TABLE real_t5(a INTEGER PRIMARY KEY);
000035 CREATE TABLE real_t6(a INTEGER);
000036 CREATE TABLE real_t7 (x, y);
000037 CREATE TABLE real_t8 (a integer primary key, b);
000038 CREATE TABLE real_t9(a INTEGER PRIMARY KEY, b);
000039 CREATE TABLE real_t10(x INTEGER PRIMARY KEY, y);
000040 CREATE TABLE real_t11(p INTEGER PRIMARY KEY, q);
000041 CREATE TABLE real_t12(a,b);
000042 CREATE TABLE real_t13(b,c);
000043 CREATE TABLE real_t21(a,b,c);
000044 CREATE TABLE real_t22(p,q);
000045 }
000046 foreach t [list t1 t2 t3 t4 t5 t6 t7 t8 t9 t10 t11 t12 t13 t21 t22] {
000047 execsql "CREATE VIRTUAL TABLE $t USING echo(real_$t)"
000048 }
000049
000050 do_test vtab6-1.1 {
000051 execsql {
000052 INSERT INTO t1 VALUES(1,2,3);
000053 INSERT INTO t1 VALUES(2,3,4);
000054 INSERT INTO t1 VALUES(3,4,5);
000055 SELECT * FROM t1;
000056 }
000057 } {1 2 3 2 3 4 3 4 5}
000058 do_test vtab6-1.2 {
000059 execsql {
000060 INSERT INTO t2 VALUES(1,2,3);
000061 INSERT INTO t2 VALUES(2,3,4);
000062 INSERT INTO t2 VALUES(3,4,5);
000063 SELECT * FROM t2;
000064 }
000065 } {1 2 3 2 3 4 3 4 5}
000066
000067 do_test vtab6-1.3 {
000068 execsql2 {
000069 SELECT * FROM t1 NATURAL JOIN t2;
000070 }
000071 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
000072 do_test vtab6-1.3.1 {
000073 execsql2 {
000074 SELECT * FROM t2 NATURAL JOIN t1;
000075 }
000076 } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
000077 do_test vtab6-1.3.2 {
000078 execsql2 {
000079 SELECT * FROM t2 AS x NATURAL JOIN t1;
000080 }
000081 } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
000082 do_test vtab6-1.3.3 {
000083 execsql2 {
000084 SELECT * FROM t2 NATURAL JOIN t1 AS y;
000085 }
000086 } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
000087 do_test vtab6-1.3.4 {
000088 execsql {
000089 SELECT b FROM t1 NATURAL JOIN t2;
000090 }
000091 } {2 3}
000092 do_test vtab6-1.4.1 {
000093 execsql2 {
000094 SELECT * FROM t1 INNER JOIN t2 USING(b,c);
000095 }
000096 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
000097 do_test vtab6-1.4.2 {
000098 execsql2 {
000099 SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c);
000100 }
000101 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
000102 do_test vtab6-1.4.3 {
000103 execsql2 {
000104 SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c);
000105 }
000106 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
000107 do_test vtab6-1.4.4 {
000108 execsql2 {
000109 SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c);
000110 }
000111 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
000112 do_test vtab6-1.4.5 {
000113 execsql {
000114 SELECT b FROM t1 JOIN t2 USING(b);
000115 }
000116 } {2 3}
000117 do_test vtab6-1.5 {
000118 execsql2 {
000119 SELECT * FROM t1 INNER JOIN t2 USING(b);
000120 }
000121 } {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5}
000122 do_test vtab6-1.6 {
000123 execsql2 {
000124 SELECT * FROM t1 INNER JOIN t2 USING(c);
000125 }
000126 } {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5}
000127 do_test vtab6-1.7 {
000128 execsql2 {
000129 SELECT * FROM t1 INNER JOIN t2 USING(c,b);
000130 }
000131 } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
000132
000133 do_test vtab6-1.8 {
000134 execsql {
000135 SELECT * FROM t1 NATURAL CROSS JOIN t2;
000136 }
000137 } {1 2 3 4 2 3 4 5}
000138 do_test vtab6-1.9 {
000139 execsql {
000140 SELECT * FROM t1 CROSS JOIN t2 USING(b,c);
000141 }
000142 } {1 2 3 4 2 3 4 5}
000143 do_test vtab6-1.10 {
000144 execsql {
000145 SELECT * FROM t1 NATURAL INNER JOIN t2;
000146 }
000147 } {1 2 3 4 2 3 4 5}
000148 do_test vtab6-1.11 {
000149 execsql {
000150 SELECT * FROM t1 INNER JOIN t2 USING(b,c);
000151 }
000152 } {1 2 3 4 2 3 4 5}
000153 do_test vtab6-1.12 {
000154 execsql {
000155 SELECT * FROM t1 natural inner join t2;
000156 }
000157 } {1 2 3 4 2 3 4 5}
000158
000159 ifcapable subquery {
000160 do_test vtab6-1.13 {
000161 execsql2 {
000162 SELECT * FROM t1 NATURAL JOIN
000163 (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3
000164 }
000165 } {a 1 b 2 c 3 d 4 e 5}
000166 do_test vtab6-1.14 {
000167 execsql2 {
000168 SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx'
000169 NATURAL JOIN t1
000170 }
000171 } {c 3 d 4 e 5 a 1 b 2}
000172 }
000173
000174 do_test vtab6-1.15 {
000175 execsql {
000176 INSERT INTO t3 VALUES(2,3,4);
000177 INSERT INTO t3 VALUES(3,4,5);
000178 INSERT INTO t3 VALUES(4,5,6);
000179 SELECT * FROM t3;
000180 }
000181 } {2 3 4 3 4 5 4 5 6}
000182 do_test vtab6-1.16 {
000183 execsql {
000184 SELECT * FROM t1 natural join t2 natural join t3;
000185 }
000186 } {1 2 3 4 5 2 3 4 5 6}
000187 do_test vtab6-1.17 {
000188 execsql2 {
000189 SELECT * FROM t1 natural join t2 natural join t3;
000190 }
000191 } {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6}
000192 do_test vtab6-1.18 {
000193 execsql {
000194 INSERT INTO t4 VALUES(2,3,4);
000195 INSERT INTO t4 VALUES(3,4,5);
000196 INSERT INTO t4 VALUES(4,5,6);
000197 SELECT * FROM t4;
000198 }
000199 } {2 3 4 3 4 5 4 5 6}
000200 do_test vtab6-1.19.1 {
000201 execsql {
000202 SELECT * FROM t1 natural join t2 natural join t4;
000203 }
000204 } {1 2 3 4 5 6}
000205 do_test vtab6-1.19.2 {
000206 execsql2 {
000207 SELECT * FROM t1 natural join t2 natural join t4;
000208 }
000209 } {a 1 b 2 c 3 d 4 e 5 f 6}
000210 do_test vtab6-1.20 {
000211 execsql {
000212 SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1
000213 }
000214 } {1 2 3 4 5}
000215
000216 do_test vtab6-2.1 {
000217 execsql {
000218 SELECT * FROM t1 NATURAL LEFT JOIN t2;
000219 }
000220 } {1 2 3 4 2 3 4 5 3 4 5 {}}
000221 do_test vtab6-2.2 {
000222 execsql {
000223 SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
000224 }
000225 } {1 2 3 {} 2 3 4 1 3 4 5 2}
000226 #do_test vtab6-2.3 {
000227 # catchsql {
000228 # SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
000229 # }
000230 #} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
000231 do_test vtab6-2.4 {
000232 execsql {
000233 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
000234 }
000235 } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
000236 do_test vtab6-2.4.1 {
000237 execsql {
000238 SELECT * FROM t1 LEFT JOIN t2 ON t1.a IS t2.d
000239 }
000240 } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
000241 do_test vtab6-2.5 {
000242 execsql {
000243 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
000244 }
000245 } {2 3 4 {} {} {} 3 4 5 1 2 3}
000246 do_test vtab6-2.6 {
000247 execsql {
000248 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1
000249 }
000250 } {1 2 3 {} {} {} 2 3 4 {} {} {}}
000251
000252 do_test vtab6-3.1 {
000253 catchsql {
000254 SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;
000255 }
000256 } {1 {a NATURAL join may not have an ON or USING clause}}
000257 do_test vtab6-3.2 {
000258 catchsql {
000259 SELECT * FROM t1 NATURAL JOIN t2 USING(b);
000260 }
000261 } {1 {a NATURAL join may not have an ON or USING clause}}
000262 do_test vtab6-3.3 {
000263 catchsql {
000264 SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b);
000265 }
000266 } {1 {near "USING": syntax error}}
000267 do_test vtab6-3.4 {
000268 catchsql {
000269 SELECT * FROM t1 JOIN t2 USING(a);
000270 }
000271 } {1 {cannot join using column a - column not present in both tables}}
000272 do_test vtab6-3.5 {
000273 catchsql { SELECT * FROM t1 USING(a) }
000274 } {1 {a JOIN clause is required before USING}}
000275 do_test vtab6-3.6 {
000276 catchsql {
000277 SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
000278 }
000279 } {1 {no such column: t3.a}}
000280
000281 # EVIDENCE-OF: R-47973-48020 you cannot say "INNER OUTER JOIN", because
000282 # that would be contradictory.
000283 do_test vtab6-3.7 {
000284 catchsql {
000285 SELECT * FROM t1 INNER OUTER JOIN t2;
000286 }
000287 } {1 {unknown join type: INNER OUTER}}
000288
000289 do_test vtab6-3.7 {
000290 catchsql {
000291 SELECT * FROM t1 LEFT BOGUS JOIN t2;
000292 }
000293 } {1 {unknown join type: LEFT BOGUS}}
000294
000295 do_test vtab6-4.1 {
000296 execsql {
000297 BEGIN;
000298 INSERT INTO t6 VALUES(NULL);
000299 INSERT INTO t6 VALUES(NULL);
000300 INSERT INTO t6 SELECT * FROM t6;
000301 INSERT INTO t6 SELECT * FROM t6;
000302 INSERT INTO t6 SELECT * FROM t6;
000303 INSERT INTO t6 SELECT * FROM t6;
000304 INSERT INTO t6 SELECT * FROM t6;
000305 INSERT INTO t6 SELECT * FROM t6;
000306 COMMIT;
000307 }
000308 execsql {
000309 SELECT * FROM t6 NATURAL JOIN t5;
000310 }
000311 } {}
000312 do_test vtab6-4.2 {
000313 execsql {
000314 SELECT * FROM t6, t5 WHERE t6.a<t5.a;
000315 }
000316 } {}
000317 do_test vtab6-4.3 {
000318 execsql {
000319 SELECT * FROM t6, t5 WHERE t6.a>t5.a;
000320 }
000321 } {}
000322 do_test vtab6-4.4 {
000323 execsql {
000324 UPDATE t6 SET a='xyz';
000325 SELECT * FROM t6 NATURAL JOIN t5;
000326 }
000327 } {}
000328 do_test vtab6-4.6 {
000329 execsql {
000330 SELECT * FROM t6, t5 WHERE t6.a<t5.a;
000331 }
000332 } {}
000333 do_test vtab6-4.7 {
000334 execsql {
000335 SELECT * FROM t6, t5 WHERE t6.a>t5.a;
000336 }
000337 } {}
000338 do_test vtab6-4.8 {
000339 execsql {
000340 UPDATE t6 SET a=1;
000341 SELECT * FROM t6 NATURAL JOIN t5;
000342 }
000343 } {}
000344 do_test vtab6-4.9 {
000345 execsql {
000346 SELECT * FROM t6, t5 WHERE t6.a<t5.a;
000347 }
000348 } {}
000349 do_test vtab6-4.10 {
000350 execsql {
000351 SELECT * FROM t6, t5 WHERE t6.a>t5.a;
000352 }
000353 } {}
000354
000355 # A test for ticket #247.
000356 #
000357 do_test vtab6-7.1 {
000358 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1
000359 execsql {
000360 INSERT INTO t7 VALUES ("pa1", 1);
000361 INSERT INTO t7 VALUES ("pa2", NULL);
000362 INSERT INTO t7 VALUES ("pa3", NULL);
000363 INSERT INTO t7 VALUES ("pa4", 2);
000364 INSERT INTO t7 VALUES ("pa30", 131);
000365 INSERT INTO t7 VALUES ("pa31", 130);
000366 INSERT INTO t7 VALUES ("pa28", NULL);
000367
000368 INSERT INTO t8 VALUES (1, "pa1");
000369 INSERT INTO t8 VALUES (2, "pa4");
000370 INSERT INTO t8 VALUES (3, NULL);
000371 INSERT INTO t8 VALUES (4, NULL);
000372 INSERT INTO t8 VALUES (130, "pa31");
000373 INSERT INTO t8 VALUES (131, "pa30");
000374
000375 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
000376 }
000377 } {1 999 999 2 131 130 999}
000378
000379 # Make sure a left join where the right table is really a view that
000380 # is itself a join works right. Ticket #306.
000381 #
000382 ifcapable view {
000383 do_test vtab6-8.1 {
000384 execsql {
000385 BEGIN;
000386 INSERT INTO t9 VALUES(1,11);
000387 INSERT INTO t9 VALUES(2,22);
000388 INSERT INTO t10 VALUES(1,2);
000389 INSERT INTO t10 VALUES(3,3);
000390 INSERT INTO t11 VALUES(2,111);
000391 INSERT INTO t11 VALUES(3,333);
000392 CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p;
000393 COMMIT;
000394 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );
000395 }
000396 } {1 11 1 111 2 22 {} {}}
000397 ifcapable subquery {
000398 do_test vtab6-8.2 {
000399 execsql {
000400 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)
000401 ON( a=x);
000402 }
000403 } {1 11 1 111 2 22 {} {}}
000404 }
000405 do_test vtab6-8.3 {
000406 execsql {
000407 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );
000408 }
000409 } {1 111 1 11 3 333 {} {}}
000410 } ;# ifcapable view
000411
000412 # Ticket #350 describes a scenario where LEFT OUTER JOIN does not
000413 # function correctly if the right table in the join is really
000414 # subquery.
000415 #
000416 # To test the problem, we generate the same LEFT OUTER JOIN in two
000417 # separate selects but with on using a subquery and the other calling
000418 # the table directly. Then connect the two SELECTs using an EXCEPT.
000419 # Both queries should generate the same results so the answer should
000420 # be an empty set.
000421 #
000422 ifcapable compound {
000423 do_test vtab6-9.1 {
000424 execsql {
000425 BEGIN;
000426 INSERT INTO t12 VALUES(1,11);
000427 INSERT INTO t12 VALUES(2,22);
000428 INSERT INTO t13 VALUES(22,222);
000429 COMMIT;
000430 }
000431 } {}
000432
000433 ifcapable subquery {
000434 do_test vtab6-9.1.1 {
000435 execsql {
000436 SELECT * FROM t12 NATURAL LEFT JOIN t13
000437 EXCEPT
000438 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);
000439 }
000440 } {}
000441 }
000442 ifcapable view {
000443 do_test vtab6-9.2 {
000444 execsql {
000445 CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;
000446 SELECT * FROM t12 NATURAL LEFT JOIN t13
000447 EXCEPT
000448 SELECT * FROM t12 NATURAL LEFT JOIN v13;
000449 }
000450 } {}
000451 } ;# ifcapable view
000452 } ;# ifcapable compound
000453
000454 ifcapable subquery {
000455 do_test vtab6-10.1 {
000456 execsql {
000457 CREATE INDEX i22 ON real_t22(q);
000458 SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=
000459 (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);
000460 }
000461 } {}
000462 } ;# ifcapable subquery
000463
000464 do_test vtab6-11.1.0 {
000465 execsql {
000466 CREATE TABLE ab_r(a, b);
000467 CREATE TABLE bc_r(b, c);
000468
000469 CREATE VIRTUAL TABLE ab USING echo(ab_r);
000470 CREATE VIRTUAL TABLE bc USING echo(bc_r);
000471
000472 INSERT INTO ab VALUES(1, 2);
000473 INSERT INTO bc VALUES(2, 3);
000474 }
000475 } {}
000476
000477 do_test vtab6-11.1.1 {
000478 execsql {
000479 SELECT a, b, c FROM ab NATURAL JOIN bc;
000480 }
000481 } {1 2 3}
000482 do_test vtab6-11.1.2 {
000483 execsql {
000484 SELECT a, b, c FROM bc NATURAL JOIN ab;
000485 }
000486 } {1 2 3}
000487
000488 set ::echo_module_cost 1.0
000489
000490 do_test vtab6-11.1.3 {
000491 execsql {
000492 SELECT a, b, c FROM ab NATURAL JOIN bc;
000493 }
000494 } {1 2 3}
000495 do_test vtab6-11.1.4 {
000496 execsql {
000497 SELECT a, b, c FROM bc NATURAL JOIN ab;
000498 }
000499 } {1 2 3}
000500
000501
000502 do_test vtab6-11.2.0 {
000503 execsql {
000504 CREATE INDEX ab_i ON ab_r(b);
000505 CREATE INDEX bc_i ON bc_r(b);
000506 }
000507 } {}
000508
000509 unset ::echo_module_cost
000510
000511 do_test vtab6-11.2.1 {
000512 execsql {
000513 SELECT a, b, c FROM ab NATURAL JOIN bc;
000514 }
000515 } {1 2 3}
000516 do_test vtab6-11.2.2 {
000517 execsql {
000518 SELECT a, b, c FROM bc NATURAL JOIN ab;
000519 }
000520 } {1 2 3}
000521
000522 set ::echo_module_cost 1.0
000523
000524 do_test vtab6-11.2.3 {
000525 execsql {
000526 SELECT a, b, c FROM ab NATURAL JOIN bc;
000527 }
000528 } {1 2 3}
000529 do_test vtab6-11.2.4 {
000530 execsql {
000531 SELECT a, b, c FROM bc NATURAL JOIN ab;
000532 }
000533 } {1 2 3}
000534
000535 unset ::echo_module_cost
000536 db close
000537 sqlite3 db test.db
000538 register_echo_module [sqlite3_connection_pointer db]
000539
000540 do_test vtab6-11.3.1 {
000541 execsql {
000542 SELECT a, b, c FROM ab NATURAL JOIN bc;
000543 }
000544 } {1 2 3}
000545
000546 do_test vtab6-11.3.2 {
000547 execsql {
000548 SELECT a, b, c FROM bc NATURAL JOIN ab;
000549 }
000550 } {1 2 3}
000551
000552 set ::echo_module_cost 1.0
000553
000554 do_test vtab6-11.3.3 {
000555 execsql {
000556 SELECT a, b, c FROM ab NATURAL JOIN bc;
000557 }
000558 } {1 2 3}
000559 do_test vtab6-11.3.4 {
000560 execsql {
000561 SELECT a, b, c FROM bc NATURAL JOIN ab;
000562 }
000563 } {1 2 3}
000564
000565 unset ::echo_module_cost
000566
000567 set ::echo_module_ignore_usable 1
000568 db cache flush
000569
000570 do_test vtab6-11.4.1 {
000571 catchsql {
000572 SELECT a, b, c FROM ab NATURAL JOIN bc;
000573 }
000574 } {1 {ab.xBestIndex malfunction}}
000575 do_test vtab6-11.4.2 {
000576 catchsql {
000577 SELECT a, b, c FROM bc NATURAL JOIN ab;
000578 }
000579 } {1 {bc.xBestIndex malfunction}}
000580
000581 unset ::echo_module_ignore_usable
000582
000583 finish_test