Import SQLite 3.10.2.

third_party/sqlite/src/test/indexedby.test

-# 2008 October 4
+# 2008-10-04
 #
 # 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.
 #
 #***********************************************************************
 #
-# $Id: indexedby.test,v 1.5 2009/03/22 20:36:19 drh Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
 set ::testprefix indexedby
 
 # Create a schema with some indexes.
 #
 do_test indexedby-1.1 {
   execsql {
     CREATE TABLE t1(a, b);
@@ skipping 28 matching lines @@
   EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; 
 } {
   0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)} 
   0 1 0 {SCAN TABLE t1}
 }
 
 # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 
 # attached to a table in the FROM clause, but not to a sub-select or
 # SQL view. Also test that specifying an index that does not exist or
 # is attached to a different table is detected as an error.
+#
+# EVIDENCE-OF: R-07004-11522 -- syntax diagram qualified-table-name
 # 
+# EVIDENCE-OF: R-58230-57098 The "INDEXED BY index-name" phrase
+# specifies that the named index must be used in order to look up values
+# on the preceding table.
+#
 do_test indexedby-2.1 {
   execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
 } {}
+do_test indexedby-2.1b {
+  execsql { SELECT * FROM main.t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
+} {}
 do_test indexedby-2.2 {
   execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
 } {}
+do_test indexedby-2.2b {
+  execsql { SELECT * FROM main.t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
+} {}
 do_test indexedby-2.3 {
   execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'}
 } {}
-
+# EVIDENCE-OF: R-44699-55558 The INDEXED BY clause does not give the
+# optimizer hints about which index to use; it gives the optimizer a
+# requirement of which index to use.
+# EVIDENCE-OF: R-15800-25719 If index-name does not exist or cannot be
+# used for the query, then the preparation of the SQL statement fails.
+#
 do_test indexedby-2.4 {
   catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'}
 } {1 {no such index: i3}}
+
+# EVIDENCE-OF: R-62112-42456 If the query optimizer is unable to use the
+# index specified by the INDEX BY clause, then the query will fail with
+# an error.
+do_test indexedby-2.4.1 {
+  catchsql { SELECT b FROM t1 INDEXED BY i1 WHERE b = 'two' }
+} {1 {no query solution}}
+
 do_test indexedby-2.5 {
   catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'}
 } {1 {no such index: i5}}
 do_test indexedby-2.6 {
   catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'}
 } {1 {near "WHERE": syntax error}}
 do_test indexedby-2.7 {
   catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
 } {1 {no such index: i1}}
 
+
 # Tests for single table cases.
 #
+# EVIDENCE-OF: R-37002-28871 The "NOT INDEXED" clause specifies that no
+# index shall be used when accessing the preceding table, including
+# implied indices create by UNIQUE and PRIMARY KEY constraints. However,
+# the rowid can still be used to look up entries even when "NOT INDEXED"
+# is specified.
+#
 do_execsql_test indexedby-3.1 {
+  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a = 'one' AND b = 'two'
+} {/SEARCH TABLE t1 USING INDEX/}
+do_execsql_test indexedby-3.1.1 {
   EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
 } {0 0 0 {SCAN TABLE t1}}
+do_execsql_test indexedby-3.1.2 {
+  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE rowid=1
+} {/SEARCH TABLE t1 USING INTEGER PRIMARY KEY .rowid=/}
+
+
 do_execsql_test indexedby-3.2 {
   EXPLAIN QUERY PLAN 
   SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'
 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
 do_execsql_test indexedby-3.3 {
   EXPLAIN QUERY PLAN 
   SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'
 } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
 do_test indexedby-3.4 {
   catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
@@ skipping 77 matching lines @@
 
 # Test that "NOT INDEXED" may use the rowid index, but not others.
 # 
 do_execsql_test indexedby-6.1 {
   EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 
 } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
 do_execsql_test indexedby-6.2 {
   EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 
 } {0 0 0 {SCAN TABLE t1}}
 
+# EVIDENCE-OF: R-40297-14464 The INDEXED BY phrase forces the SQLite
+# query planner to use a particular named index on a DELETE, SELECT, or
+# UPDATE statement.
+#
 # Test that "INDEXED BY" can be used in a DELETE statement.
 # 
 do_execsql_test indexedby-7.1 {
   EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 
-} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
 do_execsql_test indexedby-7.2 {
   EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 
 } {0 0 0 {SCAN TABLE t1}}
 do_execsql_test indexedby-7.3 {
   EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 
-} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
+} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
 do_execsql_test indexedby-7.4 {
   EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
 } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
 do_execsql_test indexedby-7.5 {
   EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
 } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
 do_test indexedby-7.6 {
   catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
 } {1 {no query solution}}
 
@@ skipping 107 matching lines @@
   SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3';
 } {1 1 3}
 do_execsql_test 11.9 {
   SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
 } {1 1 3}
 do_eqp_test 11.10 {
   SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
 } {0 0 0 {SEARCH TABLE x2 USING COVERING INDEX x2i (a=? AND b=? AND rowid=?)}}
 
 finish_test