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.1 2006/06/21 07:02:34 danielk1977 Exp $ 18 19set testdir [file dirname $argv0] 20source $testdir/tester.tcl 21 22register_echo_module [sqlite3_connection_pointer db] 23 24execsql { 25 CREATE TABLE real_t1(a,b,c); 26 CREATE TABLE real_t2(b,c,d); 27 CREATE TABLE real_t3(c,d,e); 28 CREATE TABLE real_t4(d,e,f); 29 CREATE TABLE real_t5(a INTEGER PRIMARY KEY); 30 CREATE TABLE real_t6(a INTEGER); 31 CREATE TABLE real_t7 (x, y); 32 CREATE TABLE real_t8 (a integer primary key, b); 33 CREATE TABLE real_t9(a INTEGER PRIMARY KEY, b); 34 CREATE TABLE real_t10(x INTEGER PRIMARY KEY, y); 35 CREATE TABLE real_t11(p INTEGER PRIMARY KEY, q); 36 CREATE TABLE real_t12(a,b); 37 CREATE TABLE real_t13(b,c); 38 CREATE TABLE real_t21(a,b,c); 39 CREATE TABLE real_t22(p,q); 40} 41foreach t [list t1 t2 t3 t4 t5 t6 t7 t8 t9 t10 t11 t12 t13 t21 t22] { 42 execsql "CREATE VIRTUAL TABLE $t USING echo(real_$t)" 43} 44 45do_test vtab6-1.1 { 46 execsql { 47 INSERT INTO t1 VALUES(1,2,3); 48 INSERT INTO t1 VALUES(2,3,4); 49 INSERT INTO t1 VALUES(3,4,5); 50 SELECT * FROM t1; 51 } 52} {1 2 3 2 3 4 3 4 5} 53do_test vtab6-1.2 { 54 execsql { 55 INSERT INTO t2 VALUES(1,2,3); 56 INSERT INTO t2 VALUES(2,3,4); 57 INSERT INTO t2 VALUES(3,4,5); 58 SELECT * FROM t2; 59 } 60} {1 2 3 2 3 4 3 4 5} 61 62do_test vtab6-1.3 { 63 execsql2 { 64 SELECT * FROM t1 NATURAL JOIN t2; 65 } 66} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 67do_test vtab6-1.3.1 { 68 execsql2 { 69 SELECT * FROM t2 NATURAL JOIN t1; 70 } 71} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} 72do_test vtab6-1.3.2 { 73 execsql2 { 74 SELECT * FROM t2 AS x 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.3 { 78 execsql2 { 79 SELECT * FROM t2 NATURAL JOIN t1 AS y; 80 } 81} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2} 82do_test vtab6-1.3.4 { 83 execsql { 84 SELECT b FROM t1 NATURAL JOIN t2; 85 } 86} {2 3} 87do_test vtab6-1.4.1 { 88 execsql2 { 89 SELECT * FROM t1 INNER JOIN t2 USING(b,c); 90 } 91} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 92do_test vtab6-1.4.2 { 93 execsql2 { 94 SELECT * FROM t1 AS x 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.3 { 98 execsql2 { 99 SELECT * FROM t1 INNER JOIN t2 AS y 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.4 { 103 execsql2 { 104 SELECT * FROM t1 AS x 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.5 { 108 execsql { 109 SELECT b FROM t1 JOIN t2 USING(b); 110 } 111} {2 3} 112do_test vtab6-1.5 { 113 execsql2 { 114 SELECT * FROM t1 INNER JOIN t2 USING(b); 115 } 116} {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5} 117do_test vtab6-1.6 { 118 execsql2 { 119 SELECT * FROM t1 INNER JOIN t2 USING(c); 120 } 121} {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5} 122do_test vtab6-1.7 { 123 execsql2 { 124 SELECT * FROM t1 INNER JOIN t2 USING(c,b); 125 } 126} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5} 127 128do_test vtab6-1.8 { 129 execsql { 130 SELECT * FROM t1 NATURAL CROSS JOIN t2; 131 } 132} {1 2 3 4 2 3 4 5} 133do_test vtab6-1.9 { 134 execsql { 135 SELECT * FROM t1 CROSS JOIN t2 USING(b,c); 136 } 137} {1 2 3 4 2 3 4 5} 138do_test vtab6-1.10 { 139 execsql { 140 SELECT * FROM t1 NATURAL INNER JOIN t2; 141 } 142} {1 2 3 4 2 3 4 5} 143do_test vtab6-1.11 { 144 execsql { 145 SELECT * FROM t1 INNER JOIN t2 USING(b,c); 146 } 147} {1 2 3 4 2 3 4 5} 148do_test vtab6-1.12 { 149 execsql { 150 SELECT * FROM t1 natural inner join t2; 151 } 152} {1 2 3 4 2 3 4 5} 153 154ifcapable subquery { 155breakpoint 156 do_test vtab6-1.13 { 157 execsql2 { 158 SELECT * FROM t1 NATURAL JOIN 159 (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3 160 } 161 } {a 1 b 2 c 3 d 4 e 5} 162 do_test vtab6-1.14 { 163 execsql2 { 164 SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx' 165 NATURAL JOIN t1 166 } 167 } {c 3 d 4 e 5 a 1 b 2} 168} 169 170do_test vtab6-1.15 { 171 execsql { 172 INSERT INTO t3 VALUES(2,3,4); 173 INSERT INTO t3 VALUES(3,4,5); 174 INSERT INTO t3 VALUES(4,5,6); 175 SELECT * FROM t3; 176 } 177} {2 3 4 3 4 5 4 5 6} 178do_test vtab6-1.16 { 179 execsql { 180 SELECT * FROM t1 natural join t2 natural join t3; 181 } 182} {1 2 3 4 5 2 3 4 5 6} 183do_test vtab6-1.17 { 184 execsql2 { 185 SELECT * FROM t1 natural join t2 natural join t3; 186 } 187} {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6} 188do_test vtab6-1.18 { 189 execsql { 190 INSERT INTO t4 VALUES(2,3,4); 191 INSERT INTO t4 VALUES(3,4,5); 192 INSERT INTO t4 VALUES(4,5,6); 193 SELECT * FROM t4; 194 } 195} {2 3 4 3 4 5 4 5 6} 196do_test vtab6-1.19.1 { 197 execsql { 198 SELECT * FROM t1 natural join t2 natural join t4; 199 } 200} {1 2 3 4 5 6} 201do_test vtab6-1.19.2 { 202 execsql2 { 203 SELECT * FROM t1 natural join t2 natural join t4; 204 } 205} {a 1 b 2 c 3 d 4 e 5 f 6} 206do_test vtab6-1.20 { 207 execsql { 208 SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1 209 } 210} {1 2 3 4 5} 211 212do_test vtab6-2.1 { 213 execsql { 214 SELECT * FROM t1 NATURAL LEFT JOIN t2; 215 } 216} {1 2 3 4 2 3 4 5 3 4 5 {}} 217do_test vtab6-2.2 { 218 execsql { 219 SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1; 220 } 221} {1 2 3 {} 2 3 4 1 3 4 5 2} 222do_test vtab6-2.3 { 223 catchsql { 224 SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2; 225 } 226} {1 {RIGHT and FULL OUTER JOINs are not currently supported}} 227do_test vtab6-2.4 { 228 execsql { 229 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d 230 } 231} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3} 232do_test vtab6-2.5 { 233 execsql { 234 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1 235 } 236} {2 3 4 {} {} {} 3 4 5 1 2 3} 237do_test vtab6-2.6 { 238 execsql { 239 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1 240 } 241} {1 2 3 {} {} {} 2 3 4 {} {} {}} 242 243do_test vtab6-3.1 { 244 catchsql { 245 SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b; 246 } 247} {1 {a NATURAL join may not have an ON or USING clause}} 248do_test vtab6-3.2 { 249 catchsql { 250 SELECT * FROM t1 NATURAL JOIN t2 USING(b); 251 } 252} {1 {a NATURAL join may not have an ON or USING clause}} 253do_test vtab6-3.3 { 254 catchsql { 255 SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b); 256 } 257} {1 {cannot have both ON and USING clauses in the same join}} 258do_test vtab6-3.4 { 259 catchsql { 260 SELECT * FROM t1 JOIN t2 USING(a); 261 } 262} {1 {cannot join using column a - column not present in both tables}} 263do_test vtab6-3.5 { 264 catchsql { 265 SELECT * FROM t1 USING(a); 266 } 267} {0 {1 2 3 2 3 4 3 4 5}} 268do_test vtab6-3.6 { 269 catchsql { 270 SELECT * FROM t1 JOIN t2 ON t3.a=t2.b; 271 } 272} {1 {no such column: t3.a}} 273do_test vtab6-3.7 { 274 catchsql { 275 SELECT * FROM t1 INNER OUTER JOIN t2; 276 } 277} {1 {unknown or unsupported join type: INNER OUTER}} 278do_test vtab6-3.7 { 279 catchsql { 280 SELECT * FROM t1 LEFT BOGUS JOIN t2; 281 } 282} {1 {unknown or unsupported join type: LEFT BOGUS}} 283 284do_test vtab6-4.1 { 285 execsql { 286 BEGIN; 287 INSERT INTO t6 VALUES(NULL); 288 INSERT INTO t6 VALUES(NULL); 289 INSERT INTO t6 SELECT * FROM t6; 290 INSERT INTO t6 SELECT * FROM t6; 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 COMMIT; 296 } 297 execsql { 298 SELECT * FROM t6 NATURAL JOIN t5; 299 } 300} {} 301do_test vtab6-4.2 { 302 execsql { 303 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 304 } 305} {} 306do_test vtab6-4.3 { 307 execsql { 308 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 309 } 310} {} 311do_test vtab6-4.4 { 312 execsql { 313 UPDATE t6 SET a='xyz'; 314 SELECT * FROM t6 NATURAL JOIN t5; 315 } 316} {} 317do_test vtab6-4.6 { 318 execsql { 319 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 320 } 321} {} 322do_test vtab6-4.7 { 323 execsql { 324 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 325 } 326} {} 327do_test vtab6-4.8 { 328 execsql { 329 UPDATE t6 SET a=1; 330 SELECT * FROM t6 NATURAL JOIN t5; 331 } 332} {} 333do_test vtab6-4.9 { 334 execsql { 335 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 336 } 337} {} 338do_test vtab6-4.10 { 339 execsql { 340 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 341 } 342} {} 343 344# A test for ticket #247. 345# 346do_test vtab6-7.1 { 347 execsql { 348 INSERT INTO t7 VALUES ("pa1", 1); 349 INSERT INTO t7 VALUES ("pa2", NULL); 350 INSERT INTO t7 VALUES ("pa3", NULL); 351 INSERT INTO t7 VALUES ("pa4", 2); 352 INSERT INTO t7 VALUES ("pa30", 131); 353 INSERT INTO t7 VALUES ("pa31", 130); 354 INSERT INTO t7 VALUES ("pa28", NULL); 355 356 INSERT INTO t8 VALUES (1, "pa1"); 357 INSERT INTO t8 VALUES (2, "pa4"); 358 INSERT INTO t8 VALUES (3, NULL); 359 INSERT INTO t8 VALUES (4, NULL); 360 INSERT INTO t8 VALUES (130, "pa31"); 361 INSERT INTO t8 VALUES (131, "pa30"); 362 363 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a; 364 } 365} {1 999 999 2 131 130 999} 366 367# Make sure a left join where the right table is really a view that 368# is itself a join works right. Ticket #306. 369# 370ifcapable view { 371do_test vtab6-8.1 { 372 execsql { 373 BEGIN; 374 INSERT INTO t9 VALUES(1,11); 375 INSERT INTO t9 VALUES(2,22); 376 INSERT INTO t10 VALUES(1,2); 377 INSERT INTO t10 VALUES(3,3); 378 INSERT INTO t11 VALUES(2,111); 379 INSERT INTO t11 VALUES(3,333); 380 CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p; 381 COMMIT; 382 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x ); 383 } 384} {1 11 1 111 2 22 {} {}} 385ifcapable subquery { 386 do_test vtab6-8.2 { 387 execsql { 388 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p) 389 ON( a=x); 390 } 391 } {1 11 1 111 2 22 {} {}} 392} 393do_test vtab6-8.3 { 394 execsql { 395 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x ); 396 } 397} {1 111 1 11 3 333 {} {}} 398} ;# ifcapable view 399 400# Ticket #350 describes a scenario where LEFT OUTER JOIN does not 401# function correctly if the right table in the join is really 402# subquery. 403# 404# To test the problem, we generate the same LEFT OUTER JOIN in two 405# separate selects but with on using a subquery and the other calling 406# the table directly. Then connect the two SELECTs using an EXCEPT. 407# Both queries should generate the same results so the answer should 408# be an empty set. 409# 410ifcapable compound { 411do_test vtab6-9.1 { 412 execsql { 413 BEGIN; 414 INSERT INTO t12 VALUES(1,11); 415 INSERT INTO t12 VALUES(2,22); 416 INSERT INTO t13 VALUES(22,222); 417 COMMIT; 418 } 419} {} 420 421ifcapable subquery { 422 do_test vtab6-9.1.1 { 423 execsql { 424 SELECT * FROM t12 NATURAL LEFT JOIN t13 425 EXCEPT 426 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0); 427 } 428 } {} 429} 430ifcapable view { 431 do_test vtab6-9.2 { 432 execsql { 433 CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0; 434 SELECT * FROM t12 NATURAL LEFT JOIN t13 435 EXCEPT 436 SELECT * FROM t12 NATURAL LEFT JOIN v13; 437 } 438 } {} 439} ;# ifcapable view 440} ;# ifcapable compound 441 442ifcapable subquery { 443do_test vtab6-10.1 { 444 execsql { 445 CREATE INDEX i22 ON real_t22(q); 446 SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q= 447 (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1); 448 } 449} {} 450} ;# ifcapable subquery 451 452finish_test 453