From 08119c361d1181b3e8f1abb429236e488a664753 Mon Sep 17 00:00:00 2001
From: Hans-Christoph Steiner <hans@eds.org>
Date: Tue, 13 Aug 2013 15:42:54 -0400
Subject: Imported Upstream version 2.2.1

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

(limited to 'test/coveridxscan.test')

diff --git a/test/coveridxscan.test b/test/coveridxscan.test
new file mode 100644
index 0000000..7b3c0b0
--- /dev/null
+++ b/test/coveridxscan.test
@@ -0,0 +1,93 @@
+# 2012 September 17
+#
+# 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.
+#
+#***********************************************************************
+#
+# Tests for the optimization which attempts to use a covering index
+# for a full-table scan (under the theory that the index will be smaller
+# and require less I/O and hence will run faster.)
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+set testprefix coveridxscan
+
+do_test 1.1 {
+  db eval {
+    CREATE TABLE t1(a,b,c);
+    INSERT INTO t1 VALUES(5,4,3), (4,8,2), (3,2,1);
+    CREATE INDEX t1ab ON t1(a,b);
+    CREATE INDEX t1b ON t1(b);
+    SELECT a FROM t1;
+  }
+  # covering index used for the scan, hence values are increasing
+} {3 4 5}
+
+do_test 1.2 {
+  db eval {
+    SELECT a, c FROM t1;
+  }
+  # There is no covering index, hence the values are in rowid order
+} {5 3 4 2 3 1}
+
+do_test 1.3 {
+  db eval {
+    SELECT b FROM t1;
+  }
+  # Choice of two indices: use the one with fewest columns
+} {2 4 8}
+
+do_test 2.1 {
+  optimization_control db cover-idx-scan 0
+  db eval {SELECT a FROM t1}
+  # With the optimization turned off, output in rowid order
+} {5 4 3}
+do_test 2.2 {
+  db eval {SELECT a, c FROM t1}
+} {5 3 4 2 3 1}
+do_test 2.3 {
+  db eval {SELECT b FROM t1}
+} {4 8 2}
+
+db close
+sqlite3_shutdown
+sqlite3_config_cis 0
+sqlite3 db test.db
+
+do_test 3.1 {
+  db eval {SELECT a FROM t1}
+  # With the optimization configured off, output in rowid order
+} {5 4 3}
+do_test 3.2 {
+  db eval {SELECT a, c FROM t1}
+} {5 3 4 2 3 1}
+do_test 3.3 {
+  db eval {SELECT b FROM t1}
+} {4 8 2}
+
+db close
+sqlite3_shutdown
+sqlite3_config_cis 1
+sqlite3 db test.db
+
+# The CIS optimization is enabled again.  Covering indices are once again
+# used for all table scans.
+do_test 4.1 {
+  db eval {SELECT a FROM t1}
+} {3 4 5}
+do_test 4.2 {
+  db eval {SELECT a, c FROM t1}
+} {5 3 4 2 3 1}
+do_test 4.3 {
+  db eval {SELECT b FROM t1}
+} {2 4 8}
+
+
+finish_test
-- 
cgit v1.2.3