Return to where3.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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