Annotation of embedaddon/sqlite3/test/where3.test, revision 1.1
1.1 ! misho 1: # 2006 January 31
! 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. The
! 12: # focus of this file is testing the join reordering optimization
! 13: # in cases that include a LEFT JOIN.
! 14: #
! 15: # $Id: where3.test,v 1.4 2008/04/17 19:14:02 drh Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: # The following is from ticket #1652.
! 21: #
! 22: # A comma join then a left outer join: A,B left join C.
! 23: # Arrange indices so that the B table is chosen to go first.
! 24: # Also put an index on C, but make sure that A is chosen before C.
! 25: #
! 26: do_test where3-1.1 {
! 27: execsql {
! 28: CREATE TABLE t1(a, b);
! 29: CREATE TABLE t2(p, q);
! 30: CREATE TABLE t3(x, y);
! 31:
! 32: INSERT INTO t1 VALUES(111,'one');
! 33: INSERT INTO t1 VALUES(222,'two');
! 34: INSERT INTO t1 VALUES(333,'three');
! 35:
! 36: INSERT INTO t2 VALUES(1,111);
! 37: INSERT INTO t2 VALUES(2,222);
! 38: INSERT INTO t2 VALUES(4,444);
! 39: CREATE INDEX t2i1 ON t2(p);
! 40:
! 41: INSERT INTO t3 VALUES(999,'nine');
! 42: CREATE INDEX t3i1 ON t3(x);
! 43:
! 44: SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x WHERE p=2 AND a=q;
! 45: }
! 46: } {222 two 2 222 {} {}}
! 47:
! 48: ifcapable explain {
! 49: do_test where3-1.1.1 {
! 50: explain_no_trace {SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x
! 51: WHERE p=2 AND a=q}
! 52: } [explain_no_trace {SELECT * FROM t1, t2 LEFT JOIN t3 ON x=q
! 53: WHERE p=2 AND a=q}]
! 54: }
! 55:
! 56: # Ticket #1830
! 57: #
! 58: # This is similar to the above but with the LEFT JOIN on the
! 59: # other side.
! 60: #
! 61: do_test where3-1.2 {
! 62: execsql {
! 63: CREATE TABLE parent1(parent1key, child1key, Child2key, child3key);
! 64: CREATE TABLE child1 ( child1key NVARCHAR, value NVARCHAR );
! 65: CREATE UNIQUE INDEX PKIDXChild1 ON child1 ( child1key );
! 66: CREATE TABLE child2 ( child2key NVARCHAR, value NVARCHAR );
! 67:
! 68: INSERT INTO parent1(parent1key,child1key,child2key)
! 69: VALUES ( 1, 'C1.1', 'C2.1' );
! 70: INSERT INTO child1 ( child1key, value ) VALUES ( 'C1.1', 'Value for C1.1' );
! 71: INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.1', 'Value for C2.1' );
! 72:
! 73: INSERT INTO parent1 ( parent1key, child1key, child2key )
! 74: VALUES ( 2, 'C1.2', 'C2.2' );
! 75: INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.2', 'Value for C2.2' );
! 76:
! 77: INSERT INTO parent1 ( parent1key, child1key, child2key )
! 78: VALUES ( 3, 'C1.3', 'C2.3' );
! 79: INSERT INTO child1 ( child1key, value ) VALUES ( 'C1.3', 'Value for C1.3' );
! 80: INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.3', 'Value for C2.3' );
! 81:
! 82: SELECT parent1.parent1key, child1.value, child2.value
! 83: FROM parent1
! 84: LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key
! 85: INNER JOIN child2 ON child2.child2key = parent1.child2key;
! 86: }
! 87: } {1 {Value for C1.1} {Value for C2.1} 2 {} {Value for C2.2} 3 {Value for C1.3} {Value for C2.3}}
! 88:
! 89: ifcapable explain {
! 90: do_test where3-1.2.1 {
! 91: explain_no_trace {
! 92: SELECT parent1.parent1key, child1.value, child2.value
! 93: FROM parent1
! 94: LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key
! 95: INNER JOIN child2 ON child2.child2key = parent1.child2key;
! 96: }
! 97: } [explain_no_trace {
! 98: SELECT parent1.parent1key, child1.value, child2.value
! 99: FROM parent1
! 100: LEFT OUTER JOIN child1 ON parent1.child1key = child1.child1key
! 101: INNER JOIN child2 ON child2.child2key = parent1.child2key;
! 102: }]
! 103: }
! 104:
! 105: # This procedure executes the SQL. Then it appends
! 106: # the ::sqlite_query_plan variable.
! 107: #
! 108: proc queryplan {sql} {
! 109: set ::sqlite_sort_count 0
! 110: set data [execsql $sql]
! 111: return [concat $data $::sqlite_query_plan]
! 112: }
! 113:
! 114:
! 115: # If you have a from clause of the form: A B C left join D
! 116: # then make sure the query optimizer is able to reorder the
! 117: # A B C part anyway it wants.
! 118: #
! 119: # Following the fix to ticket #1652, there was a time when
! 120: # the C table would not reorder. So the following reorderings
! 121: # were possible:
! 122: #
! 123: # A B C left join D
! 124: # B A C left join D
! 125: #
! 126: # But these reorders were not allowed
! 127: #
! 128: # C A B left join D
! 129: # A C B left join D
! 130: # C B A left join D
! 131: # B C A left join D
! 132: #
! 133: # The following tests are here to verify that the latter four
! 134: # reorderings are allowed again.
! 135: #
! 136: do_test where3-2.1 {
! 137: execsql {
! 138: CREATE TABLE tA(apk integer primary key, ax);
! 139: CREATE TABLE tB(bpk integer primary key, bx);
! 140: CREATE TABLE tC(cpk integer primary key, cx);
! 141: CREATE TABLE tD(dpk integer primary key, dx);
! 142: }
! 143: queryplan {
! 144: SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
! 145: WHERE cpk=bx AND bpk=ax
! 146: }
! 147: } {tA {} tB * tC * tD *}
! 148: do_test where3-2.1.1 {
! 149: queryplan {
! 150: SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
! 151: WHERE cpk=bx AND bpk=ax
! 152: }
! 153: } {tA {} tB * tC * tD *}
! 154: do_test where3-2.1.2 {
! 155: queryplan {
! 156: SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
! 157: WHERE bx=cpk AND bpk=ax
! 158: }
! 159: } {tA {} tB * tC * tD *}
! 160: do_test where3-2.1.3 {
! 161: queryplan {
! 162: SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
! 163: WHERE bx=cpk AND ax=bpk
! 164: }
! 165: } {tA {} tB * tC * tD *}
! 166: do_test where3-2.1.4 {
! 167: queryplan {
! 168: SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
! 169: WHERE bx=cpk AND ax=bpk
! 170: }
! 171: } {tA {} tB * tC * tD *}
! 172: do_test where3-2.1.5 {
! 173: queryplan {
! 174: SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
! 175: WHERE cpk=bx AND ax=bpk
! 176: }
! 177: } {tA {} tB * tC * tD *}
! 178: do_test where3-2.2 {
! 179: queryplan {
! 180: SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
! 181: WHERE cpk=bx AND apk=bx
! 182: }
! 183: } {tB {} tA * tC * tD *}
! 184: do_test where3-2.3 {
! 185: queryplan {
! 186: SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
! 187: WHERE cpk=bx AND apk=bx
! 188: }
! 189: } {tB {} tA * tC * tD *}
! 190: do_test where3-2.4 {
! 191: queryplan {
! 192: SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
! 193: WHERE apk=cx AND bpk=ax
! 194: }
! 195: } {tC {} tA * tB * tD *}
! 196: do_test where3-2.5 {
! 197: queryplan {
! 198: SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
! 199: WHERE cpk=ax AND bpk=cx
! 200: }
! 201: } {tA {} tC * tB * tD *}
! 202: do_test where3-2.6 {
! 203: queryplan {
! 204: SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
! 205: WHERE bpk=cx AND apk=bx
! 206: }
! 207: } {tC {} tB * tA * tD *}
! 208: do_test where3-2.7 {
! 209: queryplan {
! 210: SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
! 211: WHERE cpk=bx AND apk=cx
! 212: }
! 213: } {tB {} tC * tA * tD *}
! 214:
! 215: # Ticket [13f033c865f878953]
! 216: # If the outer loop must be a full table scan, do not let ANALYZE trick
! 217: # the planner into use a table for the outer loop that might be indexable
! 218: # if held until an inner loop.
! 219: #
! 220: do_execsql_test where3-3.0 {
! 221: CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c);
! 222: CREATE INDEX t301c ON t301(c);
! 223: INSERT INTO t301 VALUES(1,2,3);
! 224: CREATE TABLE t302(x, y);
! 225: INSERT INTO t302 VALUES(4,5);
! 226: ANALYZE;
! 227: explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y;
! 228: } {
! 229: 0 0 0 {SCAN TABLE t302 (~1 rows)}
! 230: 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
! 231: }
! 232: do_execsql_test where3-3.1 {
! 233: explain query plan
! 234: SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
! 235: } {
! 236: 0 0 1 {SCAN TABLE t302 (~1 rows)}
! 237: 0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
! 238: }
! 239:
! 240: # Verify that when there are multiple tables in a join which must be
! 241: # full table scans that the query planner attempts put the table with
! 242: # the fewest number of output rows as the outer loop.
! 243: #
! 244: do_execsql_test where3-4.0 {
! 245: CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c);
! 246: CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r);
! 247: CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z);
! 248: EXPLAIN QUERY PLAN
! 249: SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*';
! 250: } {
! 251: 0 0 2 {SCAN TABLE t402 (~500000 rows)}
! 252: 0 1 0 {SCAN TABLE t400 (~1000000 rows)}
! 253: 0 2 1 {SCAN TABLE t401 (~1000000 rows)}
! 254: }
! 255: do_execsql_test where3-4.1 {
! 256: EXPLAIN QUERY PLAN
! 257: SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*';
! 258: } {
! 259: 0 0 1 {SCAN TABLE t401 (~500000 rows)}
! 260: 0 1 0 {SCAN TABLE t400 (~1000000 rows)}
! 261: 0 2 2 {SCAN TABLE t402 (~1000000 rows)}
! 262: }
! 263: do_execsql_test where3-4.2 {
! 264: EXPLAIN QUERY PLAN
! 265: SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*';
! 266: } {
! 267: 0 0 0 {SCAN TABLE t400 (~500000 rows)}
! 268: 0 1 1 {SCAN TABLE t401 (~1000000 rows)}
! 269: 0 2 2 {SCAN TABLE t402 (~1000000 rows)}
! 270: }
! 271:
! 272: # Verify that a performance regression encountered by firefox
! 273: # has been fixed.
! 274: #
! 275: do_execsql_test where3-5.0 {
! 276: CREATE TABLE aaa (id INTEGER PRIMARY KEY, type INTEGER,
! 277: fk INTEGER DEFAULT NULL, parent INTEGER,
! 278: position INTEGER, title LONGVARCHAR,
! 279: keyword_id INTEGER, folder_type TEXT,
! 280: dateAdded INTEGER, lastModified INTEGER);
! 281: CREATE INDEX aaa_111 ON aaa (fk, type);
! 282: CREATE INDEX aaa_222 ON aaa (parent, position);
! 283: CREATE INDEX aaa_333 ON aaa (fk, lastModified);
! 284: CREATE TABLE bbb (id INTEGER PRIMARY KEY, type INTEGER,
! 285: fk INTEGER DEFAULT NULL, parent INTEGER,
! 286: position INTEGER, title LONGVARCHAR,
! 287: keyword_id INTEGER, folder_type TEXT,
! 288: dateAdded INTEGER, lastModified INTEGER);
! 289: CREATE INDEX bbb_111 ON bbb (fk, type);
! 290: CREATE INDEX bbb_222 ON bbb (parent, position);
! 291: CREATE INDEX bbb_333 ON bbb (fk, lastModified);
! 292:
! 293: EXPLAIN QUERY PLAN
! 294: SELECT bbb.title AS tag_title
! 295: FROM aaa JOIN bbb ON bbb.id = aaa.parent
! 296: WHERE aaa.fk = 'constant'
! 297: AND LENGTH(bbb.title) > 0
! 298: AND bbb.parent = 4
! 299: ORDER BY bbb.title COLLATE NOCASE ASC;
! 300: } {
! 301: 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)}
! 302: 0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
! 303: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 304: }
! 305: do_execsql_test where3-5.1 {
! 306: EXPLAIN QUERY PLAN
! 307: SELECT bbb.title AS tag_title
! 308: FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent
! 309: WHERE aaa.fk = 'constant'
! 310: AND LENGTH(bbb.title) > 0
! 311: AND bbb.parent = 4
! 312: ORDER BY bbb.title COLLATE NOCASE ASC;
! 313: } {
! 314: 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)}
! 315: 0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
! 316: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 317: }
! 318: do_execsql_test where3-5.2 {
! 319: EXPLAIN QUERY PLAN
! 320: SELECT bbb.title AS tag_title
! 321: FROM bbb JOIN aaa ON bbb.id = aaa.parent
! 322: WHERE aaa.fk = 'constant'
! 323: AND LENGTH(bbb.title) > 0
! 324: AND bbb.parent = 4
! 325: ORDER BY bbb.title COLLATE NOCASE ASC;
! 326: } {
! 327: 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)}
! 328: 0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
! 329: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 330: }
! 331: do_execsql_test where3-5.3 {
! 332: EXPLAIN QUERY PLAN
! 333: SELECT bbb.title AS tag_title
! 334: FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent
! 335: WHERE aaa.fk = 'constant'
! 336: AND LENGTH(bbb.title) > 0
! 337: AND bbb.parent = 4
! 338: ORDER BY bbb.title COLLATE NOCASE ASC;
! 339: } {
! 340: 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)}
! 341: 0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
! 342: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
! 343: }
! 344:
! 345: # Name resolution with NATURAL JOIN and USING
! 346: #
! 347: do_test where3-6.setup {
! 348: db eval {
! 349: CREATE TABLE t6w(a, w);
! 350: INSERT INTO t6w VALUES(1, 'w-one');
! 351: INSERT INTO t6w VALUES(2, 'w-two');
! 352: INSERT INTO t6w VALUES(9, 'w-nine');
! 353: CREATE TABLE t6x(a, x);
! 354: INSERT INTO t6x VALUES(1, 'x-one');
! 355: INSERT INTO t6x VALUES(3, 'x-three');
! 356: INSERT INTO t6x VALUES(9, 'x-nine');
! 357: CREATE TABLE t6y(a, y);
! 358: INSERT INTO t6y VALUES(1, 'y-one');
! 359: INSERT INTO t6y VALUES(4, 'y-four');
! 360: INSERT INTO t6y VALUES(9, 'y-nine');
! 361: CREATE TABLE t6z(a, z);
! 362: INSERT INTO t6z VALUES(1, 'z-one');
! 363: INSERT INTO t6z VALUES(5, 'z-five');
! 364: INSERT INTO t6z VALUES(9, 'z-nine');
! 365: }
! 366: } {}
! 367: set cnt 0
! 368: foreach predicate {
! 369: {}
! 370: {ORDER BY a}
! 371: {ORDER BY t6w.a}
! 372: {WHERE a>0}
! 373: {WHERE t6y.a>0}
! 374: {WHERE a>0 ORDER BY a}
! 375: } {
! 376: incr cnt
! 377: do_test where3-6.$cnt.1 {
! 378: set sql "SELECT * FROM t6w NATURAL JOIN t6x NATURAL JOIN t6y"
! 379: append sql " NATURAL JOIN t6z "
! 380: append sql $::predicate
! 381: db eval $sql
! 382: } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
! 383: do_test where3-6.$cnt.2 {
! 384: set sql "SELECT * FROM t6w JOIN t6x USING(a) JOIN t6y USING(a)"
! 385: append sql " JOIN t6z USING(a) "
! 386: append sql $::predicate
! 387: db eval $sql
! 388: } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
! 389: do_test where3-6.$cnt.3 {
! 390: set sql "SELECT * FROM t6w NATURAL JOIN t6x JOIN t6y USING(a)"
! 391: append sql " JOIN t6z USING(a) "
! 392: append sql $::predicate
! 393: db eval $sql
! 394: } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
! 395: do_test where3-6.$cnt.4 {
! 396: set sql "SELECT * FROM t6w JOIN t6x USING(a) NATURAL JOIN t6y"
! 397: append sql " JOIN t6z USING(a) "
! 398: append sql $::predicate
! 399: db eval $sql
! 400: } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
! 401: do_test where3-6.$cnt.5 {
! 402: set sql "SELECT * FROM t6w JOIN t6x USING(a) JOIN t6y USING(a)"
! 403: append sql " NATURAL JOIN t6z "
! 404: append sql $::predicate
! 405: db eval $sql
! 406: } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
! 407: do_test where3-6.$cnt.6 {
! 408: set sql "SELECT * FROM t6w JOIN t6x USING(a) NATURAL JOIN t6y"
! 409: append sql " NATURAL JOIN t6z "
! 410: append sql $::predicate
! 411: db eval $sql
! 412: } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
! 413: do_test where3-6.$cnt.7 {
! 414: set sql "SELECT * FROM t6w NATURAL JOIN t6x JOIN t6y USING(a)"
! 415: append sql " NATURAL JOIN t6z "
! 416: append sql $::predicate
! 417: db eval $sql
! 418: } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
! 419: do_test where3-6.$cnt.8 {
! 420: set sql "SELECT * FROM t6w NATURAL JOIN t6x NATURAL JOIN t6y"
! 421: append sql " JOIN t6z USING(a) "
! 422: append sql $::predicate
! 423: db eval $sql
! 424: } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
! 425: }
! 426:
! 427:
! 428: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to where3.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