# 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 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.*/}

# 2019-08-15.

# Ticket https://sqlite.org/src/tktview/e4598ecbdd18bd82945f602901

# The sz=N parameter in the sqlite_stat1 table needs to have a value of

# 2 or more to avoid a division by zero in the query planner.

#

do_execsql_test 4.4 {

DROP TABLE IF EXISTS t44;

CREATE TABLE t44(a PRIMARY KEY);

INSERT INTO sqlite_stat1 VALUES('t44',null,'sz=0');

ANALYZE sqlite_master;

SELECT 0 FROM t44 WHERE a IN(1,2,3);

} {}

# 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