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 sqlite3_db_config db SQLITE_DBCONFIG_DQS_DML 1 355 execsql { 356 INSERT INTO t7 VALUES ("pa1", 1); 357 INSERT INTO t7 VALUES ("pa2", NULL); 358 INSERT INTO t7 VALUES ("pa3", NULL); 359 INSERT INTO t7 VALUES ("pa4", 2); 360 INSERT INTO t7 VALUES ("pa30", 131); 361 INSERT INTO t7 VALUES ("pa31", 130); 362 INSERT INTO t7 VALUES ("pa28", NULL); 363 364 INSERT INTO t8 VALUES (1, "pa1"); 365 INSERT INTO t8 VALUES (2, "pa4"); 366 INSERT INTO t8 VALUES (3, NULL); 367 INSERT INTO t8 VALUES (4, NULL); 368 INSERT INTO t8 VALUES (130, "pa31"); 369 INSERT INTO t8 VALUES (131, "pa30"); 370 371 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a; 372 } 373} {1 999 999 2 131 130 999} 374 375# Make sure a left join where the right table is really a view that 376# is itself a join works right. Ticket #306. 377# 378ifcapable view { 379do_test vtab6-8.1 { 380 execsql { 381 BEGIN; 382 INSERT INTO t9 VALUES(1,11); 383 INSERT INTO t9 VALUES(2,22); 384 INSERT INTO t10 VALUES(1,2); 385 INSERT INTO t10 VALUES(3,3); 386 INSERT INTO t11 VALUES(2,111); 387 INSERT INTO t11 VALUES(3,333); 388 CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p; 389 COMMIT; 390 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x ); 391 } 392} {1 11 1 111 2 22 {} {}} 393ifcapable subquery { 394 do_test vtab6-8.2 { 395 execsql { 396 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p) 397 ON( a=x); 398 } 399 } {1 11 1 111 2 22 {} {}} 400} 401do_test vtab6-8.3 { 402 execsql { 403 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x ); 404 } 405} {1 111 1 11 3 333 {} {}} 406} ;# ifcapable view 407 408# Ticket #350 describes a scenario where LEFT OUTER JOIN does not 409# function correctly if the right table in the join is really 410# subquery. 411# 412# To test the problem, we generate the same LEFT OUTER JOIN in two 413# separate selects but with on using a subquery and the other calling 414# the table directly. Then connect the two SELECTs using an EXCEPT. 415# Both queries should generate the same results so the answer should 416# be an empty set. 417# 418ifcapable compound { 419do_test vtab6-9.1 { 420 execsql { 421 BEGIN; 422 INSERT INTO t12 VALUES(1,11); 423 INSERT INTO t12 VALUES(2,22); 424 INSERT INTO t13 VALUES(22,222); 425 COMMIT; 426 } 427} {} 428 429ifcapable subquery { 430 do_test vtab6-9.1.1 { 431 execsql { 432 SELECT * FROM t12 NATURAL LEFT JOIN t13 433 EXCEPT 434 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0); 435 } 436 } {} 437} 438ifcapable view { 439 do_test vtab6-9.2 { 440 execsql { 441 CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0; 442 SELECT * FROM t12 NATURAL LEFT JOIN t13 443 EXCEPT 444 SELECT * FROM t12 NATURAL LEFT JOIN v13; 445 } 446 } {} 447} ;# ifcapable view 448} ;# ifcapable compound 449 450ifcapable subquery { 451do_test vtab6-10.1 { 452 execsql { 453 CREATE INDEX i22 ON real_t22(q); 454 SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q= 455 (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1); 456 } 457} {} 458} ;# ifcapable subquery 459 460do_test vtab6-11.1.0 { 461 execsql { 462 CREATE TABLE ab_r(a, b); 463 CREATE TABLE bc_r(b, c); 464 465 CREATE VIRTUAL TABLE ab USING echo(ab_r); 466 CREATE VIRTUAL TABLE bc USING echo(bc_r); 467 468 INSERT INTO ab VALUES(1, 2); 469 INSERT INTO bc VALUES(2, 3); 470 } 471} {} 472 473do_test vtab6-11.1.1 { 474 execsql { 475 SELECT a, b, c FROM ab NATURAL JOIN bc; 476 } 477} {1 2 3} 478do_test vtab6-11.1.2 { 479 execsql { 480 SELECT a, b, c FROM bc NATURAL JOIN ab; 481 } 482} {1 2 3} 483 484set ::echo_module_cost 1.0 485 486do_test vtab6-11.1.3 { 487 execsql { 488 SELECT a, b, c FROM ab NATURAL JOIN bc; 489 } 490} {1 2 3} 491do_test vtab6-11.1.4 { 492 execsql { 493 SELECT a, b, c FROM bc NATURAL JOIN ab; 494 } 495} {1 2 3} 496 497 498do_test vtab6-11.2.0 { 499 execsql { 500 CREATE INDEX ab_i ON ab_r(b); 501 CREATE INDEX bc_i ON bc_r(b); 502 } 503} {} 504 505unset ::echo_module_cost 506 507do_test vtab6-11.2.1 { 508 execsql { 509 SELECT a, b, c FROM ab NATURAL JOIN bc; 510 } 511} {1 2 3} 512do_test vtab6-11.2.2 { 513 execsql { 514 SELECT a, b, c FROM bc NATURAL JOIN ab; 515 } 516} {1 2 3} 517 518set ::echo_module_cost 1.0 519 520do_test vtab6-11.2.3 { 521 execsql { 522 SELECT a, b, c FROM ab NATURAL JOIN bc; 523 } 524} {1 2 3} 525do_test vtab6-11.2.4 { 526 execsql { 527 SELECT a, b, c FROM bc NATURAL JOIN ab; 528 } 529} {1 2 3} 530 531unset ::echo_module_cost 532db close 533sqlite3 db test.db 534register_echo_module [sqlite3_connection_pointer db] 535 536do_test vtab6-11.3.1 { 537 execsql { 538 SELECT a, b, c FROM ab NATURAL JOIN bc; 539 } 540} {1 2 3} 541 542do_test vtab6-11.3.2 { 543 execsql { 544 SELECT a, b, c FROM bc NATURAL JOIN ab; 545 } 546} {1 2 3} 547 548set ::echo_module_cost 1.0 549 550do_test vtab6-11.3.3 { 551 execsql { 552 SELECT a, b, c FROM ab NATURAL JOIN bc; 553 } 554} {1 2 3} 555do_test vtab6-11.3.4 { 556 execsql { 557 SELECT a, b, c FROM bc NATURAL JOIN ab; 558 } 559} {1 2 3} 560 561unset ::echo_module_cost 562 563set ::echo_module_ignore_usable 1 564db cache flush 565 566do_test vtab6-11.4.1 { 567 catchsql { 568 SELECT a, b, c FROM ab NATURAL JOIN bc; 569 } 570} {1 {ab.xBestIndex malfunction}} 571do_test vtab6-11.4.2 { 572 catchsql { 573 SELECT a, b, c FROM bc NATURAL JOIN ab; 574 } 575} {1 {bc.xBestIndex malfunction}} 576 577unset ::echo_module_ignore_usable 578 579finish_test 580