/ Check-in [bb51c345]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add a test to indexedby.test to check that automatic indexes (sqlite_autoindex_xxx) can be used with the INDEXED BY syntax. (CVS 5767)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bb51c34506b3353506b6f3566fbe2a10d02e8ebc
User & Date: danielk1977 2008-10-06 11:29:49
Context
2008-10-06
12:46
In sqlite3_bind() interfaces, avoid acquiring the mutex until after the statement handle has been validated. Ticket #3418. (CVS 5768) check-in: 693503e2 user: drh tags: trunk
11:29
Add a test to indexedby.test to check that automatic indexes (sqlite_autoindex_xxx) can be used with the INDEXED BY syntax. (CVS 5767) check-in: bb51c345 user: danielk1977 tags: trunk
05:32
Allow INDEXED BY and NOT INDEXED clauses in SELECT statements. (CVS 5766) check-in: 98ca5580 user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/indexedby.test.

     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12         -# $Id: indexedby.test,v 1.1 2008/10/06 05:32:19 danielk1977 Exp $
           12  +# $Id: indexedby.test,v 1.2 2008/10/06 11:29:49 danielk1977 Exp $
    13     13   
    14     14   set testdir [file dirname $argv0]
    15     15   source $testdir/tester.tcl
    16     16   
    17     17   # Create a schema with some indexes.
    18     18   #
    19     19   do_test indexedby-1.1 {
................................................................................
    21     21       CREATE TABLE t1(a, b);
    22     22       CREATE INDEX i1 ON t1(a);
    23     23       CREATE INDEX i2 ON t1(b);
    24     24   
    25     25       CREATE TABLE t2(c, d);
    26     26       CREATE INDEX i3 ON t2(c);
    27     27       CREATE INDEX i4 ON t2(d);
           28  +
           29  +    CREATE TABLE t3(e PRIMARY KEY, f);
    28     30   
    29     31       CREATE VIEW v1 AS SELECT * FROM t1;
    30     32     }
    31     33   } {}
    32     34   
    33     35   # Explain Query Plan
    34     36   #
................................................................................
    96     98   do_test indexedby-3.6 {
    97     99     catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
    98    100   } {0 {}}
    99    101   do_test indexedby-3.7 {
   100    102     catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
   101    103   } {0 {}}
   102    104   
          105  +do_test indexedby-3.8 {
          106  +  EQP { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e }
          107  +} {0 0 {TABLE t3 WITH INDEX sqlite_autoindex_t3_1 ORDER BY}}
          108  +do_test indexedby-3.9 {
          109  +  EQP { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 }
          110  +} {0 0 {TABLE t3 WITH INDEX sqlite_autoindex_t3_1}}
          111  +do_test indexedby-3.10 {
          112  +  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
          113  +} {1 {cannot use index: sqlite_autoindex_t3_1}}
          114  +do_test indexedby-3.11 {
          115  +  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
          116  +} {1 {no such index: sqlite_autoindex_t3_2}}
          117  +
   103    118   # Tests for multiple table cases.
   104    119   #
   105    120   do_test indexedby-4.1 {
   106    121     EQP { SELECT * FROM t1, t2 WHERE a = c }
   107    122   } {0 0 {TABLE t1} 1 1 {TABLE t2 WITH INDEX i3}}
   108    123   do_test indexedby-4.2 {
   109    124     EQP { SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c }