test/index7.test

   1 # 2013-11-04
   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 # Test cases for partial indices in WITHOUT ROWID tables
  13 #
  14
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18
  19 ifcapable !vtab {
  20   finish_test
  21   return
  22 }
  23
  24 # Capture the output of a pragma in a TEMP table.
  25 #
  26 proc capture_pragma {db tabname sql} {
  27   $db eval "DROP TABLE IF EXISTS temp.$tabname"
  28   set once 1
  29   $db eval $sql x {
  30     if {$once} {
  31       set once 0
  32       set ins "INSERT INTO $tabname VALUES"
  33       set crtab "CREATE TEMP TABLE $tabname "
  34       set sep "("
  35       foreach col $x(*) {
  36         append ins ${sep}\$x($col)
  37         append crtab ${sep}\"$col\"
  38         set sep ,
  39       }
  40       append ins )
  41       append crtab )
  42       $db eval $crtab
  43     }
  44     $db eval $ins
  45   }
  46 }
  47
  48
  49 load_static_extension db wholenumber;
  50 do_test index7-1.1 {
  51   # Able to parse and manage partial indices
  52   execsql {
  53     CREATE TABLE t1(a,b,c PRIMARY KEY) WITHOUT rowid;
  54     CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL;
  55     CREATE INDEX t1b ON t1(b) WHERE b>10;
  56     CREATE VIRTUAL TABLE nums USING wholenumber;
  57     INSERT INTO t1(a,b,c)
  58        SELECT CASE WHEN value%3!=0 THEN value END, value, value
  59          FROM nums WHERE value<=20;
  60     SELECT count(a), count(b) FROM t1;
  61     PRAGMA integrity_check;
  62   }
  63 } {14 20 ok}
  64
  65 # (The "partial" column of the PRAGMA index_list output is...)
  66 # EVIDENCE-OF: R-34457-09668 "1" if the index is a partial index and "0"
  67 # if not.
  68 #
  69 do_test index7-1.1a {
  70   capture_pragma db out {PRAGMA index_list(t1)}
  71   db eval {SELECT "name", "partial", '|' FROM out ORDER BY "name"}
  72 } {sqlite_autoindex_t1_1 0 | t1a 1 | t1b 1 |}
  73
  74 # Make sure the count(*) optimization works correctly with
  75 # partial indices.  Ticket [a5c8ed66cae16243be6] 2013-10-03.
  76 #
  77 do_execsql_test index7-1.1.1 {
  78   SELECT count(*) FROM t1;
  79 } {20}
  80
  81 # Error conditions during parsing...
  82 #
  83 do_test index7-1.2 {
  84   catchsql {
  85     CREATE INDEX bad1 ON t1(a,b) WHERE x IS NOT NULL;
  86   }
  87 } {1 {no such column: x}}
  88 do_test index7-1.3 {
  89   catchsql {
  90     CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1);
  91   }
  92 } {1 {subqueries prohibited in partial index WHERE clauses}}
  93 do_test index7-1.4 {
  94   catchsql {
  95     CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
  96   }
  97 } {1 {parameters prohibited in partial index WHERE clauses}}
  98 do_test index7-1.5 {
  99   catchsql {
 100     CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
 101   }
 102 } {1 {non-deterministic functions prohibited in partial index WHERE clauses}}
 103 do_test index7-1.6 {
 104   catchsql {
 105     CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
 106   }
 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 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 } {}
 121
 122 do_test index7-1.10 {
 123   execsql {
 124     ANALYZE;
 125     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
 126     PRAGMA integrity_check;
 127   }
 128 } {t1 {20 1} t1a {14 1} t1b {10 1} ok}
 129
 130 # STAT1 shows the partial indices have a reduced number of
 131 # rows.
 132 #
 133 do_test index7-1.11 {
 134   execsql {
 135     UPDATE t1 SET a=b;
 136     ANALYZE;
 137     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
 138     PRAGMA integrity_check;
 139   }
 140 } {t1 {20 1} t1a {20 1} t1b {10 1} ok}
 141
 142 do_test index7-1.11b {
 143   execsql {
 144     UPDATE t1 SET a=NULL WHERE b%3!=0;
 145     UPDATE t1 SET b=b+100;
 146     ANALYZE;
 147     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
 148     PRAGMA integrity_check;
 149   }
 150 } {t1 {20 1} t1a {6 1} t1b {20 1} ok}
 151
 152 do_test index7-1.12 {
 153   execsql {
 154     UPDATE t1 SET a=CASE WHEN b%3!=0 THEN b END;
 155     UPDATE t1 SET b=b-100;
 156     ANALYZE;
 157     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
 158     PRAGMA integrity_check;
 159   }
 160 } {t1 {20 1} t1a {13 1} t1b {10 1} ok}
 161
 162 do_test index7-1.13 {
 163   execsql {
 164     DELETE FROM t1 WHERE b BETWEEN 8 AND 12;
 165     ANALYZE;
 166     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
 167     PRAGMA integrity_check;
 168   }
 169 } {t1 {15 1} t1a {10 1} t1b {8 1} ok}
 170
 171 do_test index7-1.14 {
 172   execsql {
 173     REINDEX;
 174     ANALYZE;
 175     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
 176     PRAGMA integrity_check;
 177   }
 178 } {t1 {15 1} t1a {10 1} t1b {8 1} ok}
 179
 180 do_test index7-1.15 {
 181   execsql {
 182     CREATE INDEX t1c ON t1(c);
 183     ANALYZE;
 184     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
 185     PRAGMA integrity_check;
 186   }
 187 } {t1 {15 1} t1a {10 1} t1b {8 1} t1c {15 1} ok}
 188
 189 # Queries use partial indices at appropriate times.
 190 #
 191 do_test index7-2.1 {
 192   execsql {
 193     CREATE TABLE t2(a,b PRIMARY KEY) without rowid;
 194     INSERT INTO t2(a,b) SELECT value, value FROM nums WHERE value<1000;
 195     UPDATE t2 SET a=NULL WHERE b%5==0;
 196     CREATE INDEX t2a1 ON t2(a) WHERE a IS NOT NULL;
 197     SELECT count(*) FROM t2 WHERE a IS NOT NULL;
 198   }
 199 } {800}
 200 do_test index7-2.2 {
 201   execsql {
 202     EXPLAIN QUERY PLAN
 203     SELECT * FROM t2 WHERE a=5;
 204   }
 205 } {/(SCAN|SEARCH) t2 USING COVERING INDEX t2a1 /}
 206 ifcapable stat4 {
 207   do_test index7-2.3stat4 {
 208     execsql {
 209       EXPLAIN QUERY PLAN
 210       SELECT * FROM t2 WHERE a IS NOT NULL;
 211     }
 212   } {/(SCAN|SEARCH) t2 USING COVERING INDEX t2a1 /}
 213 } else {
 214   do_test index7-2.3stat4 {
 215     execsql {
 216       EXPLAIN QUERY PLAN
 217       SELECT * FROM t2 WHERE a IS NOT NULL AND a>0;
 218     }
 219   } {/(SCAN|SEARCH) t2 USING COVERING INDEX t2a1 /}
 220 }
 221 do_test index7-2.4 {
 222   execsql {
 223     EXPLAIN QUERY PLAN
 224     SELECT * FROM t2 WHERE a IS NULL;
 225   }
 226 } {~/INDEX t2a1/}
 227
 228 do_execsql_test index7-2.101 {
 229   DROP INDEX t2a1;
 230   UPDATE t2 SET a=b, b=b+10000;
 231   SELECT b FROM t2 WHERE a=15;
 232 } {10015}
 233 do_execsql_test index7-2.102 {
 234   CREATE INDEX t2a2 ON t2(a) WHERE a<100 OR a>200;
 235   SELECT b FROM t2 WHERE a=15;
 236   PRAGMA integrity_check;
 237 } {10015 ok}
 238 do_execsql_test index7-2.102eqp {
 239   EXPLAIN QUERY PLAN
 240   SELECT b FROM t2 WHERE a=15;
 241 } {~/.*INDEX t2a2.*/}
 242 do_execsql_test index7-2.103 {
 243   SELECT b FROM t2 WHERE a=15 AND a<100;
 244 } {10015}
 245 do_execsql_test index7-2.103eqp {
 246   EXPLAIN QUERY PLAN
 247   SELECT b FROM t2 WHERE a=15 AND a<100;
 248 } {/.*INDEX t2a2.*/}
 249 do_execsql_test index7-2.104 {
 250   SELECT b FROM t2 WHERE a=515 AND a>200;
 251 } {10515}
 252 do_execsql_test index7-2.104eqp {
 253   EXPLAIN QUERY PLAN
 254   SELECT b FROM t2 WHERE a=515 AND a>200;
 255 } {/.*INDEX t2a2.*/}
 256
 257 # Partial UNIQUE indices
 258 #
 259 do_execsql_test index7-3.1 {
 260   CREATE TABLE t3(a,b PRIMARY KEY) without rowid;
 261   INSERT INTO t3 SELECT value, value FROM nums WHERE value<200;
 262   UPDATE t3 SET a=999 WHERE b%5!=0;
 263   CREATE UNIQUE INDEX t3a ON t3(a) WHERE a<>999;
 264 } {}
 265 do_test index7-3.2 {
 266   # unable to insert a duplicate row a-value that is not 999.
 267   catchsql {
 268     INSERT INTO t3(a,b) VALUES(150, 'test1');
 269   }
 270 } {1 {UNIQUE constraint failed: t3.a}}
 271 do_test index7-3.3 {
 272   # can insert multiple rows with a==999 because such rows are not
 273   # part of the unique index.
 274   catchsql {
 275     INSERT INTO t3(a,b) VALUES(999, 'test1'), (999, 'test2');
 276   }
 277 } {0 {}}
 278 do_execsql_test index7-3.4 {
 279   SELECT count(*) FROM t3 WHERE a=999;
 280 } {162}
 281 integrity_check index7-3.5
 282
 283 do_execsql_test index7-4.0 {
 284   VACUUM;
 285   PRAGMA integrity_check;
 286 } {ok}
 287
 288 # Silently ignore database name qualifiers in partial indices.
 289 #
 290 do_execsql_test index7-5.0 {
 291   CREATE INDEX t3b ON t3(b) WHERE xyzzy.t3.b BETWEEN 5 AND 10;
 292                                /* ^^^^^-- ignored */
 293   ANALYZE;
 294   SELECT count(*) FROM t3 WHERE t3.b BETWEEN 5 AND 10;
 295   SELECT stat+0 FROM sqlite_stat1 WHERE idx='t3b';
 296 } {6 6}
 297
 298 # Verify that the problem identified by ticket [98d973b8f5] has been fixed.
 299 #
 300 do_execsql_test index7-6.1 {
 301   CREATE TABLE t5(a, b);
 302   CREATE TABLE t4(c, d);
 303   INSERT INTO t5 VALUES(1, 'xyz');
 304   INSERT INTO t4 VALUES('abc', 'not xyz');
 305   SELECT * FROM (SELECT * FROM t5 WHERE a=1 AND b='xyz'), t4 WHERE c='abc';
 306 } {
 307   1 xyz abc {not xyz}
 308 }
 309 do_execsql_test index7-6.2 {
 310   CREATE INDEX i4 ON t4(c) WHERE d='xyz';
 311   SELECT * FROM (SELECT * FROM t5 WHERE a=1 AND b='xyz'), t4 WHERE c='abc';
 312 } {
 313   1 xyz abc {not xyz}
 314 }
 315 do_execsql_test index7-6.3 {
 316   CREATE VIEW v4 AS SELECT * FROM t4;
 317   INSERT INTO t4 VALUES('def', 'xyz');
 318   SELECT * FROM v4 WHERE d='xyz' AND c='def'
 319 } {
 320   def xyz
 321 }
 322 do_eqp_test index7-6.4 {
 323   SELECT * FROM v4 WHERE d='xyz' AND c='def'
 324 } {SEARCH t4 USING INDEX i4 (c=?)}
 325
 326 do_catchsql_test index7-6.5 {
 327   CREATE INDEX t5a ON t5(a) WHERE a=#1;
 328 } {1 {near "#1": syntax error}}
 329
 330 do_execsql_test index7-7.0 {
 331   CREATE TABLE t6(x, y);
 332   INSERT INTO t6 VALUES(1, 1);
 333   INSERT INTO t6 VALUES(0, 0);
 334   SELECT * FROM t6 WHERE y IS TRUE ORDER BY x;
 335 } {1 1}
 336
 337 do_execsql_test index7-7.1 {
 338   CREATE INDEX i6 ON t6(x) WHERE y IS NOT TRUE;
 339   SELECT * FROM t6 WHERE y IS TRUE ORDER BY x;
 340 } {1 1}
 341
 342 # 2020-05-27.  tag-20200527-1.
 343 # Incomplete stat1 information on a table with few rows should still use the
 344 # index.
 345 reset_db
 346 do_execsql_test index7-8.1 {
 347   CREATE TABLE t1(x INTEGER PRIMARY KEY, y);
 348   CREATE INDEX t1y ON t1(y) WHERE y IS NOT NULL;
 349   INSERT INTO t1(x) VALUES(1),(2);
 350   ANALYZE;
 351   EXPLAIN QUERY PLAN SELECT 1 FROM t1 WHERE y=5;
 352 } {/SEARCH t1 USING COVERING INDEX t1y/}
 353
 354
 355 finish_test