test/indexA.test

   1 # 2023 September 23
   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 #
  13
  14 set testdir [file dirname $argv0]
  15 source $testdir/tester.tcl
  16 set testprefix indexA
  17
  18 do_execsql_test 1.0 {
  19   CREATE TABLE t1(a TEXT, b, c);
  20   CREATE INDEX i1 ON t1(b, c) WHERE a='abc';
  21   INSERT INTO t1 VALUES('abc', 1, 2);
  22 }
  23
  24 do_execsql_test 1.1 {
  25   SELECT * FROM t1 WHERE a='abc'
  26 } {abc 1 2}
  27
  28 do_eqp_test 1.2 {
  29   SELECT * FROM t1 WHERE a='abc'
  30 } {USING COVERING INDEX i1}
  31
  32 do_execsql_test 1.3 {
  33   CREATE INDEX i2 ON t1(b, c) WHERE a=5;
  34   INSERT INTO t1 VALUES(5, 4, 3);
  35
  36   SELECT a, typeof(a), b, c FROM t1 WHERE a=5;
  37 } {5 text 4 3}
  38
  39 do_execsql_test 1.4 {
  40   CREATE TABLE t2(x);
  41   INSERT INTO t2 VALUES('v');
  42 }
  43
  44 do_execsql_test 1.5 {
  45   SELECT x, a, b, c FROM t2 LEFT JOIN t1 ON (a=5 AND b=x)
  46 } {v {} {} {}}
  47
  48 do_execsql_test 1.6 {
  49   SELECT x, a, b, c FROM t2 RIGHT JOIN t1 ON (t1.a=5 AND t1.b=t2.x)
  50 } {{} abc 1 2   {} 5 4 3}
  51
  52 do_eqp_test 1.7 {
  53   SELECT x, a, b, c FROM t2 RIGHT JOIN t1 ON (t1.a=5 AND t1.b=t2.x)
  54 } {USING INDEX i2}
  55
  56 #-------------------------------------------------------------------------
  57 reset_db
  58
  59 do_execsql_test 2.0 {
  60   CREATE TABLE x1(a TEXT, b, c);
  61   INSERT INTO x1 VALUES('2', 'two', 'ii');
  62   INSERT INTO x1 VALUES('2.0', 'twopointoh', 'ii.0');
  63
  64   CREATE TABLE x2(a NUMERIC, b, c);
  65   INSERT INTO x2 VALUES('2', 'two', 'ii');
  66   INSERT INTO x2 VALUES('2.0', 'twopointoh', 'ii.0');
  67
  68   CREATE TABLE x3(a REAL, b, c);
  69   INSERT INTO x3 VALUES('2', 'two', 'ii');
  70   INSERT INTO x3 VALUES('2.0', 'twopointoh', 'ii.0');
  71 }
  72
  73 foreach {tn idx} {
  74   0 {
  75   }
  76   1 {
  77     CREATE INDEX i1 ON x1(b, c) WHERE a=2;
  78     CREATE INDEX i2 ON x2(b, c) WHERE a=2;
  79     CREATE INDEX i3 ON x3(b, c) WHERE a=2;
  80   }
  81   2 {
  82     CREATE INDEX i1 ON x1(b, c) WHERE a=2.0;
  83     CREATE INDEX i2 ON x2(b, c) WHERE a=2.0;
  84     CREATE INDEX i3 ON x3(b, c) WHERE a=2.0;
  85   }
  86   3 {
  87     CREATE INDEX i1 ON x1(b, c) WHERE a='2.0';
  88     CREATE INDEX i2 ON x2(b, c) WHERE a='2.0';
  89     CREATE INDEX i3 ON x3(b, c) WHERE a='2.0';
  90   }
  91   4 {
  92     CREATE INDEX i1 ON x1(b, c) WHERE a='2';
  93     CREATE INDEX i2 ON x2(b, c) WHERE a='2';
  94     CREATE INDEX i3 ON x3(b, c) WHERE a='2';
  95   }
  96 } {
  97   execsql { DROP INDEX IF EXISTS i1 }
  98   execsql { DROP INDEX IF EXISTS i2 }
  99   execsql { DROP INDEX IF EXISTS i3 }
 100
 101   execsql $idx
 102   do_execsql_test 2.1.$tn.1 {
 103     SELECT *, typeof(a) FROM x1 WHERE a=2
 104   } {2 two ii text}
 105   do_execsql_test 2.1.$tn.2 {
 106     SELECT *, typeof(a) FROM x1 WHERE a=2.0
 107   } {2.0 twopointoh ii.0 text}
 108   do_execsql_test 2.1.$tn.3 {
 109     SELECT *, typeof(a) FROM x1 WHERE a='2'
 110   } {2 two ii text}
 111   do_execsql_test 2.1.$tn.4 {
 112     SELECT *, typeof(a) FROM x1 WHERE a='2.0'
 113   } {2.0 twopointoh ii.0 text}
 114
 115   do_execsql_test 2.1.$tn.5 {
 116     SELECT *, typeof(a) FROM x2 WHERE a=2
 117   } {2 two ii integer 2 twopointoh ii.0 integer}
 118   do_execsql_test 2.1.$tn.6 {
 119     SELECT *, typeof(a) FROM x2 WHERE a=2.0
 120   } {2 two ii integer 2 twopointoh ii.0 integer}
 121   do_execsql_test 2.1.$tn.7 {
 122     SELECT *, typeof(a) FROM x2 WHERE a='2'
 123   } {2 two ii integer 2 twopointoh ii.0 integer}
 124   do_execsql_test 2.1.$tn.8 {
 125     SELECT *, typeof(a) FROM x2 WHERE a='2.0'
 126   } {2 two ii integer 2 twopointoh ii.0 integer}
 127
 128   do_execsql_test 2.1.$tn.9 {
 129     SELECT *, typeof(a) FROM x3 WHERE a=2
 130   } {2.0 two ii real 2.0 twopointoh ii.0 real}
 131   do_execsql_test 2.1.$tn.10 {
 132     SELECT *, typeof(a) FROM x3 WHERE a=2.0
 133   } {2.0 two ii real 2.0 twopointoh ii.0 real}
 134   do_execsql_test 2.1.$tn.11 {
 135     SELECT *, typeof(a) FROM x3 WHERE a='2'
 136   } {2.0 two ii real 2.0 twopointoh ii.0 real}
 137   do_execsql_test 2.1.$tn.12 {
 138     SELECT *, typeof(a) FROM x3 WHERE a='2.0'
 139   } {2.0 two ii real 2.0 twopointoh ii.0 real}
 140
 141 }
 142
 143 reset_db
 144 do_execsql_test 3.0 {
 145   CREATE TABLE x1(a TEXT, d PRIMARY KEY, b, c) WITHOUT ROWID;
 146   INSERT INTO x1 VALUES('2', 1, 'two', 'ii');
 147   INSERT INTO x1 VALUES('2.0', 2, 'twopointoh', 'ii.0');
 148
 149   CREATE TABLE x2(a NUMERIC, b, c, d PRIMARY KEY) WITHOUT ROWID;
 150   INSERT INTO x2 VALUES('2', 'two', 'ii', 1);
 151   INSERT INTO x2 VALUES('2.0', 'twopointoh', 'ii.0', 2);
 152
 153   CREATE TABLE x3(d PRIMARY KEY, a REAL, b, c) WITHOUT ROWID;
 154   INSERT INTO x3 VALUES(34, '2', 'two', 'ii');
 155   INSERT INTO x3 VALUES(35, '2.0', 'twopointoh', 'ii.0');
 156 }
 157
 158 foreach {tn idx} {
 159   0 {
 160   }
 161   1 {
 162     CREATE INDEX i1 ON x1(b, c) WHERE a=2;
 163     CREATE INDEX i2 ON x2(b, c) WHERE a=2;
 164     CREATE INDEX i3 ON x3(b, c) WHERE a=2;
 165   }
 166   2 {
 167     CREATE INDEX i1 ON x1(b, c) WHERE a=2.0;
 168     CREATE INDEX i2 ON x2(b, c) WHERE a=2.0;
 169     CREATE INDEX i3 ON x3(b, c) WHERE a=2.0;
 170   }
 171   3 {
 172     CREATE INDEX i1 ON x1(b, c) WHERE a='2.0';
 173     CREATE INDEX i2 ON x2(b, c) WHERE a='2.0';
 174     CREATE INDEX i3 ON x3(b, c) WHERE a='2.0';
 175   }
 176   4 {
 177     CREATE INDEX i1 ON x1(b, c) WHERE a='2';
 178     CREATE INDEX i2 ON x2(b, c) WHERE a='2';
 179     CREATE INDEX i3 ON x3(b, c) WHERE a='2';
 180   }
 181 } {
 182   execsql { DROP INDEX IF EXISTS i1 }
 183   execsql { DROP INDEX IF EXISTS i2 }
 184   execsql { DROP INDEX IF EXISTS i3 }
 185
 186   execsql $idx
 187   do_execsql_test 3.1.$tn.1 {
 188     SELECT a, b, c, typeof(a) FROM x1 WHERE a=2
 189   } {2 two ii text}
 190   do_execsql_test 3.1.$tn.2 {
 191     SELECT a, b, c, typeof(a) FROM x1 WHERE a=2.0
 192   } {2.0 twopointoh ii.0 text}
 193   do_execsql_test 3.1.$tn.3 {
 194     SELECT a, b, c, typeof(a) FROM x1 WHERE a='2'
 195   } {2 two ii text}
 196   do_execsql_test 3.1.$tn.4 {
 197     SELECT a, b, c, typeof(a) FROM x1 WHERE a='2.0'
 198   } {2.0 twopointoh ii.0 text}
 199
 200   do_execsql_test 3.1.$tn.5 {
 201     SELECT a, b, c, typeof(a) FROM x2 WHERE a=2
 202   } {2 two ii integer 2 twopointoh ii.0 integer}
 203   do_execsql_test 3.1.$tn.6 {
 204     SELECT a, b, c, typeof(a) FROM x2 WHERE a=2.0
 205   } {2 two ii integer 2 twopointoh ii.0 integer}
 206   do_execsql_test 3.1.$tn.7 {
 207     SELECT a, b, c, typeof(a) FROM x2 WHERE a='2'
 208   } {2 two ii integer 2 twopointoh ii.0 integer}
 209   do_execsql_test 3.1.$tn.8 {
 210     SELECT a, b, c, typeof(a) FROM x2 WHERE a='2.0'
 211   } {2 two ii integer 2 twopointoh ii.0 integer}
 212
 213   do_execsql_test 3.1.$tn.9 {
 214     SELECT a, b, c, typeof(a) FROM x3 WHERE a=2
 215   } {2.0 two ii real 2.0 twopointoh ii.0 real}
 216   do_execsql_test 3.1.$tn.10 {
 217     SELECT a, b, c, typeof(a) FROM x3 WHERE a=2.0
 218   } {2.0 two ii real 2.0 twopointoh ii.0 real}
 219   do_execsql_test 3.1.$tn.11 {
 220     SELECT a, b, c, typeof(a) FROM x3 WHERE a='2'
 221   } {2.0 two ii real 2.0 twopointoh ii.0 real}
 222   do_execsql_test 3.1.$tn.12 {
 223     SELECT a, b, c, typeof(a) FROM x3 WHERE a='2.0'
 224   } {2.0 two ii real 2.0 twopointoh ii.0 real}
 225 }
 226
 227 #-------------------------------------------------------------------------
 228 reset_db
 229 do_execsql_test 4.0 {
 230   CREATE TABLE t2(a INTEGER, b TEXT);
 231   INSERT INTO t2 VALUES(1, 'two');
 232   INSERT INTO t2 VALUES(2, 'two');
 233   INSERT INTO t2 VALUES(3, 'two');
 234   INSERT INTO t2 VALUES(1, 'three');
 235   INSERT INTO t2 VALUES(2, 'three');
 236   INSERT INTO t2 VALUES(3, 'three');
 237
 238   CREATE INDEX t2a_two ON t2(a) WHERE b='two';
 239 }
 240
 241 # explain_i { SELECT sum(a), b FROM t2 WHERE b='two' }
 242 do_execsql_test 4.1.1 {
 243   SELECT sum(a), b FROM t2 WHERE b='two'
 244 } {6 two}
 245 do_eqp_test 4.1.2 {
 246   SELECT sum(a), b FROM t2 WHERE b='two'
 247 } {USING COVERING INDEX t2a_two}
 248
 249 #-------------------------------------------------------------------------
 250 reset_db
 251 do_execsql_test 5.0 {
 252   CREATE TABLE t1(a INTEGER PRIMQRY KEY, b, c);
 253 }
 254 do_catchsql_test 5.1 {
 255   CREATE INDEX ex1 ON t1(c) WHERE b IS 'abc' COLLATE g;
 256 } {1 {no such collation sequence: g}}
 257
 258 proc xyz {lhs rhs} {
 259   return [string compare $lhs $rhs]
 260 }
 261 db collate xyz xyz
 262 do_execsql_test 5.2 {
 263   CREATE INDEX ex1 ON t1(c) WHERE b IS 'abc' COLLATE xyz;
 264 }
 265 db close
 266 sqlite3 db test.db
 267 do_execsql_test 5.3 {
 268   SELECT * FROM t1
 269 }
 270
 271 #-------------------------------------------------------------------------
 272 reset_db
 273 do_execsql_test 6.0 {
 274   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
 275   CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER, z INTEGER);
 276   INSERT INTO t1 VALUES(1, 1, 1);
 277   INSERT INTO t1 VALUES(2, 1, 2);
 278   INSERT INTO t2 VALUES(1, 5, 1);
 279   INSERT INTO t2 VALUES(2, 5, 2);
 280
 281   CREATE INDEX t2z ON t2(z) WHERE y=5;
 282 }
 283
 284 do_execsql_test 6.1 {
 285   ANALYZE;
 286   UPDATE sqlite_stat1 SET stat = '50 1' WHERE idx='t2z';
 287   UPDATE sqlite_stat1 SET stat = '50' WHERE tbl='t2' AND idx IS NULL;
 288   UPDATE sqlite_stat1 SET stat = '5000' WHERE tbl='t1' AND idx IS NULL;
 289   ANALYZE sqlite_schema;
 290 }
 291
 292 do_execsql_test 6.2 {
 293   SELECT * FROM t1, t2 WHERE b=1 AND z=c AND y=5;
 294 } {
 295   1 1 1  1 5 1
 296   2 1 2  2 5 2
 297 }
 298
 299 do_eqp_test 6.3 {
 300   SELECT * FROM t1, t2 WHERE b=1 AND z=c AND y=5;
 301 } {BLOOM FILTER ON t2}
 302
 303 do_execsql_test 6.4 {
 304   SELECT * FROM t1 LEFT JOIN t2 ON (y=5) WHERE b=1 AND z IS c;
 305 } {
 306   1 1 1  1 5 1
 307   2 1 2  2 5 2
 308 }
 309
 310 do_eqp_test 6.5 {
 311   SELECT * FROM t1 LEFT JOIN t2 ON (y=5) WHERE b=1 AND z IS c;
 312 } {BLOOM FILTER ON t2}
 313
 314 do_execsql_test 6.6 {
 315   CREATE INDEX t2yz ON t2(y, z) WHERE y=5;
 316 }
 317
 318 do_execsql_test 6.7 {
 319   SELECT * FROM t1 LEFT JOIN t2 ON (y=5) WHERE b=1 AND z IS c;
 320 } {
 321   1 1 1  1 5 1
 322   2 1 2  2 5 2
 323 }
 324
 325 #-------------------------------------------------------------------------
 326 reset_db
 327 do_execsql_test 7.0 {
 328   CREATE TABLE t1(i INTEGER PRIMARY KEY, b TEXT, c TEXT);
 329   CREATE INDEX i1 ON t1(c) WHERE b='abc' AND i=5;
 330   INSERT INTO t1 VALUES(5, 'abc', 'xyz');
 331   SELECT * FROM t1 INDEXED BY i1 WHERE b='abc' AND i=5 ORDER BY c;
 332 } {5 abc xyz}
 333
 334 #-------------------------------------------------------------------------
 335 reset_db
 336 do_execsql_test 8.0 {
 337   CREATE TABLE t1(a, b, c);
 338   CREATE INDEX ex2 ON t1(a, 4);
 339   CREATE INDEX ex1 ON t1(a) WHERE 4=b;
 340   INSERT INTO t1 VALUES(1, 4, 1);
 341   INSERT INTO t1 VALUES(1, 5, 1);
 342   INSERT INTO t1 VALUES(2, 4, 2);
 343 }
 344 do_execsql_test 8.1 {
 345   SELECT * FROM t1 WHERE b=4;
 346 } {
 347   1 4 1  2 4 2
 348 }
 349
 350 finish_test