OLD | NEW |
1 # 2013-07-31 | 1 # 2013-07-31 |
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 47 matching lines...) Expand 10 before | Expand all | Expand 10 after Loading... |
58 } {1 {subqueries prohibited in partial index WHERE clauses}} | 58 } {1 {subqueries prohibited in partial index WHERE clauses}} |
59 do_test index6-1.4 { | 59 do_test index6-1.4 { |
60 catchsql { | 60 catchsql { |
61 CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1; | 61 CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1; |
62 } | 62 } |
63 } {1 {parameters prohibited in partial index WHERE clauses}} | 63 } {1 {parameters prohibited in partial index WHERE clauses}} |
64 do_test index6-1.5 { | 64 do_test index6-1.5 { |
65 catchsql { | 65 catchsql { |
66 CREATE INDEX bad1 ON t1(a,b) WHERE a!=random(); | 66 CREATE INDEX bad1 ON t1(a,b) WHERE a!=random(); |
67 } | 67 } |
68 } {1 {functions prohibited in partial index WHERE clauses}} | 68 } {1 {non-deterministic functions prohibited in partial index WHERE clauses}} |
69 do_test index6-1.6 { | 69 do_test index6-1.6 { |
70 catchsql { | 70 catchsql { |
71 CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%'; | 71 CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%'; |
72 } | 72 } |
73 } {1 {functions prohibited in partial index WHERE clauses}} | 73 } {0 {}} |
| 74 do_execsql_test index6-1.7 { |
| 75 DROP INDEX IF EXISTS bad1; |
| 76 } |
74 | 77 |
75 do_test index6-1.10 { | 78 do_test index6-1.10 { |
76 execsql { | 79 execsql { |
77 ANALYZE; | 80 ANALYZE; |
78 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; | 81 SELECT idx, stat FROM sqlite_stat1 ORDER BY idx; |
79 PRAGMA integrity_check; | 82 PRAGMA integrity_check; |
80 } | 83 } |
81 } {{} 20 t1a {14 1} t1b {10 1} ok} | 84 } {{} 20 t1a {14 1} t1b {10 1} ok} |
82 | 85 |
83 # STAT1 shows the partial indices have a reduced number of | 86 # STAT1 shows the partial indices have a reduced number of |
(...skipping 285 matching lines...) Expand 10 before | Expand all | Expand 10 after Loading... |
369 SELECT e FROM t10 WHERE c=3 AND 2=b AND a=1 ORDER BY d DESC; | 372 SELECT e FROM t10 WHERE c=3 AND 2=b AND a=1 ORDER BY d DESC; |
370 } {/USING INDEX t10x/} | 373 } {/USING INDEX t10x/} |
371 do_execsql_test index6-10.3 { | 374 do_execsql_test index6-10.3 { |
372 SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC; | 375 SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC; |
373 } {9 5} | 376 } {9 5} |
374 do_execsql_test index6-10.3eqp { | 377 do_execsql_test index6-10.3eqp { |
375 EXPLAIN QUERY PLAN | 378 EXPLAIN QUERY PLAN |
376 SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC; | 379 SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC; |
377 } {~/USING INDEX t10x/} | 380 } {~/USING INDEX t10x/} |
378 | 381 |
| 382 # A partial index will be used for a full table scan, where possible |
| 383 do_execsql_test index6-11.1 { |
| 384 CREATE TABLE t11(a,b,c); |
| 385 CREATE INDEX t11x ON t11(a) WHERE b<>99; |
| 386 EXPLAIN QUERY PLAN SELECT a FROM t11 WHERE b<>99; |
| 387 } {/USING INDEX t11x/} |
| 388 do_execsql_test index6-11.2 { |
| 389 EXPLAIN QUERY PLAN SELECT a FROM t11 WHERE b<>99 AND c<>98; |
| 390 } {/USING INDEX t11x/} |
| 391 |
| 392 |
379 finish_test | 393 finish_test |
OLD | NEW |