/ Check-in [4997d8b8]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:More test cases for the OR optimization.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | or-opt
Files: files | file ages | folders
SHA1: 4997d8b81cd3ea7c708911bfece00020d11224f9
User & Date: drh 2011-10-07 16:08:28
Context
2011-10-07
17:45
Add testcase() macros to ensure good test coverage. Closed-Leaf check-in: 5c132592 user: drh tags: or-opt
16:08
More test cases for the OR optimization. check-in: 4997d8b8 user: drh tags: or-opt
14:40
Prevent infinite recursion of in the query planner for some pathological test cases by disabling OR-clause processing upon first recursion. check-in: 9fca05ea user: drh tags: or-opt
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/where7.test.

     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the multi-index OR clause optimizer.
    13         -#
    14         -# $Id: where7.test,v 1.9 2009/06/07 23:45:11 drh Exp $
    15     13   
    16     14   set testdir [file dirname $argv0]
    17     15   source $testdir/tester.tcl
    18     16   
    19     17   ifcapable !or_opt {
    20     18     finish_test
    21     19     return
................................................................................
 23337  23335       FROM t302 JOIN t301 ON t302.c8 = t301.c8
 23338  23336       WHERE t302.c2 = 19571
 23339  23337         AND t302.c3 > 1287603136
 23340  23338         AND (t301.c4 = 1407449685622784
 23341  23339              OR t301.c8 = 1407424651264000)
 23342  23340      ORDER BY t302.c5 LIMIT 200;
 23343  23341   } {
 23344         -  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} 
        23342  +  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~5 rows)} 
 23345  23343     0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
 23346  23344     0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
 23347  23345     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
 23348  23346   }
 23349  23347   
 23350  23348   finish_test

Changes to test/where9.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the multi-index OR clause optimizer.
    13     13   #
    14         -# $Id: where9.test,v 1.9 2009/06/05 17:09:12 drh Exp $
    15     14   
    16     15   set testdir [file dirname $argv0]
    17     16   source $testdir/tester.tcl
    18     17   
    19     18   ifcapable !or_opt {
    20     19     finish_test
    21     20     return
................................................................................
   361    360     do_execsql_test where9-3.1 {
   362    361       EXPLAIN QUERY PLAN
   363    362       SELECT t2.a FROM t1, t2
   364    363       WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
   365    364     } {
   366    365       0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
   367    366       0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
   368         -    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
          367  +    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
   369    368     }
   370    369     do_execsql_test where9-3.2 {
   371    370       EXPLAIN QUERY PLAN
   372    371       SELECT coalesce(t2.a,9999)
   373    372       FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
   374    373       WHERE t1.a=80
   375    374     } {
   376    375       0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
   377    376       0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
   378         -    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
          377  +    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
   379    378     }
   380    379   } 
   381    380   
   382    381   # Make sure that INDEXED BY and multi-index OR clauses play well with
   383    382   # one another.
   384    383   #
   385    384   do_test where9-4.1 {
................................................................................
   450    449   ifcapable explain {
   451    450     # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
   452    451     # the former is an equality test which is expected to return fewer rows.
   453    452     #
   454    453     do_execsql_test where9-5.1 {
   455    454       EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
   456    455     } {
   457         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~10 rows)} 
   458         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~10 rows)}
          456  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~2 rows)} 
          457  +    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~2 rows)}
   459    458     }
   460    459   
   461    460     # In contrast, b=1000 is preferred over any OR-clause.
   462    461     #
   463    462     do_execsql_test where9-5.2 {
   464    463       EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
   465    464     } {
................................................................................
   778    777     catchsql {
   779    778       UPDATE t1 INDEXED BY t1b SET a=a+100
   780    779        WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
   781    780           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
   782    781           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   783    782     }
   784    783   } {1 {cannot use index: t1b}}
          784  +
          785  +############################################################################
          786  +# Test cases where terms inside an OR series are combined with AND terms
          787  +# external to the OR clause.  In other words, cases where
          788  +#
          789  +#              x AND (y OR z)
          790  +#
          791  +# is able to use indices on x,y and x,z, or indices y,x and z,x.
          792  +#
          793  +do_test where9-7.0 {
          794  +  execsql {
          795  +    CREATE TABLE t5(a, b, c, d, e, f, g, x, y);
          796  +    INSERT INTO t5
          797  +     SELECT a, b, c, e, d, f, g,
          798  +            CASE WHEN (a&1)!=0 THEN 'y' ELSE 'n' END,
          799  +            CASE WHEN (a&2)!=0 THEN 'y' ELSE 'n' END
          800  +       FROM t1;
          801  +    CREATE INDEX t5xb ON t5(x, b);
          802  +    CREATE INDEX t5xc ON t5(x, c);
          803  +    CREATE INDEX t5xd ON t5(x, d);
          804  +    CREATE INDEX t5xe ON t5(x, e);
          805  +    CREATE INDEX t5xf ON t5(x, f);
          806  +    CREATE INDEX t5xg ON t5(x, g);
          807  +    CREATE INDEX t5yb ON t5(y, b);
          808  +    CREATE INDEX t5yc ON t5(y, c);
          809  +    CREATE INDEX t5yd ON t5(y, d);
          810  +    CREATE INDEX t5ye ON t5(y, e);
          811  +    CREATE INDEX t5yf ON t5(y, f);
          812  +    CREATE INDEX t5yg ON t5(y, g);
          813  +    CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
          814  +    INSERT INTO t6 SELECT * FROM t5;
          815  +    ANALYZE t5;
          816  +  }
          817  +} {}
          818  +do_test where9-7.1.1 {
          819  +  count_steps {
          820  +    SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
          821  +  }
          822  +} {79 81 83 scan 0 sort 1}
          823  +do_test where9-7.1.2 {
          824  +  execsql {
          825  +    SELECT a FROM t6 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
          826  +  }
          827  +} {79 81 83}
          828  +do_test where9-7.1.3 {
          829  +  count_steps {
          830  +    SELECT a FROM t5 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
          831  +  }
          832  +} {80 scan 0 sort 1}
          833  +do_test where9-7.1.4 {
          834  +  execsql {
          835  +    SELECT a FROM t6 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
          836  +  }
          837  +} {80}
          838  +do_test where9-7.2.1 {
          839  +  count_steps {
          840  +    SELECT a FROM t5 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
          841  +  }
          842  +} {83 scan 0 sort 1}
          843  +do_test where9-7.2.2 {
          844  +  execsql {
          845  +    SELECT a FROM t6 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
          846  +  }
          847  +} {83}
          848  +do_test where9-7.3.1 {
          849  +  count_steps {
          850  +    SELECT a FROM t5 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
          851  +  }
          852  +} {79 81 scan 0 sort 1}
          853  +do_test where9-7.3.2 {
          854  +  execsql {
          855  +    SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
          856  +  }
          857  +} {79 81}
          858  +
   785    859   
   786    860   finish_test