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.3 2008/03/17 09:36:45 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 { 160breakpoint 161 do_test vtab6-1.13 { 162 execsql2 { 163 SELECT * FROM t1 NATURAL JOIN 164 (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3 165 } 166 } {a 1 b 2 c 3 d 4 e 5} 167 do_test vtab6-1.14 { 168 execsql2 { 169 SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx' 170 NATURAL JOIN t1 171 } 172 } {c 3 d 4 e 5 a 1 b 2} 173} 174 175do_test vtab6-1.15 { 176 execsql { 177 INSERT INTO t3 VALUES(2,3,4); 178 INSERT INTO t3 VALUES(3,4,5); 179 INSERT INTO t3 VALUES(4,5,6); 180 SELECT * FROM t3; 181 } 182} {2 3 4 3 4 5 4 5 6} 183do_test vtab6-1.16 { 184 execsql { 185 SELECT * FROM t1 natural join t2 natural join t3; 186 } 187} {1 2 3 4 5 2 3 4 5 6} 188do_test vtab6-1.17 { 189 execsql2 { 190 SELECT * FROM t1 natural join t2 natural join t3; 191 } 192} {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6} 193do_test vtab6-1.18 { 194 execsql { 195 INSERT INTO t4 VALUES(2,3,4); 196 INSERT INTO t4 VALUES(3,4,5); 197 INSERT INTO t4 VALUES(4,5,6); 198 SELECT * FROM t4; 199 } 200} {2 3 4 3 4 5 4 5 6} 201do_test vtab6-1.19.1 { 202 execsql { 203 SELECT * FROM t1 natural join t2 natural join t4; 204 } 205} {1 2 3 4 5 6} 206do_test vtab6-1.19.2 { 207 execsql2 { 208 SELECT * FROM t1 natural join t2 natural join t4; 209 } 210} {a 1 b 2 c 3 d 4 e 5 f 6} 211do_test vtab6-1.20 { 212 execsql { 213 SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1 214 } 215} {1 2 3 4 5} 216 217do_test vtab6-2.1 { 218 execsql { 219 SELECT * FROM t1 NATURAL LEFT JOIN t2; 220 } 221} {1 2 3 4 2 3 4 5 3 4 5 {}} 222do_test vtab6-2.2 { 223 execsql { 224 SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1; 225 } 226} {1 2 3 {} 2 3 4 1 3 4 5 2} 227do_test vtab6-2.3 { 228 catchsql { 229 SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2; 230 } 231} {1 {RIGHT and FULL OUTER JOINs are not currently supported}} 232do_test vtab6-2.4 { 233 execsql { 234 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d 235 } 236} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3} 237do_test vtab6-2.5 { 238 execsql { 239 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1 240 } 241} {2 3 4 {} {} {} 3 4 5 1 2 3} 242do_test vtab6-2.6 { 243 execsql { 244 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1 245 } 246} {1 2 3 {} {} {} 2 3 4 {} {} {}} 247 248do_test vtab6-3.1 { 249 catchsql { 250 SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b; 251 } 252} {1 {a NATURAL join may not have an ON or USING clause}} 253do_test vtab6-3.2 { 254 catchsql { 255 SELECT * FROM t1 NATURAL JOIN t2 USING(b); 256 } 257} {1 {a NATURAL join may not have an ON or USING clause}} 258do_test vtab6-3.3 { 259 catchsql { 260 SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b); 261 } 262} {1 {cannot have both ON and USING clauses in the same join}} 263do_test vtab6-3.4 { 264 catchsql { 265 SELECT * FROM t1 JOIN t2 USING(a); 266 } 267} {1 {cannot join using column a - column not present in both tables}} 268do_test vtab6-3.5 { 269 catchsql { 270 SELECT * FROM t1 USING(a); 271 } 272} {0 {1 2 3 2 3 4 3 4 5}} 273do_test vtab6-3.6 { 274 catchsql { 275 SELECT * FROM t1 JOIN t2 ON t3.a=t2.b; 276 } 277} {1 {no such column: t3.a}} 278do_test vtab6-3.7 { 279 catchsql { 280 SELECT * FROM t1 INNER OUTER JOIN t2; 281 } 282} {1 {unknown or unsupported join type: INNER OUTER}} 283do_test vtab6-3.7 { 284 catchsql { 285 SELECT * FROM t1 LEFT BOGUS JOIN t2; 286 } 287} {1 {unknown or unsupported join type: LEFT BOGUS}} 288 289do_test vtab6-4.1 { 290 execsql { 291 BEGIN; 292 INSERT INTO t6 VALUES(NULL); 293 INSERT INTO t6 VALUES(NULL); 294 INSERT INTO t6 SELECT * FROM t6; 295 INSERT INTO t6 SELECT * FROM t6; 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 COMMIT; 301 } 302 execsql { 303 SELECT * FROM t6 NATURAL JOIN t5; 304 } 305} {} 306do_test vtab6-4.2 { 307 execsql { 308 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 309 } 310} {} 311do_test vtab6-4.3 { 312 execsql { 313 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 314 } 315} {} 316do_test vtab6-4.4 { 317 execsql { 318 UPDATE t6 SET a='xyz'; 319 SELECT * FROM t6 NATURAL JOIN t5; 320 } 321} {} 322do_test vtab6-4.6 { 323 execsql { 324 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 325 } 326} {} 327do_test vtab6-4.7 { 328 execsql { 329 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 330 } 331} {} 332do_test vtab6-4.8 { 333 execsql { 334 UPDATE t6 SET a=1; 335 SELECT * FROM t6 NATURAL JOIN t5; 336 } 337} {} 338do_test vtab6-4.9 { 339 execsql { 340 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 341 } 342} {} 343do_test vtab6-4.10 { 344 execsql { 345 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 346 } 347} {} 348 349# A test for ticket #247. 350# 351do_test vtab6-7.1 { 352 execsql { 353 INSERT INTO t7 VALUES ("pa1", 1); 354 INSERT INTO t7 VALUES ("pa2", NULL); 355 INSERT INTO t7 VALUES ("pa3", NULL); 356 INSERT INTO t7 VALUES ("pa4", 2); 357 INSERT INTO t7 VALUES ("pa30", 131); 358 INSERT INTO t7 VALUES ("pa31", 130); 359 INSERT INTO t7 VALUES ("pa28", NULL); 360 361 INSERT INTO t8 VALUES (1, "pa1"); 362 INSERT INTO t8 VALUES (2, "pa4"); 363 INSERT INTO t8 VALUES (3, NULL); 364 INSERT INTO t8 VALUES (4, NULL); 365 INSERT INTO t8 VALUES (130, "pa31"); 366 INSERT INTO t8 VALUES (131, "pa30"); 367 368 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a; 369 } 370} {1 999 999 2 131 130 999} 371 372# Make sure a left join where the right table is really a view that 373# is itself a join works right. Ticket #306. 374# 375ifcapable view { 376do_test vtab6-8.1 { 377 execsql { 378 BEGIN; 379 INSERT INTO t9 VALUES(1,11); 380 INSERT INTO t9 VALUES(2,22); 381 INSERT INTO t10 VALUES(1,2); 382 INSERT INTO t10 VALUES(3,3); 383 INSERT INTO t11 VALUES(2,111); 384 INSERT INTO t11 VALUES(3,333); 385 CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p; 386 COMMIT; 387 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x ); 388 } 389} {1 11 1 111 2 22 {} {}} 390ifcapable subquery { 391 do_test vtab6-8.2 { 392 execsql { 393 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p) 394 ON( a=x); 395 } 396 } {1 11 1 111 2 22 {} {}} 397} 398do_test vtab6-8.3 { 399 execsql { 400 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x ); 401 } 402} {1 111 1 11 3 333 {} {}} 403} ;# ifcapable view 404 405# Ticket #350 describes a scenario where LEFT OUTER JOIN does not 406# function correctly if the right table in the join is really 407# subquery. 408# 409# To test the problem, we generate the same LEFT OUTER JOIN in two 410# separate selects but with on using a subquery and the other calling 411# the table directly. Then connect the two SELECTs using an EXCEPT. 412# Both queries should generate the same results so the answer should 413# be an empty set. 414# 415ifcapable compound { 416do_test vtab6-9.1 { 417 execsql { 418 BEGIN; 419 INSERT INTO t12 VALUES(1,11); 420 INSERT INTO t12 VALUES(2,22); 421 INSERT INTO t13 VALUES(22,222); 422 COMMIT; 423 } 424} {} 425 426ifcapable subquery { 427 do_test vtab6-9.1.1 { 428 execsql { 429 SELECT * FROM t12 NATURAL LEFT JOIN t13 430 EXCEPT 431 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0); 432 } 433 } {} 434} 435ifcapable view { 436 do_test vtab6-9.2 { 437 execsql { 438 CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0; 439 SELECT * FROM t12 NATURAL LEFT JOIN t13 440 EXCEPT 441 SELECT * FROM t12 NATURAL LEFT JOIN v13; 442 } 443 } {} 444} ;# ifcapable view 445} ;# ifcapable compound 446 447ifcapable subquery { 448do_test vtab6-10.1 { 449 execsql { 450 CREATE INDEX i22 ON real_t22(q); 451 SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q= 452 (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1); 453 } 454} {} 455} ;# ifcapable subquery 456 457do_test vtab6-11.1.0 { 458 execsql { 459 CREATE TABLE ab_r(a, b); 460 CREATE TABLE bc_r(b, c); 461 462 CREATE VIRTUAL TABLE ab USING echo(ab_r); 463 CREATE VIRTUAL TABLE bc USING echo(bc_r); 464 465 INSERT INTO ab VALUES(1, 2); 466 INSERT INTO bc VALUES(2, 3); 467 } 468} {} 469 470do_test vtab6-11.1.1 { 471 execsql { 472 SELECT a, b, c FROM ab NATURAL JOIN bc; 473 } 474} {1 2 3} 475do_test vtab6-11.1.2 { 476 execsql { 477 SELECT a, b, c FROM bc NATURAL JOIN ab; 478 } 479} {1 2 3} 480 481set ::echo_module_cost 1.0 482 483do_test vtab6-11.1.3 { 484 execsql { 485 SELECT a, b, c FROM ab NATURAL JOIN bc; 486 } 487} {1 2 3} 488do_test vtab6-11.1.4 { 489 execsql { 490 SELECT a, b, c FROM bc NATURAL JOIN ab; 491 } 492} {1 2 3} 493 494 495do_test vtab6-11.2.0 { 496 execsql { 497 CREATE INDEX ab_i ON ab_r(b); 498 } 499} {} 500 501unset ::echo_module_cost 502 503do_test vtab6-11.2.1 { 504 execsql { 505 SELECT a, b, c FROM ab NATURAL JOIN bc; 506 } 507} {1 2 3} 508do_test vtab6-11.2.2 { 509 execsql { 510 SELECT a, b, c FROM bc NATURAL JOIN ab; 511 } 512} {1 2 3} 513 514set ::echo_module_cost 1.0 515 516do_test vtab6-11.2.3 { 517 execsql { 518 SELECT a, b, c FROM ab NATURAL JOIN bc; 519 } 520} {1 2 3} 521do_test vtab6-11.2.4 { 522 execsql { 523 SELECT a, b, c FROM bc NATURAL JOIN ab; 524 } 525} {1 2 3} 526 527unset ::echo_module_cost 528db close 529sqlite3 db test.db 530register_echo_module [sqlite3_connection_pointer db] 531 532do_test vtab6-11.3.1 { 533 execsql { 534 SELECT a, b, c FROM ab NATURAL JOIN bc; 535 } 536} {1 2 3} 537 538do_test vtab6-11.3.2 { 539 execsql { 540 SELECT a, b, c FROM bc NATURAL JOIN ab; 541 } 542} {1 2 3} 543 544set ::echo_module_cost 1.0 545 546do_test vtab6-11.3.3 { 547 execsql { 548 SELECT a, b, c FROM ab NATURAL JOIN bc; 549 } 550} {1 2 3} 551do_test vtab6-11.3.4 { 552 execsql { 553 SELECT a, b, c FROM bc NATURAL JOIN ab; 554 } 555} {1 2 3} 556 557unset ::echo_module_cost 558 559set ::echo_module_ignore_usable 1 560db cache flush 561 562do_test vtab6-11.4.1 { 563 catchsql { 564 SELECT a, b, c FROM ab NATURAL JOIN bc; 565 } 566} {1 {table ab: xBestIndex returned an invalid plan}} 567do_test vtab6-11.4.2 { 568 catchsql { 569 SELECT a, b, c FROM bc NATURAL JOIN ab; 570 } 571} {1 {table ab: xBestIndex returned an invalid plan}} 572 573unset ::echo_module_ignore_usable 574 575finish_test 576