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.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} 241do_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 247do_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}} 252do_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}} 257do_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}} 262do_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}} 267do_test vtab6-3.5 { 268 catchsql { SELECT * FROM t1 USING(a) } 269} {1 {a JOIN clause is required before USING}} 270do_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}} 275do_test vtab6-3.7 { 276 catchsql { 277 SELECT * FROM t1 INNER OUTER JOIN t2; 278 } 279} {1 {unknown or unsupported join type: INNER OUTER}} 280do_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 286do_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} {} 303do_test vtab6-4.2 { 304 execsql { 305 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 306 } 307} {} 308do_test vtab6-4.3 { 309 execsql { 310 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 311 } 312} {} 313do_test vtab6-4.4 { 314 execsql { 315 UPDATE t6 SET a='xyz'; 316 SELECT * FROM t6 NATURAL JOIN t5; 317 } 318} {} 319do_test vtab6-4.6 { 320 execsql { 321 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 322 } 323} {} 324do_test vtab6-4.7 { 325 execsql { 326 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 327 } 328} {} 329do_test vtab6-4.8 { 330 execsql { 331 UPDATE t6 SET a=1; 332 SELECT * FROM t6 NATURAL JOIN t5; 333 } 334} {} 335do_test vtab6-4.9 { 336 execsql { 337 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 338 } 339} {} 340do_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# 348do_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# 372ifcapable view { 373do_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 {} {}} 387ifcapable 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} 395do_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# 412ifcapable compound { 413do_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 423ifcapable 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} 432ifcapable 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 444ifcapable subquery { 445do_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 454do_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 467do_test vtab6-11.1.1 { 468 execsql { 469 SELECT a, b, c FROM ab NATURAL JOIN bc; 470 } 471} {1 2 3} 472do_test vtab6-11.1.2 { 473 execsql { 474 SELECT a, b, c FROM bc NATURAL JOIN ab; 475 } 476} {1 2 3} 477 478set ::echo_module_cost 1.0 479 480do_test vtab6-11.1.3 { 481 execsql { 482 SELECT a, b, c FROM ab NATURAL JOIN bc; 483 } 484} {1 2 3} 485do_test vtab6-11.1.4 { 486 execsql { 487 SELECT a, b, c FROM bc NATURAL JOIN ab; 488 } 489} {1 2 3} 490 491 492do_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 499unset ::echo_module_cost 500 501do_test vtab6-11.2.1 { 502 execsql { 503 SELECT a, b, c FROM ab NATURAL JOIN bc; 504 } 505} {1 2 3} 506do_test vtab6-11.2.2 { 507 execsql { 508 SELECT a, b, c FROM bc NATURAL JOIN ab; 509 } 510} {1 2 3} 511 512set ::echo_module_cost 1.0 513 514do_test vtab6-11.2.3 { 515 execsql { 516 SELECT a, b, c FROM ab NATURAL JOIN bc; 517 } 518} {1 2 3} 519do_test vtab6-11.2.4 { 520 execsql { 521 SELECT a, b, c FROM bc NATURAL JOIN ab; 522 } 523} {1 2 3} 524 525unset ::echo_module_cost 526db close 527sqlite3 db test.db 528register_echo_module [sqlite3_connection_pointer db] 529 530do_test vtab6-11.3.1 { 531 execsql { 532 SELECT a, b, c FROM ab NATURAL JOIN bc; 533 } 534} {1 2 3} 535 536do_test vtab6-11.3.2 { 537 execsql { 538 SELECT a, b, c FROM bc NATURAL JOIN ab; 539 } 540} {1 2 3} 541 542set ::echo_module_cost 1.0 543 544do_test vtab6-11.3.3 { 545 execsql { 546 SELECT a, b, c FROM ab NATURAL JOIN bc; 547 } 548} {1 2 3} 549do_test vtab6-11.3.4 { 550 execsql { 551 SELECT a, b, c FROM bc NATURAL JOIN ab; 552 } 553} {1 2 3} 554 555unset ::echo_module_cost 556 557set ::echo_module_ignore_usable 1 558db cache flush 559 560do_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}} 565do_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 571unset ::echo_module_ignore_usable 572 573finish_test 574