Annotation of embedaddon/sqlite3/test/join.test, revision 1.1
1.1 ! misho 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.27 2009/07/01 16:12:08 danielk1977 Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: do_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}
! 29: do_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:
! 39: do_test join-1.3 {
! 40: execsql2 {
! 41: SELECT * FROM t1 NATURAL JOIN t2;
! 42: }
! 43: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
! 44: do_test join-1.3.1 {
! 45: execsql2 {
! 46: SELECT * FROM t2 NATURAL JOIN t1;
! 47: }
! 48: } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
! 49: do_test join-1.3.2 {
! 50: execsql2 {
! 51: SELECT * FROM t2 AS x NATURAL JOIN t1;
! 52: }
! 53: } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
! 54: do_test join-1.3.3 {
! 55: execsql2 {
! 56: SELECT * FROM t2 NATURAL JOIN t1 AS y;
! 57: }
! 58: } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
! 59: do_test join-1.3.4 {
! 60: execsql {
! 61: SELECT b FROM t1 NATURAL JOIN t2;
! 62: }
! 63: } {2 3}
! 64:
! 65: # ticket #3522
! 66: do_test join-1.3.5 {
! 67: execsql2 {
! 68: SELECT t2.* FROM t2 NATURAL JOIN t1
! 69: }
! 70: } {b 2 c 3 d 4 b 3 c 4 d 5}
! 71: do_test join-1.3.6 {
! 72: execsql2 {
! 73: SELECT xyzzy.* FROM t2 AS xyzzy NATURAL JOIN t1
! 74: }
! 75: } {b 2 c 3 d 4 b 3 c 4 d 5}
! 76: do_test join-1.3.7 {
! 77: execsql2 {
! 78: SELECT t1.* FROM t2 NATURAL JOIN t1
! 79: }
! 80: } {a 1 b 2 c 3 a 2 b 3 c 4}
! 81: do_test join-1.3.8 {
! 82: execsql2 {
! 83: SELECT xyzzy.* FROM t2 NATURAL JOIN t1 AS xyzzy
! 84: }
! 85: } {a 1 b 2 c 3 a 2 b 3 c 4}
! 86: do_test join-1.3.9 {
! 87: execsql2 {
! 88: SELECT aaa.*, bbb.* FROM t2 AS aaa NATURAL JOIN t1 AS bbb
! 89: }
! 90: } {b 2 c 3 d 4 a 1 b 2 c 3 b 3 c 4 d 5 a 2 b 3 c 4}
! 91: do_test join-1.3.10 {
! 92: execsql2 {
! 93: SELECT t1.*, t2.* FROM t2 NATURAL JOIN t1
! 94: }
! 95: } {a 1 b 2 c 3 b 2 c 3 d 4 a 2 b 3 c 4 b 3 c 4 d 5}
! 96:
! 97:
! 98: do_test join-1.4.1 {
! 99: execsql2 {
! 100: SELECT * FROM t1 INNER JOIN t2 USING(b,c);
! 101: }
! 102: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
! 103: do_test join-1.4.2 {
! 104: execsql2 {
! 105: SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c);
! 106: }
! 107: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
! 108: do_test join-1.4.3 {
! 109: execsql2 {
! 110: SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c);
! 111: }
! 112: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
! 113: do_test join-1.4.4 {
! 114: execsql2 {
! 115: SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c);
! 116: }
! 117: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
! 118: do_test join-1.4.5 {
! 119: execsql {
! 120: SELECT b FROM t1 JOIN t2 USING(b);
! 121: }
! 122: } {2 3}
! 123:
! 124: # Ticket #3522
! 125: do_test join-1.4.6 {
! 126: execsql2 {
! 127: SELECT t1.* FROM t1 JOIN t2 USING(b);
! 128: }
! 129: } {a 1 b 2 c 3 a 2 b 3 c 4}
! 130: do_test join-1.4.7 {
! 131: execsql2 {
! 132: SELECT t2.* FROM t1 JOIN t2 USING(b);
! 133: }
! 134: } {b 2 c 3 d 4 b 3 c 4 d 5}
! 135:
! 136: do_test join-1.5 {
! 137: execsql2 {
! 138: SELECT * FROM t1 INNER JOIN t2 USING(b);
! 139: }
! 140: } {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5}
! 141: do_test join-1.6 {
! 142: execsql2 {
! 143: SELECT * FROM t1 INNER JOIN t2 USING(c);
! 144: }
! 145: } {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5}
! 146: do_test join-1.7 {
! 147: execsql2 {
! 148: SELECT * FROM t1 INNER JOIN t2 USING(c,b);
! 149: }
! 150: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
! 151:
! 152: do_test join-1.8 {
! 153: execsql {
! 154: SELECT * FROM t1 NATURAL CROSS JOIN t2;
! 155: }
! 156: } {1 2 3 4 2 3 4 5}
! 157: do_test join-1.9 {
! 158: execsql {
! 159: SELECT * FROM t1 CROSS JOIN t2 USING(b,c);
! 160: }
! 161: } {1 2 3 4 2 3 4 5}
! 162: do_test join-1.10 {
! 163: execsql {
! 164: SELECT * FROM t1 NATURAL INNER JOIN t2;
! 165: }
! 166: } {1 2 3 4 2 3 4 5}
! 167: do_test join-1.11 {
! 168: execsql {
! 169: SELECT * FROM t1 INNER JOIN t2 USING(b,c);
! 170: }
! 171: } {1 2 3 4 2 3 4 5}
! 172: do_test join-1.12 {
! 173: execsql {
! 174: SELECT * FROM t1 natural inner join t2;
! 175: }
! 176: } {1 2 3 4 2 3 4 5}
! 177:
! 178: ifcapable subquery {
! 179: do_test join-1.13 {
! 180: execsql2 {
! 181: SELECT * FROM t1 NATURAL JOIN
! 182: (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3
! 183: }
! 184: } {a 1 b 2 c 3 d 4 e 5}
! 185: do_test join-1.14 {
! 186: execsql2 {
! 187: SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx'
! 188: NATURAL JOIN t1
! 189: }
! 190: } {c 3 d 4 e 5 a 1 b 2}
! 191: }
! 192:
! 193: do_test join-1.15 {
! 194: execsql {
! 195: CREATE TABLE t3(c,d,e);
! 196: INSERT INTO t3 VALUES(2,3,4);
! 197: INSERT INTO t3 VALUES(3,4,5);
! 198: INSERT INTO t3 VALUES(4,5,6);
! 199: SELECT * FROM t3;
! 200: }
! 201: } {2 3 4 3 4 5 4 5 6}
! 202: do_test join-1.16 {
! 203: execsql {
! 204: SELECT * FROM t1 natural join t2 natural join t3;
! 205: }
! 206: } {1 2 3 4 5 2 3 4 5 6}
! 207: do_test join-1.17 {
! 208: execsql2 {
! 209: SELECT * FROM t1 natural join t2 natural join t3;
! 210: }
! 211: } {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6}
! 212: do_test join-1.18 {
! 213: execsql {
! 214: CREATE TABLE t4(d,e,f);
! 215: INSERT INTO t4 VALUES(2,3,4);
! 216: INSERT INTO t4 VALUES(3,4,5);
! 217: INSERT INTO t4 VALUES(4,5,6);
! 218: SELECT * FROM t4;
! 219: }
! 220: } {2 3 4 3 4 5 4 5 6}
! 221: do_test join-1.19.1 {
! 222: execsql {
! 223: SELECT * FROM t1 natural join t2 natural join t4;
! 224: }
! 225: } {1 2 3 4 5 6}
! 226: do_test join-1.19.2 {
! 227: execsql2 {
! 228: SELECT * FROM t1 natural join t2 natural join t4;
! 229: }
! 230: } {a 1 b 2 c 3 d 4 e 5 f 6}
! 231: do_test join-1.20 {
! 232: execsql {
! 233: SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1
! 234: }
! 235: } {1 2 3 4 5}
! 236:
! 237: do_test join-2.1 {
! 238: execsql {
! 239: SELECT * FROM t1 NATURAL LEFT JOIN t2;
! 240: }
! 241: } {1 2 3 4 2 3 4 5 3 4 5 {}}
! 242:
! 243: # ticket #3522
! 244: do_test join-2.1.1 {
! 245: execsql2 {
! 246: SELECT * FROM t1 NATURAL LEFT JOIN t2;
! 247: }
! 248: } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5 a 3 b 4 c 5 d {}}
! 249: do_test join-2.1.2 {
! 250: execsql2 {
! 251: SELECT t1.* FROM t1 NATURAL LEFT JOIN t2;
! 252: }
! 253: } {a 1 b 2 c 3 a 2 b 3 c 4 a 3 b 4 c 5}
! 254: do_test join-2.1.3 {
! 255: execsql2 {
! 256: SELECT t2.* FROM t1 NATURAL LEFT JOIN t2;
! 257: }
! 258: } {b 2 c 3 d 4 b 3 c 4 d 5 b {} c {} d {}}
! 259:
! 260: do_test join-2.2 {
! 261: execsql {
! 262: SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
! 263: }
! 264: } {1 2 3 {} 2 3 4 1 3 4 5 2}
! 265: do_test join-2.3 {
! 266: catchsql {
! 267: SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
! 268: }
! 269: } {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
! 270: do_test join-2.4 {
! 271: execsql {
! 272: SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
! 273: }
! 274: } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
! 275: do_test join-2.5 {
! 276: execsql {
! 277: SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
! 278: }
! 279: } {2 3 4 {} {} {} 3 4 5 1 2 3}
! 280: do_test join-2.6 {
! 281: execsql {
! 282: SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1
! 283: }
! 284: } {1 2 3 {} {} {} 2 3 4 {} {} {}}
! 285:
! 286: do_test join-3.1 {
! 287: catchsql {
! 288: SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;
! 289: }
! 290: } {1 {a NATURAL join may not have an ON or USING clause}}
! 291: do_test join-3.2 {
! 292: catchsql {
! 293: SELECT * FROM t1 NATURAL JOIN t2 USING(b);
! 294: }
! 295: } {1 {a NATURAL join may not have an ON or USING clause}}
! 296: do_test join-3.3 {
! 297: catchsql {
! 298: SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b);
! 299: }
! 300: } {1 {cannot have both ON and USING clauses in the same join}}
! 301: do_test join-3.4.1 {
! 302: catchsql {
! 303: SELECT * FROM t1 JOIN t2 USING(a);
! 304: }
! 305: } {1 {cannot join using column a - column not present in both tables}}
! 306: do_test join-3.4.2 {
! 307: catchsql {
! 308: SELECT * FROM t1 JOIN t2 USING(d);
! 309: }
! 310: } {1 {cannot join using column d - column not present in both tables}}
! 311: do_test join-3.5 {
! 312: catchsql { SELECT * FROM t1 USING(a) }
! 313: } {1 {a JOIN clause is required before USING}}
! 314: do_test join-3.6 {
! 315: catchsql {
! 316: SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
! 317: }
! 318: } {1 {no such column: t3.a}}
! 319: do_test join-3.7 {
! 320: catchsql {
! 321: SELECT * FROM t1 INNER OUTER JOIN t2;
! 322: }
! 323: } {1 {unknown or unsupported join type: INNER OUTER}}
! 324: do_test join-3.8 {
! 325: catchsql {
! 326: SELECT * FROM t1 INNER OUTER CROSS JOIN t2;
! 327: }
! 328: } {1 {unknown or unsupported join type: INNER OUTER CROSS}}
! 329: do_test join-3.9 {
! 330: catchsql {
! 331: SELECT * FROM t1 OUTER NATURAL INNER JOIN t2;
! 332: }
! 333: } {1 {unknown or unsupported join type: OUTER NATURAL INNER}}
! 334: do_test join-3.10 {
! 335: catchsql {
! 336: SELECT * FROM t1 LEFT BOGUS JOIN t2;
! 337: }
! 338: } {1 {unknown or unsupported join type: LEFT BOGUS}}
! 339: do_test join-3.11 {
! 340: catchsql {
! 341: SELECT * FROM t1 INNER BOGUS CROSS JOIN t2;
! 342: }
! 343: } {1 {unknown or unsupported join type: INNER BOGUS CROSS}}
! 344: do_test join-3.12 {
! 345: catchsql {
! 346: SELECT * FROM t1 NATURAL AWK SED JOIN t2;
! 347: }
! 348: } {1 {unknown or unsupported join type: NATURAL AWK SED}}
! 349:
! 350: do_test join-4.1 {
! 351: execsql {
! 352: BEGIN;
! 353: CREATE TABLE t5(a INTEGER PRIMARY KEY);
! 354: CREATE TABLE t6(a INTEGER);
! 355: INSERT INTO t6 VALUES(NULL);
! 356: INSERT INTO t6 VALUES(NULL);
! 357: INSERT INTO t6 SELECT * FROM t6;
! 358: INSERT INTO t6 SELECT * FROM t6;
! 359: INSERT INTO t6 SELECT * FROM t6;
! 360: INSERT INTO t6 SELECT * FROM t6;
! 361: INSERT INTO t6 SELECT * FROM t6;
! 362: INSERT INTO t6 SELECT * FROM t6;
! 363: COMMIT;
! 364: }
! 365: execsql {
! 366: SELECT * FROM t6 NATURAL JOIN t5;
! 367: }
! 368: } {}
! 369: do_test join-4.2 {
! 370: execsql {
! 371: SELECT * FROM t6, t5 WHERE t6.a<t5.a;
! 372: }
! 373: } {}
! 374: do_test join-4.3 {
! 375: execsql {
! 376: SELECT * FROM t6, t5 WHERE t6.a>t5.a;
! 377: }
! 378: } {}
! 379: do_test join-4.4 {
! 380: execsql {
! 381: UPDATE t6 SET a='xyz';
! 382: SELECT * FROM t6 NATURAL JOIN t5;
! 383: }
! 384: } {}
! 385: do_test join-4.6 {
! 386: execsql {
! 387: SELECT * FROM t6, t5 WHERE t6.a<t5.a;
! 388: }
! 389: } {}
! 390: do_test join-4.7 {
! 391: execsql {
! 392: SELECT * FROM t6, t5 WHERE t6.a>t5.a;
! 393: }
! 394: } {}
! 395: do_test join-4.8 {
! 396: execsql {
! 397: UPDATE t6 SET a=1;
! 398: SELECT * FROM t6 NATURAL JOIN t5;
! 399: }
! 400: } {}
! 401: do_test join-4.9 {
! 402: execsql {
! 403: SELECT * FROM t6, t5 WHERE t6.a<t5.a;
! 404: }
! 405: } {}
! 406: do_test join-4.10 {
! 407: execsql {
! 408: SELECT * FROM t6, t5 WHERE t6.a>t5.a;
! 409: }
! 410: } {}
! 411:
! 412: do_test join-5.1 {
! 413: execsql {
! 414: BEGIN;
! 415: create table centros (id integer primary key, centro);
! 416: INSERT INTO centros VALUES(1,'xxx');
! 417: create table usuarios (id integer primary key, nombre, apellidos,
! 418: idcentro integer);
! 419: INSERT INTO usuarios VALUES(1,'a','aa',1);
! 420: INSERT INTO usuarios VALUES(2,'b','bb',1);
! 421: INSERT INTO usuarios VALUES(3,'c','cc',NULL);
! 422: create index idcentro on usuarios (idcentro);
! 423: END;
! 424: select usuarios.id, usuarios.nombre, centros.centro from
! 425: usuarios left outer join centros on usuarios.idcentro = centros.id;
! 426: }
! 427: } {1 a xxx 2 b xxx 3 c {}}
! 428:
! 429: # A test for ticket #247.
! 430: #
! 431: do_test join-7.1 {
! 432: execsql {
! 433: CREATE TABLE t7 (x, y);
! 434: INSERT INTO t7 VALUES ("pa1", 1);
! 435: INSERT INTO t7 VALUES ("pa2", NULL);
! 436: INSERT INTO t7 VALUES ("pa3", NULL);
! 437: INSERT INTO t7 VALUES ("pa4", 2);
! 438: INSERT INTO t7 VALUES ("pa30", 131);
! 439: INSERT INTO t7 VALUES ("pa31", 130);
! 440: INSERT INTO t7 VALUES ("pa28", NULL);
! 441:
! 442: CREATE TABLE t8 (a integer primary key, b);
! 443: INSERT INTO t8 VALUES (1, "pa1");
! 444: INSERT INTO t8 VALUES (2, "pa4");
! 445: INSERT INTO t8 VALUES (3, NULL);
! 446: INSERT INTO t8 VALUES (4, NULL);
! 447: INSERT INTO t8 VALUES (130, "pa31");
! 448: INSERT INTO t8 VALUES (131, "pa30");
! 449:
! 450: SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
! 451: }
! 452: } {1 999 999 2 131 130 999}
! 453:
! 454: # Make sure a left join where the right table is really a view that
! 455: # is itself a join works right. Ticket #306.
! 456: #
! 457: ifcapable view {
! 458: do_test join-8.1 {
! 459: execsql {
! 460: BEGIN;
! 461: CREATE TABLE t9(a INTEGER PRIMARY KEY, b);
! 462: INSERT INTO t9 VALUES(1,11);
! 463: INSERT INTO t9 VALUES(2,22);
! 464: CREATE TABLE t10(x INTEGER PRIMARY KEY, y);
! 465: INSERT INTO t10 VALUES(1,2);
! 466: INSERT INTO t10 VALUES(3,3);
! 467: CREATE TABLE t11(p INTEGER PRIMARY KEY, q);
! 468: INSERT INTO t11 VALUES(2,111);
! 469: INSERT INTO t11 VALUES(3,333);
! 470: CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p;
! 471: COMMIT;
! 472: SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );
! 473: }
! 474: } {1 11 1 111 2 22 {} {}}
! 475: ifcapable subquery {
! 476: do_test join-8.2 {
! 477: execsql {
! 478: SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)
! 479: ON( a=x);
! 480: }
! 481: } {1 11 1 111 2 22 {} {}}
! 482: }
! 483: do_test join-8.3 {
! 484: execsql {
! 485: SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );
! 486: }
! 487: } {1 111 1 11 3 333 {} {}}
! 488: ifcapable subquery {
! 489: # Constant expressions in a subquery that is the right element of a
! 490: # LEFT JOIN evaluate to NULL for rows where the LEFT JOIN does not
! 491: # match. Ticket #3300
! 492: do_test join-8.4 {
! 493: execsql {
! 494: SELECT * FROM t9 LEFT JOIN (SELECT 44, p, q FROM t11) AS sub1 ON p=a
! 495: }
! 496: } {1 11 {} {} {} 2 22 44 2 111}
! 497: }
! 498: } ;# ifcapable view
! 499:
! 500: # Ticket #350 describes a scenario where LEFT OUTER JOIN does not
! 501: # function correctly if the right table in the join is really
! 502: # subquery.
! 503: #
! 504: # To test the problem, we generate the same LEFT OUTER JOIN in two
! 505: # separate selects but with on using a subquery and the other calling
! 506: # the table directly. Then connect the two SELECTs using an EXCEPT.
! 507: # Both queries should generate the same results so the answer should
! 508: # be an empty set.
! 509: #
! 510: ifcapable compound {
! 511: do_test join-9.1 {
! 512: execsql {
! 513: BEGIN;
! 514: CREATE TABLE t12(a,b);
! 515: INSERT INTO t12 VALUES(1,11);
! 516: INSERT INTO t12 VALUES(2,22);
! 517: CREATE TABLE t13(b,c);
! 518: INSERT INTO t13 VALUES(22,222);
! 519: COMMIT;
! 520: }
! 521: } {}
! 522:
! 523: ifcapable subquery {
! 524: do_test join-9.1.1 {
! 525: execsql {
! 526: SELECT * FROM t12 NATURAL LEFT JOIN t13
! 527: EXCEPT
! 528: SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);
! 529: }
! 530: } {}
! 531: }
! 532: ifcapable view {
! 533: do_test join-9.2 {
! 534: execsql {
! 535: CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;
! 536: SELECT * FROM t12 NATURAL LEFT JOIN t13
! 537: EXCEPT
! 538: SELECT * FROM t12 NATURAL LEFT JOIN v13;
! 539: }
! 540: } {}
! 541: } ;# ifcapable view
! 542: } ;# ifcapable compound
! 543:
! 544: ifcapable subquery {
! 545: # Ticket #1697: Left Join WHERE clause terms that contain an
! 546: # aggregate subquery.
! 547: #
! 548: do_test join-10.1 {
! 549: execsql {
! 550: CREATE TABLE t21(a,b,c);
! 551: CREATE TABLE t22(p,q);
! 552: CREATE INDEX i22 ON t22(q);
! 553: SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=
! 554: (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);
! 555: }
! 556: } {}
! 557:
! 558: # Test a LEFT JOIN when the right-hand side of hte join is an empty
! 559: # sub-query. Seems fine.
! 560: #
! 561: do_test join-10.2 {
! 562: execsql {
! 563: CREATE TABLE t23(a, b, c);
! 564: CREATE TABLE t24(a, b, c);
! 565: INSERT INTO t23 VALUES(1, 2, 3);
! 566: }
! 567: execsql {
! 568: SELECT * FROM t23 LEFT JOIN t24;
! 569: }
! 570: } {1 2 3 {} {} {}}
! 571: do_test join-10.3 {
! 572: execsql {
! 573: SELECT * FROM t23 LEFT JOIN (SELECT * FROM t24);
! 574: }
! 575: } {1 2 3 {} {} {}}
! 576:
! 577: } ;# ifcapable subquery
! 578:
! 579: #-------------------------------------------------------------------------
! 580: # The following tests are to ensure that bug b73fb0bd64 is fixed.
! 581: #
! 582: do_test join-11.1 {
! 583: drop_all_tables
! 584: execsql {
! 585: CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
! 586: CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
! 587: INSERT INTO t1 VALUES(1,'abc');
! 588: INSERT INTO t1 VALUES(2,'def');
! 589: INSERT INTO t2 VALUES(1,'abc');
! 590: INSERT INTO t2 VALUES(2,'def');
! 591: SELECT * FROM t1 NATURAL JOIN t2;
! 592: }
! 593: } {1 abc 2 def}
! 594:
! 595: do_test join-11.2 {
! 596: execsql { SELECT a FROM t1 JOIN t1 USING (a)}
! 597: } {1 2}
! 598: do_test join-11.3 {
! 599: execsql { SELECT a FROM t1 JOIN t1 AS t2 USING (a)}
! 600: } {1 2}
! 601: do_test join-11.3 {
! 602: execsql { SELECT * FROM t1 NATURAL JOIN t1 AS t2}
! 603: } {1 abc 2 def}
! 604: do_test join-11.4 {
! 605: execsql { SELECT * FROM t1 NATURAL JOIN t1 }
! 606: } {1 abc 2 def}
! 607:
! 608: do_test join-11.5 {
! 609: drop_all_tables
! 610: execsql {
! 611: CREATE TABLE t1(a COLLATE nocase, b);
! 612: CREATE TABLE t2(a, b);
! 613: INSERT INTO t1 VALUES('ONE', 1);
! 614: INSERT INTO t1 VALUES('two', 2);
! 615: INSERT INTO t2 VALUES('one', 1);
! 616: INSERT INTO t2 VALUES('two', 2);
! 617: }
! 618: } {}
! 619: do_test join-11.6 {
! 620: execsql { SELECT * FROM t1 NATURAL JOIN t2 }
! 621: } {ONE 1 two 2}
! 622: do_test join-11.7 {
! 623: execsql { SELECT * FROM t2 NATURAL JOIN t1 }
! 624: } {two 2}
! 625:
! 626: do_test join-11.8 {
! 627: drop_all_tables
! 628: execsql {
! 629: CREATE TABLE t1(a, b TEXT);
! 630: CREATE TABLE t2(b INTEGER, a);
! 631: INSERT INTO t1 VALUES('one', '1.0');
! 632: INSERT INTO t1 VALUES('two', '2');
! 633: INSERT INTO t2 VALUES(1, 'one');
! 634: INSERT INTO t2 VALUES(2, 'two');
! 635: }
! 636: } {}
! 637: do_test join-11.9 {
! 638: execsql { SELECT * FROM t1 NATURAL JOIN t2 }
! 639: } {one 1.0 two 2}
! 640: do_test join-11.10 {
! 641: execsql { SELECT * FROM t2 NATURAL JOIN t1 }
! 642: } {1 one 2 two}
! 643:
! 644: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to join.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test