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} 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}} 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 {near "USING": syntax error}} 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}} 280 281# EVIDENCE-OF: R-47973-48020 you cannot say "INNER OUTER JOIN", because 282# that would be contradictory. 283do_test vtab6-3.7 { 284 catchsql { 285 SELECT * FROM t1 INNER OUTER JOIN t2; 286 } 287} {1 {unknown join type: INNER OUTER}} 288 289do_test vtab6-3.7 { 290 catchsql { 291 SELECT * FROM t1 LEFT BOGUS JOIN t2; 292 } 293} {1 {unknown join type: LEFT BOGUS}} 294 295do_test vtab6-4.1 { 296 execsql { 297 BEGIN; 298 INSERT INTO t6 VALUES(NULL); 299 INSERT INTO t6 VALUES(NULL); 300 INSERT INTO t6 SELECT * FROM t6; 301 INSERT INTO t6 SELECT * FROM t6; 302 INSERT INTO t6 SELECT * FROM t6; 303 INSERT INTO t6 SELECT * FROM t6; 304 INSERT INTO t6 SELECT * FROM t6; 305 INSERT INTO t6 SELECT * FROM t6; 306 COMMIT; 307 } 308 execsql { 309 SELECT * FROM t6 NATURAL JOIN t5; 310 } 311} {} 312do_test vtab6-4.2 { 313 execsql { 314 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 315 } 316} {} 317do_test vtab6-4.3 { 318 execsql { 319 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 320 } 321} {} 322do_test vtab6-4.4 { 323 execsql { 324 UPDATE t6 SET a='xyz'; 325 SELECT * FROM t6 NATURAL JOIN t5; 326 } 327} {} 328do_test vtab6-4.6 { 329 execsql { 330 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 331 } 332} {} 333do_test vtab6-4.7 { 334 execsql { 335 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 336 } 337} {} 338do_test vtab6-4.8 { 339 execsql { 340 UPDATE t6 SET a=1; 341 SELECT * FROM t6 NATURAL JOIN t5; 342 } 343} {} 344do_test vtab6-4.9 { 345 execsql { 346 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 347 } 348} {} 349do_test vtab6-4.10 { 350 execsql { 351 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 352 } 353} {} 354 355# A test for ticket #247. 356# 357do_test vtab6-7.1 { 358 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1 359 execsql { 360 INSERT INTO t7 VALUES ("pa1", 1); 361 INSERT INTO t7 VALUES ("pa2", NULL); 362 INSERT INTO t7 VALUES ("pa3", NULL); 363 INSERT INTO t7 VALUES ("pa4", 2); 364 INSERT INTO t7 VALUES ("pa30", 131); 365 INSERT INTO t7 VALUES ("pa31", 130); 366 INSERT INTO t7 VALUES ("pa28", NULL); 367 368 INSERT INTO t8 VALUES (1, "pa1"); 369 INSERT INTO t8 VALUES (2, "pa4"); 370 INSERT INTO t8 VALUES (3, NULL); 371 INSERT INTO t8 VALUES (4, NULL); 372 INSERT INTO t8 VALUES (130, "pa31"); 373 INSERT INTO t8 VALUES (131, "pa30"); 374 375 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a; 376 } 377} {1 999 999 2 131 130 999} 378 379# Make sure a left join where the right table is really a view that 380# is itself a join works right. Ticket #306. 381# 382ifcapable view { 383do_test vtab6-8.1 { 384 execsql { 385 BEGIN; 386 INSERT INTO t9 VALUES(1,11); 387 INSERT INTO t9 VALUES(2,22); 388 INSERT INTO t10 VALUES(1,2); 389 INSERT INTO t10 VALUES(3,3); 390 INSERT INTO t11 VALUES(2,111); 391 INSERT INTO t11 VALUES(3,333); 392 CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p; 393 COMMIT; 394 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x ); 395 } 396} {1 11 1 111 2 22 {} {}} 397ifcapable subquery { 398 do_test vtab6-8.2 { 399 execsql { 400 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p) 401 ON( a=x); 402 } 403 } {1 11 1 111 2 22 {} {}} 404} 405do_test vtab6-8.3 { 406 execsql { 407 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x ); 408 } 409} {1 111 1 11 3 333 {} {}} 410} ;# ifcapable view 411 412# Ticket #350 describes a scenario where LEFT OUTER JOIN does not 413# function correctly if the right table in the join is really 414# subquery. 415# 416# To test the problem, we generate the same LEFT OUTER JOIN in two 417# separate selects but with on using a subquery and the other calling 418# the table directly. Then connect the two SELECTs using an EXCEPT. 419# Both queries should generate the same results so the answer should 420# be an empty set. 421# 422ifcapable compound { 423do_test vtab6-9.1 { 424 execsql { 425 BEGIN; 426 INSERT INTO t12 VALUES(1,11); 427 INSERT INTO t12 VALUES(2,22); 428 INSERT INTO t13 VALUES(22,222); 429 COMMIT; 430 } 431} {} 432 433ifcapable subquery { 434 do_test vtab6-9.1.1 { 435 execsql { 436 SELECT * FROM t12 NATURAL LEFT JOIN t13 437 EXCEPT 438 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0); 439 } 440 } {} 441} 442ifcapable view { 443 do_test vtab6-9.2 { 444 execsql { 445 CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0; 446 SELECT * FROM t12 NATURAL LEFT JOIN t13 447 EXCEPT 448 SELECT * FROM t12 NATURAL LEFT JOIN v13; 449 } 450 } {} 451} ;# ifcapable view 452} ;# ifcapable compound 453 454ifcapable subquery { 455do_test vtab6-10.1 { 456 execsql { 457 CREATE INDEX i22 ON real_t22(q); 458 SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q= 459 (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1); 460 } 461} {} 462} ;# ifcapable subquery 463 464do_test vtab6-11.1.0 { 465 execsql { 466 CREATE TABLE ab_r(a, b); 467 CREATE TABLE bc_r(b, c); 468 469 CREATE VIRTUAL TABLE ab USING echo(ab_r); 470 CREATE VIRTUAL TABLE bc USING echo(bc_r); 471 472 INSERT INTO ab VALUES(1, 2); 473 INSERT INTO bc VALUES(2, 3); 474 } 475} {} 476 477do_test vtab6-11.1.1 { 478 execsql { 479 SELECT a, b, c FROM ab NATURAL JOIN bc; 480 } 481} {1 2 3} 482do_test vtab6-11.1.2 { 483 execsql { 484 SELECT a, b, c FROM bc NATURAL JOIN ab; 485 } 486} {1 2 3} 487 488set ::echo_module_cost 1.0 489 490do_test vtab6-11.1.3 { 491 execsql { 492 SELECT a, b, c FROM ab NATURAL JOIN bc; 493 } 494} {1 2 3} 495do_test vtab6-11.1.4 { 496 execsql { 497 SELECT a, b, c FROM bc NATURAL JOIN ab; 498 } 499} {1 2 3} 500 501 502do_test vtab6-11.2.0 { 503 execsql { 504 CREATE INDEX ab_i ON ab_r(b); 505 CREATE INDEX bc_i ON bc_r(b); 506 } 507} {} 508 509unset ::echo_module_cost 510 511do_test vtab6-11.2.1 { 512 execsql { 513 SELECT a, b, c FROM ab NATURAL JOIN bc; 514 } 515} {1 2 3} 516do_test vtab6-11.2.2 { 517 execsql { 518 SELECT a, b, c FROM bc NATURAL JOIN ab; 519 } 520} {1 2 3} 521 522set ::echo_module_cost 1.0 523 524do_test vtab6-11.2.3 { 525 execsql { 526 SELECT a, b, c FROM ab NATURAL JOIN bc; 527 } 528} {1 2 3} 529do_test vtab6-11.2.4 { 530 execsql { 531 SELECT a, b, c FROM bc NATURAL JOIN ab; 532 } 533} {1 2 3} 534 535unset ::echo_module_cost 536db close 537sqlite3 db test.db 538register_echo_module [sqlite3_connection_pointer db] 539 540do_test vtab6-11.3.1 { 541 execsql { 542 SELECT a, b, c FROM ab NATURAL JOIN bc; 543 } 544} {1 2 3} 545 546do_test vtab6-11.3.2 { 547 execsql { 548 SELECT a, b, c FROM bc NATURAL JOIN ab; 549 } 550} {1 2 3} 551 552set ::echo_module_cost 1.0 553 554do_test vtab6-11.3.3 { 555 execsql { 556 SELECT a, b, c FROM ab NATURAL JOIN bc; 557 } 558} {1 2 3} 559do_test vtab6-11.3.4 { 560 execsql { 561 SELECT a, b, c FROM bc NATURAL JOIN ab; 562 } 563} {1 2 3} 564 565unset ::echo_module_cost 566 567set ::echo_module_ignore_usable 1 568db cache flush 569 570do_test vtab6-11.4.1 { 571 catchsql { 572 SELECT a, b, c FROM ab NATURAL JOIN bc; 573 } 574} {1 {ab.xBestIndex malfunction}} 575do_test vtab6-11.4.2 { 576 catchsql { 577 SELECT a, b, c FROM bc NATURAL JOIN ab; 578 } 579} {1 {bc.xBestIndex malfunction}} 580 581unset ::echo_module_ignore_usable 582 583finish_test 584