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