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. 14# 15# $Id: join.test,v 1.11 2003/09/27 13:39:40 drh Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20do_test join-1.1 { 21 execsql { 22 CREATE TABLE t1(a,b,c); 23 INSERT INTO t1 VALUES(1,2,3); 24 INSERT INTO t1 VALUES(2,3,4); 25 INSERT INTO t1 VALUES(3,4,5); 26 SELECT * FROM t1; 27 } 28} {1 2 3 2 3 4 3 4 5} 29do_test join-1.2 { 30 execsql { 31 CREATE TABLE t2(b,c,d); 32 INSERT INTO t2 VALUES(1,2,3); 33 INSERT INTO t2 VALUES(2,3,4); 34 INSERT INTO t2 VALUES(3,4,5); 35 SELECT * FROM t2; 36 } 37} {1 2 3 2 3 4 3 4 5} 38 39do_test join-1.3 { 40 execsql2 { 41 SELECT * FROM t1 NATURAL JOIN t2; 42 } 43} {t1.a 1 t1.b 2 t1.c 3 t2.d 4 t1.a 2 t1.b 3 t1.c 4 t2.d 5} 44do_test join-1.3.1 { 45 execsql2 { 46 SELECT * FROM t2 NATURAL JOIN t1; 47 } 48} {t2.b 2 t2.c 3 t2.d 4 t1.a 1 t2.b 3 t2.c 4 t2.d 5 t1.a 2} 49do_test join-1.4 { 50 execsql2 { 51 SELECT * FROM t1 INNER JOIN t2 USING(b,c); 52 } 53} {t1.a 1 t1.b 2 t1.c 3 t2.d 4 t1.a 2 t1.b 3 t1.c 4 t2.d 5} 54do_test join-1.5 { 55 execsql2 { 56 SELECT * FROM t1 INNER JOIN t2 USING(b); 57 } 58} {t1.a 1 t1.b 2 t1.c 3 t2.c 3 t2.d 4 t1.a 2 t1.b 3 t1.c 4 t2.c 4 t2.d 5} 59do_test join-1.6 { 60 execsql2 { 61 SELECT * FROM t1 INNER JOIN t2 USING(c); 62 } 63} {t1.a 1 t1.b 2 t1.c 3 t2.b 2 t2.d 4 t1.a 2 t1.b 3 t1.c 4 t2.b 3 t2.d 5} 64do_test join-1.7 { 65 execsql2 { 66 SELECT * FROM t1 INNER JOIN t2 USING(c,b); 67 } 68} {t1.a 1 t1.b 2 t1.c 3 t2.d 4 t1.a 2 t1.b 3 t1.c 4 t2.d 5} 69 70do_test join-1.8 { 71 execsql { 72 SELECT * FROM t1 NATURAL CROSS JOIN t2; 73 } 74} {1 2 3 4 2 3 4 5} 75do_test join-1.9 { 76 execsql { 77 SELECT * FROM t1 CROSS JOIN t2 USING(b,c); 78 } 79} {1 2 3 4 2 3 4 5} 80do_test join-1.10 { 81 execsql { 82 SELECT * FROM t1 NATURAL INNER JOIN t2; 83 } 84} {1 2 3 4 2 3 4 5} 85do_test join-1.11 { 86 execsql { 87 SELECT * FROM t1 INNER JOIN t2 USING(b,c); 88 } 89} {1 2 3 4 2 3 4 5} 90do_test join-1.12 { 91 execsql { 92 SELECT * FROM t1 natural inner join t2; 93 } 94} {1 2 3 4 2 3 4 5} 95do_test join-1.13 { 96 execsql2 { 97 SELECT * FROM t1 NATURAL JOIN 98 (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3 99 } 100} {t1.a 1 t1.b 2 t1.c 3 t3.d 4 t3.e 5} 101do_test join-1.14 { 102 execsql2 { 103 SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx' 104 NATURAL JOIN t1 105 } 106} {tx.c 3 tx.d 4 tx.e 5 t1.a 1 t1.b 2} 107 108do_test join-1.15 { 109 execsql { 110 CREATE TABLE t3(c,d,e); 111 INSERT INTO t3 VALUES(2,3,4); 112 INSERT INTO t3 VALUES(3,4,5); 113 INSERT INTO t3 VALUES(4,5,6); 114 SELECT * FROM t3; 115 } 116} {2 3 4 3 4 5 4 5 6} 117do_test join-1.16 { 118 execsql { 119 SELECT * FROM t1 natural join t2 natural join t3; 120 } 121} {1 2 3 4 5 2 3 4 5 6} 122do_test join-1.17 { 123 execsql2 { 124 SELECT * FROM t1 natural join t2 natural join t3; 125 } 126} {t1.a 1 t1.b 2 t1.c 3 t2.d 4 t3.e 5 t1.a 2 t1.b 3 t1.c 4 t2.d 5 t3.e 6} 127do_test join-1.18 { 128 execsql { 129 CREATE TABLE t4(d,e,f); 130 INSERT INTO t4 VALUES(2,3,4); 131 INSERT INTO t4 VALUES(3,4,5); 132 INSERT INTO t4 VALUES(4,5,6); 133 SELECT * FROM t4; 134 } 135} {2 3 4 3 4 5 4 5 6} 136do_test join-1.19 { 137 execsql { 138 SELECT * FROM t1 natural join t2 natural join t4; 139 } 140} {1 2 3 4 5 6} 141do_test join-1.19 { 142 execsql2 { 143 SELECT * FROM t1 natural join t2 natural join t4; 144 } 145} {t1.a 1 t1.b 2 t1.c 3 t2.d 4 t4.e 5 t4.f 6} 146do_test join-1.20 { 147 execsql { 148 SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1 149 } 150} {1 2 3 4 5} 151 152do_test join-2.1 { 153 execsql { 154 SELECT * FROM t1 NATURAL LEFT JOIN t2; 155 } 156} {1 2 3 4 2 3 4 5 3 4 5 {}} 157do_test join-2.2 { 158 execsql { 159 SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1; 160 } 161} {1 2 3 {} 2 3 4 1 3 4 5 2} 162do_test join-2.3 { 163 catchsql { 164 SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2; 165 } 166} {1 {RIGHT and FULL OUTER JOINs are not currently supported}} 167do_test join-2.4 { 168 execsql { 169 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d 170 } 171} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3} 172do_test join-2.5 { 173 execsql { 174 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1 175 } 176} {2 3 4 {} {} {} 3 4 5 1 2 3} 177do_test join-2.6 { 178 execsql { 179 SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1 180 } 181} {1 2 3 {} {} {} 2 3 4 {} {} {}} 182 183do_test join-3.1 { 184 catchsql { 185 SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b; 186 } 187} {1 {a NATURAL join may not have an ON or USING clause}} 188do_test join-3.2 { 189 catchsql { 190 SELECT * FROM t1 NATURAL JOIN t2 USING(b); 191 } 192} {1 {a NATURAL join may not have an ON or USING clause}} 193do_test join-3.3 { 194 catchsql { 195 SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b); 196 } 197} {1 {cannot have both ON and USING clauses in the same join}} 198do_test join-3.4 { 199 catchsql { 200 SELECT * FROM t1 JOIN t2 USING(a); 201 } 202} {1 {cannot join using column a - column not present in both tables}} 203do_test join-3.5 { 204 catchsql { 205 SELECT * FROM t1 USING(a); 206 } 207} {0 {1 2 3 2 3 4 3 4 5}} 208do_test join-3.6 { 209 catchsql { 210 SELECT * FROM t1 JOIN t2 ON t3.a=t2.b; 211 } 212} {1 {no such column: t3.a}} 213do_test join-3.7 { 214 catchsql { 215 SELECT * FROM t1 INNER OUTER JOIN t2; 216 } 217} {1 {unknown or unsupported join type: INNER OUTER}} 218do_test join-3.7 { 219 catchsql { 220 SELECT * FROM t1 LEFT BOGUS JOIN t2; 221 } 222} {1 {unknown or unsupported join type: LEFT BOGUS}} 223 224do_test join-4.1 { 225 execsql { 226 BEGIN; 227 CREATE TABLE t5(a INTEGER PRIMARY KEY); 228 CREATE TABLE t6(a INTEGER); 229 INSERT INTO t6 VALUES(NULL); 230 INSERT INTO t6 VALUES(NULL); 231 INSERT INTO t6 SELECT * FROM t6; 232 INSERT INTO t6 SELECT * FROM t6; 233 INSERT INTO t6 SELECT * FROM t6; 234 INSERT INTO t6 SELECT * FROM t6; 235 INSERT INTO t6 SELECT * FROM t6; 236 INSERT INTO t6 SELECT * FROM t6; 237 COMMIT; 238 } 239 execsql { 240 SELECT * FROM t6 NATURAL JOIN t5; 241 } 242} {} 243do_test join-4.2 { 244 execsql { 245 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 246 } 247} {} 248do_test join-4.3 { 249 execsql { 250 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 251 } 252} {} 253do_test join-4.4 { 254 execsql { 255 UPDATE t6 SET a='xyz'; 256 SELECT * FROM t6 NATURAL JOIN t5; 257 } 258} {} 259do_test join-4.6 { 260 execsql { 261 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 262 } 263} {} 264do_test join-4.7 { 265 execsql { 266 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 267 } 268} {} 269do_test join-4.8 { 270 execsql { 271 UPDATE t6 SET a=1; 272 SELECT * FROM t6 NATURAL JOIN t5; 273 } 274} {} 275do_test join-4.9 { 276 execsql { 277 SELECT * FROM t6, t5 WHERE t6.a<t5.a; 278 } 279} {} 280do_test join-4.10 { 281 execsql { 282 SELECT * FROM t6, t5 WHERE t6.a>t5.a; 283 } 284} {} 285 286do_test join-5.1 { 287 execsql { 288 BEGIN; 289 create table centros (id integer primary key, centro); 290 INSERT INTO centros VALUES(1,'xxx'); 291 create table usuarios (id integer primary key, nombre, apellidos, 292 idcentro integer); 293 INSERT INTO usuarios VALUES(1,'a','aa',1); 294 INSERT INTO usuarios VALUES(2,'b','bb',1); 295 INSERT INTO usuarios VALUES(3,'c','cc',NULL); 296 create index idcentro on usuarios (idcentro); 297 END; 298 select usuarios.id, usuarios.nombre, centros.centro from 299 usuarios left outer join centros on usuarios.idcentro = centros.id; 300 } 301} {1 a xxx 2 b xxx 3 c {}} 302 303# A test for ticket #247. 304# 305do_test join-7.1 { 306 execsql { 307 CREATE TABLE t7 (x, y); 308 INSERT INTO t7 VALUES ("pa1", 1); 309 INSERT INTO t7 VALUES ("pa2", NULL); 310 INSERT INTO t7 VALUES ("pa3", NULL); 311 INSERT INTO t7 VALUES ("pa4", 2); 312 INSERT INTO t7 VALUES ("pa30", 131); 313 INSERT INTO t7 VALUES ("pa31", 130); 314 INSERT INTO t7 VALUES ("pa28", NULL); 315 316 CREATE TABLE t8 (a integer primary key, b); 317 INSERT INTO t8 VALUES (1, "pa1"); 318 INSERT INTO t8 VALUES (2, "pa4"); 319 INSERT INTO t8 VALUES (3, NULL); 320 INSERT INTO t8 VALUES (4, NULL); 321 INSERT INTO t8 VALUES (130, "pa31"); 322 INSERT INTO t8 VALUES (131, "pa30"); 323 324 SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a; 325 } 326} {1 999 999 2 131 130 999} 327 328# Make sure a left join where the right table is really a view that 329# is itself a join works right. Ticket #306. 330# 331do_test join-8.1 { 332 execsql { 333 BEGIN; 334 CREATE TABLE t9(a INTEGER PRIMARY KEY, b); 335 INSERT INTO t9 VALUES(1,11); 336 INSERT INTO t9 VALUES(2,22); 337 CREATE TABLE t10(x INTEGER PRIMARY KEY, y); 338 INSERT INTO t10 VALUES(1,2); 339 INSERT INTO t10 VALUES(3,3); 340 CREATE TABLE t11(p INTEGER PRIMARY KEY, q); 341 INSERT INTO t11 VALUES(2,111); 342 INSERT INTO t11 VALUES(3,333); 343 CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p; 344 COMMIT; 345 SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x ); 346 } 347} {1 11 1 111 2 22 {} {}} 348do_test join-8.2 { 349 execsql { 350 SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p) 351 ON( a=x); 352 } 353} {1 11 1 111 2 22 {} {}} 354do_test join-8.3 { 355 execsql { 356 SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x ); 357 } 358} {1 111 1 11 3 333 {} {}} 359 360# Ticket #350 describes a scenario where LEFT OUTER JOIN does not 361# function correctly if the right table in the join is really 362# subquery. 363# 364# To test the problem, we generate the same LEFT OUTER JOIN in two 365# separate selects but with on using a subquery and the other calling 366# the table directly. Then connect the two SELECTs using an EXCEPT. 367# Both queries should generate the same results so the answer should 368# be an empty set. 369# 370do_test join-9.1 { 371 execsql { 372 BEGIN; 373 CREATE TABLE t12(a,b); 374 INSERT INTO t12 VALUES(1,11); 375 INSERT INTO t12 VALUES(2,22); 376 CREATE TABLE t13(b,c); 377 INSERT INTO t13 VALUES(22,222); 378 COMMIT; 379 SELECT * FROM t12 NATURAL LEFT JOIN t13 380 EXCEPT 381 SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0); 382 } 383} {} 384do_test join-9.2 { 385 execsql { 386 CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0; 387 SELECT * FROM t12 NATURAL LEFT JOIN t13 388 EXCEPT 389 SELECT * FROM t12 NATURAL LEFT JOIN v13; 390 } 391} {} 392 393finish_test 394