SQLite

Check-in [bb51c34506]
Login

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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bb51c34506b3353506b6f3566fbe2a10d02e8ebc
User & Date: danielk1977 2008-10-06 11:29:49.000
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: 693503e241 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: bb51c34506 user: danielk1977 tags: trunk)
05:32
Allow INDEXED BY and NOT INDEXED clauses in SELECT statements. (CVS 5766) (check-in: 98ca5580f5 user: danielk1977 tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to test/indexedby.test.
1
2
3
4
5
6
7
8
9
10
11
12

13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


28
29
30
31
32
33
34
1
2
3
4
5
6
7
8
9
10
11

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36











-
+















+
+







# 2008 October 4
#
# 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.1 2008/10/06 05:32:19 danielk1977 Exp $
# $Id: indexedby.test,v 1.2 2008/10/06 11:29:49 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a schema with some indexes.
#
do_test indexedby-1.1 {
  execsql {
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(a);
    CREATE INDEX i2 ON t1(b);

    CREATE TABLE t2(c, d);
    CREATE INDEX i3 ON t2(c);
    CREATE INDEX i4 ON t2(d);

    CREATE TABLE t3(e PRIMARY KEY, f);

    CREATE VIEW v1 AS SELECT * FROM t1;
  }
} {}

# Explain Query Plan
#
96
97
98
99
100
101
102













103
104
105
106
107
108
109
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124







+
+
+
+
+
+
+
+
+
+
+
+
+







do_test indexedby-3.6 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
} {0 {}}
do_test indexedby-3.7 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
} {0 {}}

do_test indexedby-3.8 {
  EQP { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e }
} {0 0 {TABLE t3 WITH INDEX sqlite_autoindex_t3_1 ORDER BY}}
do_test indexedby-3.9 {
  EQP { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 }
} {0 0 {TABLE t3 WITH INDEX sqlite_autoindex_t3_1}}
do_test indexedby-3.10 {
  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
} {1 {cannot use index: sqlite_autoindex_t3_1}}
do_test indexedby-3.11 {
  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
} {1 {no such index: sqlite_autoindex_t3_2}}

# Tests for multiple table cases.
#
do_test indexedby-4.1 {
  EQP { SELECT * FROM t1, t2 WHERE a = c }
} {0 0 {TABLE t1} 1 1 {TABLE t2 WITH INDEX i3}}
do_test indexedby-4.2 {
  EQP { SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c }