gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/whereH.test (about)

     1  # 2014-03-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  # 
    12  # Test cases for query planning decisions where one candidate index
    13  # covers a proper superset of the WHERE clause terms of another
    14  # candidate index.
    15  #
    16  
    17  set testdir [file dirname $argv0]
    18  source $testdir/tester.tcl
    19  
    20  do_execsql_test whereH-1.1 {
    21    CREATE TABLE t1(a,b,c,d);
    22    CREATE INDEX t1abc ON t1(a,b,c);
    23    CREATE INDEX t1bc ON t1(b,c);
    24  
    25    EXPLAIN QUERY PLAN
    26    SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
    27  } {/INDEX t1abc /}
    28  do_execsql_test whereH-1.2 {
    29    EXPLAIN QUERY PLAN
    30    SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
    31  } {~/TEMP B-TREE FOR ORDER BY/}
    32  
    33  do_execsql_test whereH-2.1 {
    34    DROP TABLE t1;
    35    CREATE TABLE t1(a,b,c,d);
    36    CREATE INDEX t1bc ON t1(b,c);
    37    CREATE INDEX t1abc ON t1(a,b,c);
    38  
    39    EXPLAIN QUERY PLAN
    40    SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
    41  } {/INDEX t1abc /}
    42  do_execsql_test whereH-2.2 {
    43    EXPLAIN QUERY PLAN
    44    SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
    45  } {~/TEMP B-TREE FOR ORDER BY/}
    46  
    47  do_execsql_test whereH-3.1 {
    48    DROP TABLE t1;
    49    CREATE TABLE t1(a,b,c,d,e);
    50    CREATE INDEX t1cd ON t1(c,d);
    51    CREATE INDEX t1bcd ON t1(b,c,d);
    52    CREATE INDEX t1abcd ON t1(a,b,c,d);
    53  
    54    EXPLAIN QUERY PLAN
    55    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
    56  } {/INDEX t1abcd /}
    57  do_execsql_test whereH-3.2 {
    58    EXPLAIN QUERY PLAN
    59    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
    60  } {~/TEMP B-TREE FOR ORDER BY/}
    61  
    62  do_execsql_test whereH-4.1 {
    63    DROP TABLE t1;
    64    CREATE TABLE t1(a,b,c,d,e);
    65    CREATE INDEX t1cd ON t1(c,d);
    66    CREATE INDEX t1abcd ON t1(a,b,c,d);
    67    CREATE INDEX t1bcd ON t1(b,c,d);
    68  
    69    EXPLAIN QUERY PLAN
    70    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
    71  } {/INDEX t1abcd /}
    72  do_execsql_test whereH-4.2 {
    73    EXPLAIN QUERY PLAN
    74    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
    75  } {~/TEMP B-TREE FOR ORDER BY/}
    76  
    77  do_execsql_test whereH-5.1 {
    78    DROP TABLE t1;
    79    CREATE TABLE t1(a,b,c,d,e);
    80    CREATE INDEX t1bcd ON t1(b,c,d);
    81    CREATE INDEX t1cd ON t1(c,d);
    82    CREATE INDEX t1abcd ON t1(a,b,c,d);
    83  
    84    EXPLAIN QUERY PLAN
    85    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
    86  } {/INDEX t1abcd /}
    87  do_execsql_test whereH-5.2 {
    88    EXPLAIN QUERY PLAN
    89    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
    90  } {~/TEMP B-TREE FOR ORDER BY/}
    91  
    92  do_execsql_test whereH-6.1 {
    93    DROP TABLE t1;
    94    CREATE TABLE t1(a,b,c,d,e);
    95    CREATE INDEX t1bcd ON t1(b,c,d);
    96    CREATE INDEX t1abcd ON t1(a,b,c,d);
    97    CREATE INDEX t1cd ON t1(c,d);
    98  
    99    EXPLAIN QUERY PLAN
   100    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
   101  } {/INDEX t1abcd /}
   102  do_execsql_test whereH-6.2 {
   103    EXPLAIN QUERY PLAN
   104    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
   105  } {~/TEMP B-TREE FOR ORDER BY/}
   106  
   107  do_execsql_test whereH-7.1 {
   108    DROP TABLE t1;
   109    CREATE TABLE t1(a,b,c,d,e);
   110    CREATE INDEX t1abcd ON t1(a,b,c,d);
   111    CREATE INDEX t1bcd ON t1(b,c,d);
   112    CREATE INDEX t1cd ON t1(c,d);
   113  
   114    EXPLAIN QUERY PLAN
   115    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
   116  } {/INDEX t1abcd /}
   117  do_execsql_test whereH-7.2 {
   118    EXPLAIN QUERY PLAN
   119    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
   120  } {~/TEMP B-TREE FOR ORDER BY/}
   121  
   122  do_execsql_test whereH-8.1 {
   123    DROP TABLE t1;
   124    CREATE TABLE t1(a,b,c,d,e);
   125    CREATE INDEX t1abcd ON t1(a,b,c,d);
   126    CREATE INDEX t1cd ON t1(c,d);
   127    CREATE INDEX t1bcd ON t1(b,c,d);
   128  
   129    EXPLAIN QUERY PLAN
   130    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
   131  } {/INDEX t1abcd /}
   132  do_execsql_test whereH-8.2 {
   133    EXPLAIN QUERY PLAN
   134    SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
   135  } {~/TEMP B-TREE FOR ORDER BY/}
   136  
   137  
   138  
   139  finish_test