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

     1  # 2016-07-27
     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 ORDER BY and LIMIT on an index scan.
    13  #
    14  
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  
    19  # Performance regression reported at
    20  # http://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg98615.html
    21  #
    22  # Caused by the ORDER BY LIMIT optionation for check-in
    23  # https://sqlite.org/src/info/bf46179d44843769
    24  #
    25  # Fixed on approximately 2016-07-27 by changes that compute a better score
    26  # for index scans by taking into account WHERE clause constraints that can
    27  # be handled by the index and do not require a table lookup.
    28  #
    29  do_execsql_test 1.0 {
    30    CREATE TABLE t1(a,b,c,d);
    31    WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<100)
    32    INSERT INTO t1(a,b,c,d)
    33       SELECT x/10, x%10, x%19, x FROM c;
    34    CREATE INDEX t1abc ON t1(a,b,c);
    35    SELECT * FROM t1 WHERE c=4 ORDER BY a, b LIMIT 2;
    36  } {0 4 4 4 2 3 4 23}
    37  
    38  # Prior to the fix, the following EQP would show a table scan and a sort
    39  # rather than an index scan.
    40  #
    41  do_execsql_test 1.0eqp {
    42    EXPLAIN QUERY PLAN
    43    SELECT * FROM t1 WHERE c=4 ORDER BY a, b LIMIT 2;
    44  } {/SCAN t1 USING INDEX t1abc/}
    45  
    46  # If we change the index so that it no longer covers the WHERE clause,
    47  # then we should (correctly) revert to using a table scan.
    48  #
    49  do_execsql_test 1.1 {
    50    DROP INDEX t1abc;
    51    CREATE INDEX t1abd ON t1(a,b,d);
    52    SELECT * FROM t1 WHERE c=4 ORDER BY a, b LIMIT 2;
    53  } {0 4 4 4 2 3 4 23}
    54  do_execsql_test 1.1eqp {
    55    EXPLAIN QUERY PLAN
    56    SELECT * FROM t1 WHERE c=4 ORDER BY a, b LIMIT 2;
    57  } {~/USING INDEX/}
    58  
    59  
    60  finish_test