| # 2014-07-22 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # |
| # This file contains automated tests used to verify that the text terms |
| # at the end of sqlite_stat1.stat are processed correctly. |
| # |
| # (1) "unordered" means that the index cannot be used for ORDER BY |
| # or for range queries |
| # |
| # (2) "sz=NNN" sets the relative size of the index entries |
| # |
| # (3) All other fields are silently ignored |
| # |
| |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| set testprefix analyzeC |
| |
| # Baseline case. Range queries work OK. Indexes can be used for |
| # ORDER BY. |
| # |
| do_execsql_test 1.0 { |
| CREATE TABLE t1(a,b,c); |
| INSERT INTO t1(a,b,c) |
| VALUES(1,2,3),(7,8,9),(4,5,6),(10,11,12),(4,8,12),(1,11,111); |
| CREATE INDEX t1a ON t1(a); |
| CREATE INDEX t1b ON t1(b); |
| ANALYZE; |
| DELETE FROM sqlite_stat1; |
| INSERT INTO sqlite_stat1(tbl,idx,stat) |
| VALUES('t1','t1a','12345 2'),('t1','t1b','12345 4'); |
| ANALYZE sqlite_master; |
| SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; |
| } {4 5 6 # 7 8 9 # 4 8 12 #} |
| do_execsql_test 1.1 { |
| EXPLAIN QUERY PLAN |
| SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; |
| } {/.* USING INDEX t1a .a>. AND a<...*/} |
| do_execsql_test 1.2 { |
| SELECT c FROM t1 ORDER BY a; |
| } {3 111 6 12 9 12} |
| do_execsql_test 1.3 { |
| EXPLAIN QUERY PLAN |
| SELECT c FROM t1 ORDER BY a; |
| } {/.*SCAN TABLE t1 USING INDEX t1a.*/} |
| do_execsql_test 1.3x { |
| EXPLAIN QUERY PLAN |
| SELECT c FROM t1 ORDER BY a; |
| } {~/.*B-TREE FOR ORDER BY.*/} |
| |
| # Now mark the t1a index as "unordered". Range queries and ORDER BY no |
| # longer use the index, but equality queries do. |
| # |
| do_execsql_test 2.0 { |
| UPDATE sqlite_stat1 SET stat='12345 2 unordered' WHERE idx='t1a'; |
| ANALYZE sqlite_master; |
| SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; |
| } {4 5 6 # 7 8 9 # 4 8 12 #} |
| do_execsql_test 2.1 { |
| EXPLAIN QUERY PLAN |
| SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; |
| } {~/.*USING INDEX.*/} |
| do_execsql_test 2.2 { |
| SELECT c FROM t1 ORDER BY a; |
| } {3 111 6 12 9 12} |
| do_execsql_test 2.3 { |
| EXPLAIN QUERY PLAN |
| SELECT c FROM t1 ORDER BY a; |
| } {~/.*USING INDEX.*/} |
| do_execsql_test 2.3x { |
| EXPLAIN QUERY PLAN |
| SELECT c FROM t1 ORDER BY a; |
| } {/.*B-TREE FOR ORDER BY.*/} |
| |
| # Ignore extraneous text parameters in the sqlite_stat1.stat field. |
| # |
| do_execsql_test 3.0 { |
| UPDATE sqlite_stat1 SET stat='12345 2 whatever=5 unordered xyzzy=11' |
| WHERE idx='t1a'; |
| ANALYZE sqlite_master; |
| SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; |
| } {4 5 6 # 7 8 9 # 4 8 12 #} |
| do_execsql_test 3.1 { |
| EXPLAIN QUERY PLAN |
| SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c; |
| } {~/.*USING INDEX.*/} |
| do_execsql_test 3.2 { |
| SELECT c FROM t1 ORDER BY a; |
| } {3 111 6 12 9 12} |
| do_execsql_test 3.3 { |
| EXPLAIN QUERY PLAN |
| SELECT c FROM t1 ORDER BY a; |
| } {~/.*USING INDEX.*/} |
| do_execsql_test 3.3x { |
| EXPLAIN QUERY PLAN |
| SELECT c FROM t1 ORDER BY a; |
| } {/.*B-TREE FOR ORDER BY.*/} |
| |
| # The sz=NNN parameter determines which index to scan |
| # |
| do_execsql_test 4.0 { |
| DROP INDEX t1a; |
| CREATE INDEX t1ab ON t1(a,b); |
| CREATE INDEX t1ca ON t1(c,a); |
| DELETE FROM sqlite_stat1; |
| INSERT INTO sqlite_stat1(tbl,idx,stat) |
| VALUES('t1','t1ab','12345 3 2 sz=10'),('t1','t1ca','12345 3 2 sz=20'); |
| ANALYZE sqlite_master; |
| SELECT count(a) FROM t1; |
| } {6} |
| do_execsql_test 4.1 { |
| EXPLAIN QUERY PLAN |
| SELECT count(a) FROM t1; |
| } {/.*INDEX t1ab.*/} |
| do_execsql_test 4.2 { |
| DELETE FROM sqlite_stat1; |
| INSERT INTO sqlite_stat1(tbl,idx,stat) |
| VALUES('t1','t1ab','12345 3 2 sz=20'),('t1','t1ca','12345 3 2 sz=10'); |
| ANALYZE sqlite_master; |
| SELECT count(a) FROM t1; |
| } {6} |
| do_execsql_test 4.3 { |
| EXPLAIN QUERY PLAN |
| SELECT count(a) FROM t1; |
| } {/.*INDEX t1ca.*/} |
| |
| |
| # The sz=NNN parameter works even if there is other extraneous text |
| # in the sqlite_stat1.stat column. |
| # |
| do_execsql_test 5.0 { |
| DELETE FROM sqlite_stat1; |
| INSERT INTO sqlite_stat1(tbl,idx,stat) |
| VALUES('t1','t1ab','12345 3 2 x=5 sz=10 y=10'), |
| ('t1','t1ca','12345 3 2 whatever sz=20 junk'); |
| ANALYZE sqlite_master; |
| SELECT count(a) FROM t1; |
| } {6} |
| do_execsql_test 5.1 { |
| EXPLAIN QUERY PLAN |
| SELECT count(a) FROM t1; |
| } {/.*INDEX t1ab.*/} |
| do_execsql_test 5.2 { |
| DELETE FROM sqlite_stat1; |
| INSERT INTO sqlite_stat1(tbl,idx,stat) |
| VALUES('t1','t1ca','12345 3 2 x=5 sz=10 y=10'), |
| ('t1','t1ab','12345 3 2 whatever sz=20 junk'); |
| ANALYZE sqlite_master; |
| SELECT count(a) FROM t1; |
| } {6} |
| do_execsql_test 5.3 { |
| EXPLAIN QUERY PLAN |
| SELECT count(a) FROM t1; |
| } {/.*INDEX t1ca.*/} |
| |
| |
| |
| |
| finish_test |