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 19set testdir [file dirname $argv0] 20source $testdir/tester.tcl 21 22ifcapable !vtab { 23 finish_test 24 return 25} 26 27register_echo_module [sqlite3_connection_pointer db] 28 29execsql { 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} 46foreach 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 50do_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} 58do_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 67do_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} 72do_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} 77do_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} 82do_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} 87do_test vtab6-1.3.4 { 88 execsql { 89 SELECT b FROM t1 NATURAL JOIN t2; 90 } 91} {2 3} 92do_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} 97do_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} 102do_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} 107do_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} 112do_test vtab6-1.4.5 { 113 execsql { 114 SELECT b FROM t1 JOIN t2 USING(b); 115 } 116} {2 3} 117do_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} 122do_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} 127do_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 133do_test vtab6-1.8 { 134 execsql { 135 SELECT * FROM t1 NATURAL CROSS JOIN t2; 136 } 137} {1 2 3 4 2 3 4 5} 138do_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} 143do_test vtab6-1.10 { 144 execsql { 145 SELECT * FROM t1 NATURAL INNER JOIN t2; 146 } 147} {1 2 3 4 2 3 4 5} 148do_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} 153do_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 159ifcapable 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 174do_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} 182do_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} 187do_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} 192do_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} 200do_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} 205do_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} 210do_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 216do_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 {}} 221do_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} 226do_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}} 231do_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} 236do_test vtab6-2.4.1 { 237 execsql { 238 SELECT * FROM t1 LEFT JOIN t2 ON t1.a IS t2.d 239 } 240} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3} 241do_test vtab6-2.5 { 242 execsql { 243 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1 244 } 245} {2 3 4 {} {} {} 3 4 5 1 2 3} 246do_test vtab6-2.6 { 247 execsql { 248 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1 249 } 250} {1 2 3 {} {} {} 2 3 4 {} {} {}} 251 252do_test vtab6-3.1 { 253 catchsql { 254 SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b; 255 } 256} {1 {a NATURAL join may not have an ON or USING clause}} 257do_test vtab6-3.2 { 258 catchsql { 259 SELECT * FROM t1 NATURAL JOIN t2 USING(b); 260 } 261} {1 {a NATURAL join may not have an ON or USING clause}} 262do_test vtab6-3.3 { 263 catchsql { 264 SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b); 265 } 266} {1 {cannot have both ON and USING clauses in the same join}} 267do_test vtab6-3.4 { 268 catchsql { 269 SELECT * FROM t1 JOIN t2 USING(a); 270 } 271} {1 {cannot join using column a - column not present in both tables}} 272do_test vtab6-3.5 { 273 catchsql { SELECT * FROM t1 USING(a) } 274} {1 {a JOIN clause is required before USING}} 275do_test vtab6-3.6 { 276 catchsql { 277 SELECT * FROM t1 JOIN t2 ON t3.a=t2.b; 278 } 279} {1 {no such column: t3.a}} 280do_test vtab6-3.7 { 281 catchsql { 282 SELECT * FROM t1 INNER OUTER JOIN t2; 283 } 284} {1 {unknown or unsupported join type: INNER OUTER}} 285do_test vtab6-3.7 { 286 catchsql { 287 SELECT * FROM t1 LEFT BOGUS JOIN t2; 288 } 289} {1 {unknown or unsupported join type: LEFT BOGUS}} 290 291do_test vtab6-4.1 { 292 execsql { 293 BEGIN; 294 INSERT INTO t6 VALUES(NULL); 295 INSERT INTO t6 VALUES(NULL); 296 INSERT INTO t6 SELECT * FROM t6; 297 INSERT INTO t6 SELECT * FROM t6; 298 INSERT INTO t6 SELECT * FROM t6; 299 INSERT INTO t6 SELECT * FROM t6; 300 INSERT INTO t6 SELECT * FROM t6; 301 INSERT INTO t6 SELECT * FROM t6; 302 COMMIT; 303 } 304 execsql { 305 SELECT * FROM t6 NATURAL JOIN t5; 306 } 307} {} 308do_test vtab6-4.2 { 309 execsql { 310 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 311 } 312} {} 313do_test vtab6-4.3 { 314 execsql { 315 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 316 } 317} {} 318do_test vtab6-4.4 { 319 execsql { 320 UPDATE t6 SET a='xyz'; 321 SELECT * FROM t6 NATURAL JOIN t5; 322 } 323} {} 324do_test vtab6-4.6 { 325 execsql { 326 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 327 } 328} {} 329do_test vtab6-4.7 { 330 execsql { 331 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 332 } 333} {} 334do_test vtab6-4.8 { 335 execsql { 336 UPDATE t6 SET a=1; 337 SELECT * FROM t6 NATURAL JOIN t5; 338 } 339} {} 340do_test vtab6-4.9 { 341 execsql { 342 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 343 } 344} {} 345do_test vtab6-4.10 { 346 execsql { 347 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 348 } 349} {} 350 351# A test for ticket #247. 352# 353do_test vtab6-7.1 { 354 execsql { 355 INSERT INTO t7 VALUES ("pa1", 1); 356 INSERT INTO t7 VALUES ("pa2", NULL); 357 INSERT INTO t7 VALUES ("pa3", NULL); 358 INSERT INTO t7 VALUES ("pa4", 2); 359 INSERT INTO t7 VALUES ("pa30", 131); 360 INSERT INTO t7 VALUES ("pa31", 130); 361 INSERT INTO t7 VALUES ("pa28", NULL); 362 363 INSERT INTO t8 VALUES (1, "pa1"); 364 INSERT INTO t8 VALUES (2, "pa4"); 365 INSERT INTO t8 VALUES (3, NULL); 366 INSERT INTO t8 VALUES (4, NULL); 367 INSERT INTO t8 VALUES (130, "pa31"); 368 INSERT INTO t8 VALUES (131, "pa30"); 369 370 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a; 371 } 372} {1 999 999 2 131 130 999} 373 374# Make sure a left join where the right table is really a view that 375# is itself a join works right. Ticket #306. 376# 377ifcapable view { 378do_test vtab6-8.1 { 379 execsql { 380 BEGIN; 381 INSERT INTO t9 VALUES(1,11); 382 INSERT INTO t9 VALUES(2,22); 383 INSERT INTO t10 VALUES(1,2); 384 INSERT INTO t10 VALUES(3,3); 385 INSERT INTO t11 VALUES(2,111); 386 INSERT INTO t11 VALUES(3,333); 387 CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p; 388 COMMIT; 389 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x ); 390 } 391} {1 11 1 111 2 22 {} {}} 392ifcapable subquery { 393 do_test vtab6-8.2 { 394 execsql { 395 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p) 396 ON( a=x); 397 } 398 } {1 11 1 111 2 22 {} {}} 399} 400do_test vtab6-8.3 { 401 execsql { 402 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x ); 403 } 404} {1 111 1 11 3 333 {} {}} 405} ;# ifcapable view 406 407# Ticket #350 describes a scenario where LEFT OUTER JOIN does not 408# function correctly if the right table in the join is really 409# subquery. 410# 411# To test the problem, we generate the same LEFT OUTER JOIN in two 412# separate selects but with on using a subquery and the other calling 413# the table directly. Then connect the two SELECTs using an EXCEPT. 414# Both queries should generate the same results so the answer should 415# be an empty set. 416# 417ifcapable compound { 418do_test vtab6-9.1 { 419 execsql { 420 BEGIN; 421 INSERT INTO t12 VALUES(1,11); 422 INSERT INTO t12 VALUES(2,22); 423 INSERT INTO t13 VALUES(22,222); 424 COMMIT; 425 } 426} {} 427 428ifcapable subquery { 429 do_test vtab6-9.1.1 { 430 execsql { 431 SELECT * FROM t12 NATURAL LEFT JOIN t13 432 EXCEPT 433 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0); 434 } 435 } {} 436} 437ifcapable view { 438 do_test vtab6-9.2 { 439 execsql { 440 CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0; 441 SELECT * FROM t12 NATURAL LEFT JOIN t13 442 EXCEPT 443 SELECT * FROM t12 NATURAL LEFT JOIN v13; 444 } 445 } {} 446} ;# ifcapable view 447} ;# ifcapable compound 448 449ifcapable subquery { 450do_test vtab6-10.1 { 451 execsql { 452 CREATE INDEX i22 ON real_t22(q); 453 SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q= 454 (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1); 455 } 456} {} 457} ;# ifcapable subquery 458 459do_test vtab6-11.1.0 { 460 execsql { 461 CREATE TABLE ab_r(a, b); 462 CREATE TABLE bc_r(b, c); 463 464 CREATE VIRTUAL TABLE ab USING echo(ab_r); 465 CREATE VIRTUAL TABLE bc USING echo(bc_r); 466 467 INSERT INTO ab VALUES(1, 2); 468 INSERT INTO bc VALUES(2, 3); 469 } 470} {} 471 472do_test vtab6-11.1.1 { 473 execsql { 474 SELECT a, b, c FROM ab NATURAL JOIN bc; 475 } 476} {1 2 3} 477do_test vtab6-11.1.2 { 478 execsql { 479 SELECT a, b, c FROM bc NATURAL JOIN ab; 480 } 481} {1 2 3} 482 483set ::echo_module_cost 1.0 484 485do_test vtab6-11.1.3 { 486 execsql { 487 SELECT a, b, c FROM ab NATURAL JOIN bc; 488 } 489} {1 2 3} 490do_test vtab6-11.1.4 { 491 execsql { 492 SELECT a, b, c FROM bc NATURAL JOIN ab; 493 } 494} {1 2 3} 495 496 497do_test vtab6-11.2.0 { 498 execsql { 499 CREATE INDEX ab_i ON ab_r(b); 500 CREATE INDEX bc_i ON bc_r(b); 501 } 502} {} 503 504unset ::echo_module_cost 505 506do_test vtab6-11.2.1 { 507 execsql { 508 SELECT a, b, c FROM ab NATURAL JOIN bc; 509 } 510} {1 2 3} 511do_test vtab6-11.2.2 { 512 execsql { 513 SELECT a, b, c FROM bc NATURAL JOIN ab; 514 } 515} {1 2 3} 516 517set ::echo_module_cost 1.0 518 519do_test vtab6-11.2.3 { 520 execsql { 521 SELECT a, b, c FROM ab NATURAL JOIN bc; 522 } 523} {1 2 3} 524do_test vtab6-11.2.4 { 525 execsql { 526 SELECT a, b, c FROM bc NATURAL JOIN ab; 527 } 528} {1 2 3} 529 530unset ::echo_module_cost 531db close 532sqlite3 db test.db 533register_echo_module [sqlite3_connection_pointer db] 534 535do_test vtab6-11.3.1 { 536 execsql { 537 SELECT a, b, c FROM ab NATURAL JOIN bc; 538 } 539} {1 2 3} 540 541do_test vtab6-11.3.2 { 542 execsql { 543 SELECT a, b, c FROM bc NATURAL JOIN ab; 544 } 545} {1 2 3} 546 547set ::echo_module_cost 1.0 548 549do_test vtab6-11.3.3 { 550 execsql { 551 SELECT a, b, c FROM ab NATURAL JOIN bc; 552 } 553} {1 2 3} 554do_test vtab6-11.3.4 { 555 execsql { 556 SELECT a, b, c FROM bc NATURAL JOIN ab; 557 } 558} {1 2 3} 559 560unset ::echo_module_cost 561 562set ::echo_module_ignore_usable 1 563db cache flush 564 565do_test vtab6-11.4.1 { 566 catchsql { 567 SELECT a, b, c FROM ab NATURAL JOIN bc; 568 } 569} {1 {table ab: xBestIndex returned an invalid plan}} 570do_test vtab6-11.4.2 { 571 catchsql { 572 SELECT a, b, c FROM bc NATURAL JOIN ab; 573 } 574} {1 {table bc: xBestIndex returned an invalid plan}} 575 576unset ::echo_module_ignore_usable 577 578finish_test 579