OLD | NEW |
1 # 2013-11-04 | 1 # 2013-11-04 |
2 # | 2 # |
3 # The author disclaims copyright to this source code. In place of | 3 # The author disclaims copyright to this source code. In place of |
4 # a legal notice, here is a blessing: | 4 # a legal notice, here is a blessing: |
5 # | 5 # |
6 # May you do good and not evil. | 6 # May you do good and not evil. |
7 # May you find forgiveness for yourself and forgive others. | 7 # May you find forgiveness for yourself and forgive others. |
8 # May you share freely, never taking more than you give. | 8 # May you share freely, never taking more than you give. |
9 # | 9 # |
10 #*********************************************************************** | 10 #*********************************************************************** |
(...skipping 81 matching lines...) Expand 10 before | Expand all | Expand 10 after Loading... |
92 } {1 {subqueries prohibited in partial index WHERE clauses}} | 92 } {1 {subqueries prohibited in partial index WHERE clauses}} |
93 do_test index7-1.4 { | 93 do_test index7-1.4 { |
94 catchsql { | 94 catchsql { |
95 CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1; | 95 CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1; |
96 } | 96 } |
97 } {1 {parameters prohibited in partial index WHERE clauses}} | 97 } {1 {parameters prohibited in partial index WHERE clauses}} |
98 do_test index7-1.5 { | 98 do_test index7-1.5 { |
99 catchsql { | 99 catchsql { |
100 CREATE INDEX bad1 ON t1(a,b) WHERE a!=random(); | 100 CREATE INDEX bad1 ON t1(a,b) WHERE a!=random(); |
101 } | 101 } |
102 } {1 {functions prohibited in partial index WHERE clauses}} | 102 } {1 {non-deterministic functions prohibited in partial index WHERE clauses}} |
103 do_test index7-1.6 { | 103 do_test index7-1.6 { |
104 catchsql { | 104 catchsql { |
105 CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%'; | 105 CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%'; |
106 } | 106 } |
107 } {1 {functions prohibited in partial index WHERE clauses}} | 107 } {0 {}} |
| 108 do_execsql_test index7-1.7 { |
| 109 INSERT INTO t1(a,b,c) |
| 110 VALUES('abcde',1,101),('abdef',2,102),('xyz',3,103),('abcz',4,104); |
| 111 SELECT c FROM t1 WHERE a NOT LIKE 'abc%' AND a=7 ORDER BY +b; |
| 112 } {7} |
| 113 do_execsql_test index7-1.7eqp { |
| 114 EXPLAIN QUERY PLAN |
| 115 SELECT b FROM t1 WHERE a NOT LIKE 'abc%' AND a=7 ORDER BY +b; |
| 116 } {/SEARCH TABLE t1 USING COVERING INDEX bad1 /} |
| 117 do_execsql_test index7-1.8 { |
| 118 DELETE FROM t1 WHERE c>=101; |
| 119 DROP INDEX IF EXISTS bad1; |
| 120 } {} |
108 | 121 |
109 do_test index7-1.10 { | 122 do_test index7-1.10 { |
110 execsql { | 123 execsql { |
111 ANALYZE; | 124 ANALYZE; |
112 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; | 125 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; |
113 PRAGMA integrity_check; | 126 PRAGMA integrity_check; |
114 } | 127 } |
115 } {t1 {20 1} t1a {14 1} t1b {10 1} ok} | 128 } {t1 {20 1} t1a {14 1} t1b {10 1} ok} |
116 | 129 |
117 # STAT1 shows the partial indices have a reduced number of | 130 # STAT1 shows the partial indices have a reduced number of |
(...skipping 192 matching lines...) Expand 10 before | Expand all | Expand 10 after Loading... |
310 SELECT * FROM v4 WHERE d='xyz' AND c='def' | 323 SELECT * FROM v4 WHERE d='xyz' AND c='def' |
311 } { | 324 } { |
312 0 0 0 {SEARCH TABLE t4 USING INDEX i4 (c=?)} | 325 0 0 0 {SEARCH TABLE t4 USING INDEX i4 (c=?)} |
313 } | 326 } |
314 do_catchsql_test index7-6.5 { | 327 do_catchsql_test index7-6.5 { |
315 CREATE INDEX t5a ON t5(a) WHERE a=#1; | 328 CREATE INDEX t5a ON t5(a) WHERE a=#1; |
316 } {1 {near "#1": syntax error}} | 329 } {1 {near "#1": syntax error}} |
317 | 330 |
318 | 331 |
319 finish_test | 332 finish_test |
OLD | NEW |