From 569c6676a6ddb0ff73821d7693b5e18ddef809b9 Mon Sep 17 00:00:00 2001
From: Hans-Christoph Steiner <hans@eds.org>
Date: Thu, 16 Oct 2014 22:51:35 -0400
Subject: Imported Upstream version 3.2.0

---
 test/analyzeC.test | 167 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 167 insertions(+)
 create mode 100644 test/analyzeC.test

(limited to 'test/analyzeC.test')

diff --git a/test/analyzeC.test b/test/analyzeC.test
new file mode 100644
index 0000000..02faa9c
--- /dev/null
+++ b/test/analyzeC.test
@@ -0,0 +1,167 @@
+# 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
-- 
cgit v1.2.3