Annotation of embedaddon/sqlite3/test/indexedby.test, revision 1.1
1.1 ! misho 1: # 2008 October 4
! 2: #
! 3: # The author disclaims copyright to this source code. In place of
! 4: # a legal notice, here is a blessing:
! 5: #
! 6: # May you do good and not evil.
! 7: # May you find forgiveness for yourself and forgive others.
! 8: # May you share freely, never taking more than you give.
! 9: #
! 10: #***********************************************************************
! 11: #
! 12: # $Id: indexedby.test,v 1.5 2009/03/22 20:36:19 drh Exp $
! 13:
! 14: set testdir [file dirname $argv0]
! 15: source $testdir/tester.tcl
! 16:
! 17: # Create a schema with some indexes.
! 18: #
! 19: do_test indexedby-1.1 {
! 20: execsql {
! 21: CREATE TABLE t1(a, b);
! 22: CREATE INDEX i1 ON t1(a);
! 23: CREATE INDEX i2 ON t1(b);
! 24:
! 25: CREATE TABLE t2(c, d);
! 26: CREATE INDEX i3 ON t2(c);
! 27: CREATE INDEX i4 ON t2(d);
! 28:
! 29: CREATE TABLE t3(e PRIMARY KEY, f);
! 30:
! 31: CREATE VIEW v1 AS SELECT * FROM t1;
! 32: }
! 33: } {}
! 34:
! 35: # Explain Query Plan
! 36: #
! 37: proc EQP {sql} {
! 38: uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
! 39: }
! 40:
! 41: # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
! 42: #
! 43: do_execsql_test indexedby-1.2 {
! 44: EXPLAIN QUERY PLAN select * from t1 WHERE a = 10;
! 45: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}}
! 46: do_execsql_test indexedby-1.3 {
! 47: EXPLAIN QUERY PLAN select * from t1 ;
! 48: } {0 0 0 {SCAN TABLE t1 (~1000000 rows)}}
! 49: do_execsql_test indexedby-1.4 {
! 50: EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10;
! 51: } {
! 52: 0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)}
! 53: 0 1 0 {SCAN TABLE t1 (~1000000 rows)}
! 54: }
! 55:
! 56: # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be
! 57: # attached to a table in the FROM clause, but not to a sub-select or
! 58: # SQL view. Also test that specifying an index that does not exist or
! 59: # is attached to a different table is detected as an error.
! 60: #
! 61: do_test indexedby-2.1 {
! 62: execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
! 63: } {}
! 64: do_test indexedby-2.2 {
! 65: execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
! 66: } {}
! 67: do_test indexedby-2.3 {
! 68: execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'}
! 69: } {}
! 70:
! 71: do_test indexedby-2.4 {
! 72: catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'}
! 73: } {1 {no such index: i3}}
! 74: do_test indexedby-2.5 {
! 75: catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'}
! 76: } {1 {no such index: i5}}
! 77: do_test indexedby-2.6 {
! 78: catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'}
! 79: } {1 {near "WHERE": syntax error}}
! 80: do_test indexedby-2.7 {
! 81: catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
! 82: } {1 {no such index: i1}}
! 83:
! 84: # Tests for single table cases.
! 85: #
! 86: do_execsql_test indexedby-3.1 {
! 87: EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
! 88: } {0 0 0 {SCAN TABLE t1 (~10000 rows)}}
! 89: do_execsql_test indexedby-3.2 {
! 90: EXPLAIN QUERY PLAN
! 91: SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'
! 92: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
! 93: do_execsql_test indexedby-3.3 {
! 94: EXPLAIN QUERY PLAN
! 95: SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'
! 96: } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
! 97: do_test indexedby-3.4 {
! 98: catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
! 99: } {1 {cannot use index: i2}}
! 100: do_test indexedby-3.5 {
! 101: catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
! 102: } {1 {cannot use index: i2}}
! 103: do_test indexedby-3.6 {
! 104: catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
! 105: } {0 {}}
! 106: do_test indexedby-3.7 {
! 107: catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
! 108: } {0 {}}
! 109:
! 110: do_execsql_test indexedby-3.8 {
! 111: EXPLAIN QUERY PLAN
! 112: SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e
! 113: } {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1 (~1000000 rows)}}
! 114: do_execsql_test indexedby-3.9 {
! 115: EXPLAIN QUERY PLAN
! 116: SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10
! 117: } {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}}
! 118: do_test indexedby-3.10 {
! 119: catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
! 120: } {1 {cannot use index: sqlite_autoindex_t3_1}}
! 121: do_test indexedby-3.11 {
! 122: catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
! 123: } {1 {no such index: sqlite_autoindex_t3_2}}
! 124:
! 125: # Tests for multiple table cases.
! 126: #
! 127: do_execsql_test indexedby-4.1 {
! 128: EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c
! 129: } {
! 130: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
! 131: 0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)}
! 132: }
! 133: do_execsql_test indexedby-4.2 {
! 134: EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c
! 135: } {
! 136: 0 0 1 {SCAN TABLE t2 (~1000000 rows)}
! 137: 0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
! 138: }
! 139: do_test indexedby-4.3 {
! 140: catchsql {
! 141: SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c
! 142: }
! 143: } {1 {cannot use index: i1}}
! 144: do_test indexedby-4.4 {
! 145: catchsql {
! 146: SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c
! 147: }
! 148: } {1 {cannot use index: i3}}
! 149:
! 150: # Test embedding an INDEXED BY in a CREATE VIEW statement. This block
! 151: # also tests that nothing bad happens if an index refered to by
! 152: # a CREATE VIEW statement is dropped and recreated.
! 153: #
! 154: do_execsql_test indexedby-5.1 {
! 155: CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
! 156: EXPLAIN QUERY PLAN SELECT * FROM v2
! 157: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~250000 rows)}}
! 158: do_execsql_test indexedby-5.2 {
! 159: EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10
! 160: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~25000 rows)}}
! 161: do_test indexedby-5.3 {
! 162: execsql { DROP INDEX i1 }
! 163: catchsql { SELECT * FROM v2 }
! 164: } {1 {no such index: i1}}
! 165: do_test indexedby-5.4 {
! 166: # Recreate index i1 in such a way as it cannot be used by the view query.
! 167: execsql { CREATE INDEX i1 ON t1(b) }
! 168: catchsql { SELECT * FROM v2 }
! 169: } {1 {cannot use index: i1}}
! 170: do_test indexedby-5.5 {
! 171: # Drop and recreate index i1 again. This time, create it so that it can
! 172: # be used by the query.
! 173: execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
! 174: catchsql { SELECT * FROM v2 }
! 175: } {0 {}}
! 176:
! 177: # Test that "NOT INDEXED" may use the rowid index, but not others.
! 178: #
! 179: do_execsql_test indexedby-6.1 {
! 180: EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid
! 181: } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}}
! 182: do_execsql_test indexedby-6.2 {
! 183: EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid
! 184: } {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY (~100000 rows)}}
! 185:
! 186: # Test that "INDEXED BY" can be used in a DELETE statement.
! 187: #
! 188: do_execsql_test indexedby-7.1 {
! 189: EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5
! 190: } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
! 191: do_execsql_test indexedby-7.2 {
! 192: EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5
! 193: } {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
! 194: do_execsql_test indexedby-7.3 {
! 195: EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5
! 196: } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
! 197: do_execsql_test indexedby-7.4 {
! 198: EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
! 199: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
! 200: do_execsql_test indexedby-7.5 {
! 201: EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
! 202: } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
! 203: do_test indexedby-7.6 {
! 204: catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
! 205: } {1 {cannot use index: i2}}
! 206:
! 207: # Test that "INDEXED BY" can be used in an UPDATE statement.
! 208: #
! 209: do_execsql_test indexedby-8.1 {
! 210: EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5
! 211: } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
! 212: do_execsql_test indexedby-8.2 {
! 213: EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5
! 214: } {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
! 215: do_execsql_test indexedby-8.3 {
! 216: EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5
! 217: } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
! 218: do_execsql_test indexedby-8.4 {
! 219: EXPLAIN QUERY PLAN
! 220: UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10
! 221: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
! 222: do_execsql_test indexedby-8.5 {
! 223: EXPLAIN QUERY PLAN
! 224: UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10
! 225: } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
! 226: do_test indexedby-8.6 {
! 227: catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
! 228: } {1 {cannot use index: i2}}
! 229:
! 230: # Test that bug #3560 is fixed.
! 231: #
! 232: do_test indexedby-9.1 {
! 233: execsql {
! 234: CREATE TABLE maintable( id integer);
! 235: CREATE TABLE joinme(id_int integer, id_text text);
! 236: CREATE INDEX joinme_id_text_idx on joinme(id_text);
! 237: CREATE INDEX joinme_id_int_idx on joinme(id_int);
! 238: }
! 239: } {}
! 240: do_test indexedby-9.2 {
! 241: catchsql {
! 242: select * from maintable as m inner join
! 243: joinme as j indexed by joinme_id_text_idx
! 244: on ( m.id = j.id_int)
! 245: }
! 246: } {1 {cannot use index: joinme_id_text_idx}}
! 247: do_test indexedby-9.3 {
! 248: catchsql { select * from maintable, joinme INDEXED by joinme_id_text_idx }
! 249: } {1 {cannot use index: joinme_id_text_idx}}
! 250:
! 251: # Make sure we can still create tables, indices, and columns whose name
! 252: # is "indexed".
! 253: #
! 254: do_test indexedby-10.1 {
! 255: execsql {
! 256: CREATE TABLE indexed(x,y);
! 257: INSERT INTO indexed VALUES(1,2);
! 258: SELECT * FROM indexed;
! 259: }
! 260: } {1 2}
! 261: do_test indexedby-10.2 {
! 262: execsql {
! 263: CREATE INDEX i10 ON indexed(x);
! 264: SELECT * FROM indexed indexed by i10 where x>0;
! 265: }
! 266: } {1 2}
! 267: do_test indexedby-10.3 {
! 268: execsql {
! 269: DROP TABLE indexed;
! 270: CREATE TABLE t10(indexed INTEGER);
! 271: INSERT INTO t10 VALUES(1);
! 272: CREATE INDEX indexed ON t10(indexed);
! 273: SELECT * FROM t10 indexed by indexed WHERE indexed>0
! 274: }
! 275: } {1}
! 276:
! 277: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to indexedby.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test