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

     1  # 2014-08-20
     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  # This file implements tests of the "skip-scan" query strategy.
    13  # In particular, this file looks at skipping intermediate terms
    14  # in an index.  For example, if (a,b,c) are indexed, and we have
    15  # "WHERE a=?1 AND c=?2" - verify that skip-scan can still be used.
    16  #
    17  
    18  set testdir [file dirname $argv0]
    19  source $testdir/tester.tcl
    20  
    21  do_execsql_test skipscan3-1.1 {
    22    CREATE TABLE t1(a,b,c,d,PRIMARY KEY(a,b,c));
    23    WITH RECURSIVE
    24      c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
    25    INSERT INTO t1(a,b,c,d)
    26      SELECT 1, 1, x, printf('x%04d',x) FROM c;
    27    ANALYZE;
    28  } {}
    29  
    30  # This version has long used skip-scan because of the "+a"
    31  #
    32  do_execsql_test skipscan3-1.2eqp {
    33    EXPLAIN QUERY PLAN SELECT d FROM t1 WHERE +a=1 AND c=32;
    34  } {/*ANY(a) AND ANY(b)*/}
    35  do_execsql_test skipscan3-1.2 {
    36    SELECT d FROM t1 WHERE +a=1 AND c=32;
    37  } {x0032}
    38  
    39  # This version (with "a" instead of "+a") should use skip-scan but
    40  # did not prior to changes implemented on 2014-08-20
    41  #
    42  do_execsql_test skipscan3-1.3eqp {
    43    EXPLAIN QUERY PLAN SELECT d FROM t1 WHERE a=1 AND c=32;
    44  } {/*ANY(a) AND ANY(b)*/}
    45  do_execsql_test skipscan3-1.3 {
    46    SELECT d FROM t1 WHERE a=1 AND c=32;
    47  } {x0032}
    48  
    49  # Repeat the test on a WITHOUT ROWID table
    50  #
    51  do_execsql_test skipscan3-2.1 {
    52    CREATE TABLE t2(a,b,c,d,PRIMARY KEY(a,b,c)) WITHOUT ROWID;
    53    WITH RECURSIVE
    54      c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
    55    INSERT INTO t2(a,b,c,d)
    56      SELECT 1, 1, x, printf('x%04d',x) FROM c;
    57    ANALYZE;
    58  } {}
    59  do_execsql_test skipscan3-2.2eqp {
    60    EXPLAIN QUERY PLAN SELECT d FROM t2 WHERE +a=1 AND c=32;
    61  } {/*ANY(a) AND ANY(b)*/}
    62  do_execsql_test skipscan3-2.2 {
    63    SELECT d FROM t2 WHERE +a=1 AND c=32;
    64  } {x0032}
    65  do_execsql_test skipscan3-2.3eqp {
    66    EXPLAIN QUERY PLAN SELECT d FROM t2 WHERE a=1 AND c=32;
    67  } {/*ANY(a) AND ANY(b)*/}
    68  do_execsql_test skipscan3-2.3 {
    69    SELECT d FROM t2 WHERE a=1 AND c=32;
    70  } {x0032}
    71  
    72    
    73  finish_test