usr/src/lib/libsqlite/test/index.test

   1 #
   2 # 2001 September 15
   3 #
   4 # The author disclaims copyright to this source code.  In place of
   5 # a legal notice, here is a blessing:
   6 #
   7 #    May you do good and not evil.
   8 #    May you find forgiveness for yourself and forgive others.
   9 #    May you share freely, never taking more than you give.
  10 #
  11 #***********************************************************************
  12 # This file implements regression tests for SQLite library.  The
  13 # focus of this file is testing the CREATE INDEX statement.
  14 #
  15 # $Id: index.test,v 1.24.2.1 2004/07/20 00:50:30 drh Exp $
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19
  20 # Create a basic index and verify it is added to sqlite_master
  21 #
  22 do_test index-1.1 {
  23   execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
  24   execsql {CREATE INDEX index1 ON test1(f1)}
  25   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
  26 } {index1 test1}
  27 do_test index-1.1b {
  28   execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
  29            WHERE name='index1'}
  30 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
  31 do_test index-1.1c {
  32   db close
  33   sqlite db test.db
  34   execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
  35            WHERE name='index1'}
  36 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
  37 do_test index-1.1d {
  38   db close
  39   sqlite db test.db
  40   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
  41 } {index1 test1}
  42
  43 # Verify that the index dies with the table
  44 #
  45 do_test index-1.2 {
  46   execsql {DROP TABLE test1}
  47   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
  48 } {}
  49
  50 # Try adding an index to a table that does not exist
  51 #
  52 do_test index-2.1 {
  53   set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]
  54   lappend v $msg
  55 } {1 {no such table: test1}}
  56
  57 # Try adding an index on a column of a table where the table
  58 # exists but the column does not.
  59 #
  60 do_test index-2.1 {
  61   execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
  62   set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg]
  63   lappend v $msg
  64 } {1 {table test1 has no column named f4}}
  65
  66 # Try an index with some columns that match and others that do now.
  67 #
  68 do_test index-2.2 {
  69   set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg]
  70   execsql {DROP TABLE test1}
  71   lappend v $msg
  72 } {1 {table test1 has no column named f4}}
  73
  74 # Try creating a bunch of indices on the same table
  75 #
  76 set r {}
  77 for {set i 1} {$i<100} {incr i} {
  78   lappend r [format index%02d $i]
  79 }
  80 do_test index-3.1 {
  81   execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)}
  82   for {set i 1} {$i<100} {incr i} {
  83     set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
  84     execsql $sql
  85   }
  86   execsql {SELECT name FROM sqlite_master
  87            WHERE type='index' AND tbl_name='test1'
  88            ORDER BY name}
  89 } $r
  90
  91
  92 # Verify that all the indices go away when we drop the table.
  93 #
  94 do_test index-3.3 {
  95   execsql {DROP TABLE test1}
  96   execsql {SELECT name FROM sqlite_master
  97            WHERE type='index' AND tbl_name='test1'
  98            ORDER BY name}
  99 } {}
 100
 101 # Create a table and insert values into that table.  Then create
 102 # an index on that table.  Verify that we can select values
 103 # from the table correctly using the index.
 104 #
 105 # Note that the index names "index9" and "indext" are chosen because
 106 # they both have the same hash.
 107 #
 108 do_test index-4.1 {
 109   execsql {CREATE TABLE test1(cnt int, power int)}
 110   for {set i 1} {$i<20} {incr i} {
 111     execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
 112   }
 113   execsql {CREATE INDEX index9 ON test1(cnt)}
 114   execsql {CREATE INDEX indext ON test1(power)}
 115   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 116 } {index9 indext test1}
 117 do_test index-4.2 {
 118   execsql {SELECT cnt FROM test1 WHERE power=4}
 119 } {2}
 120 do_test index-4.3 {
 121   execsql {SELECT cnt FROM test1 WHERE power=1024}
 122 } {10}
 123 do_test index-4.4 {
 124   execsql {SELECT power FROM test1 WHERE cnt=6}
 125 } {64}
 126 do_test index-4.5 {
 127   execsql {DROP INDEX indext}
 128   execsql {SELECT power FROM test1 WHERE cnt=6}
 129 } {64}
 130 do_test index-4.6 {
 131   execsql {SELECT cnt FROM test1 WHERE power=1024}
 132 } {10}
 133 do_test index-4.7 {
 134   execsql {CREATE INDEX indext ON test1(cnt)}
 135   execsql {SELECT power FROM test1 WHERE cnt=6}
 136 } {64}
 137 do_test index-4.8 {
 138   execsql {SELECT cnt FROM test1 WHERE power=1024}
 139 } {10}
 140 do_test index-4.9 {
 141   execsql {DROP INDEX index9}
 142   execsql {SELECT power FROM test1 WHERE cnt=6}
 143 } {64}
 144 do_test index-4.10 {
 145   execsql {SELECT cnt FROM test1 WHERE power=1024}
 146 } {10}
 147 do_test index-4.11 {
 148   execsql {DROP INDEX indext}
 149   execsql {SELECT power FROM test1 WHERE cnt=6}
 150 } {64}
 151 do_test index-4.12 {
 152   execsql {SELECT cnt FROM test1 WHERE power=1024}
 153 } {10}
 154 do_test index-4.13 {
 155   execsql {DROP TABLE test1}
 156   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 157 } {}
 158 integrity_check index-4.14
 159
 160 # Do not allow indices to be added to sqlite_master
 161 #
 162 do_test index-5.1 {
 163   set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
 164   lappend v $msg
 165 } {1 {table sqlite_master may not be indexed}}
 166 do_test index-5.2 {
 167   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 168 } {}
 169
 170 # Do not allow indices with duplicate names to be added
 171 #
 172 do_test index-6.1 {
 173   execsql {CREATE TABLE test1(f1 int, f2 int)}
 174   execsql {CREATE TABLE test2(g1 real, g2 real)}
 175   execsql {CREATE INDEX index1 ON test1(f1)}
 176   set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg]
 177   lappend v $msg
 178 } {1 {index index1 already exists}}
 179 do_test index-6.1b {
 180   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 181 } {index1 test1 test2}
 182 do_test index-6.2 {
 183   set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg]
 184   lappend v $msg
 185 } {1 {there is already a table named test1}}
 186 do_test index-6.2b {
 187   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 188 } {index1 test1 test2}
 189 do_test index-6.3 {
 190   execsql {DROP TABLE test1}
 191   execsql {DROP TABLE test2}
 192   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 193 } {}
 194 do_test index-6.4 {
 195   execsql {
 196     CREATE TABLE test1(a,b);
 197     CREATE INDEX index1 ON test1(a);
 198     CREATE INDEX index2 ON test1(b);
 199     CREATE INDEX index3 ON test1(a,b);
 200     DROP TABLE test1;
 201     SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;
 202   }
 203 } {}
 204 integrity_check index-6.5
 205
 206
 207 # Create a primary key
 208 #
 209 do_test index-7.1 {
 210   execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
 211   for {set i 1} {$i<20} {incr i} {
 212     execsql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
 213   }
 214   execsql {SELECT count(*) FROM test1}
 215 } {19}
 216 do_test index-7.2 {
 217   execsql {SELECT f1 FROM test1 WHERE f2=65536}
 218 } {16}
 219 do_test index-7.3 {
 220   execsql {
 221     SELECT name FROM sqlite_master
 222     WHERE type='index' AND tbl_name='test1'
 223   }
 224 } {{(test1 autoindex 1)}}
 225 do_test index-7.4 {
 226   execsql {DROP table test1}
 227   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 228 } {}
 229 integrity_check index-7.5
 230
 231 # Make sure we cannot drop a non-existant index.
 232 #
 233 do_test index-8.1 {
 234   set v [catch {execsql {DROP INDEX index1}} msg]
 235   lappend v $msg
 236 } {1 {no such index: index1}}
 237
 238 # Make sure we don't actually create an index when the EXPLAIN keyword
 239 # is used.
 240 #
 241 do_test index-9.1 {
 242   execsql {CREATE TABLE tab1(a int)}
 243   execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
 244   execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
 245 } {tab1}
 246 do_test index-9.2 {
 247   execsql {CREATE INDEX idx1 ON tab1(a)}
 248   execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
 249 } {idx1 tab1}
 250 integrity_check index-9.3
 251
 252 # Allow more than one entry with the same key.
 253 #
 254 do_test index-10.0 {
 255   execsql {
 256     CREATE TABLE t1(a int, b int);
 257     CREATE INDEX i1 ON t1(a);
 258     INSERT INTO t1 VALUES(1,2);
 259     INSERT INTO t1 VALUES(2,4);
 260     INSERT INTO t1 VALUES(3,8);
 261     INSERT INTO t1 VALUES(1,12);
 262     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 263   }
 264 } {2 12}
 265 do_test index-10.1 {
 266   execsql {
 267     SELECT b FROM t1 WHERE a=2 ORDER BY b;
 268   }
 269 } {4}
 270 do_test index-10.2 {
 271   execsql {
 272     DELETE FROM t1 WHERE b=12;
 273     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 274   }
 275 } {2}
 276 do_test index-10.3 {
 277   execsql {
 278     DELETE FROM t1 WHERE b=2;
 279     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 280   }
 281 } {}
 282 do_test index-10.4 {
 283   execsql {
 284     DELETE FROM t1;
 285     INSERT INTO t1 VALUES (1,1);
 286     INSERT INTO t1 VALUES (1,2);
 287     INSERT INTO t1 VALUES (1,3);
 288     INSERT INTO t1 VALUES (1,4);
 289     INSERT INTO t1 VALUES (1,5);
 290     INSERT INTO t1 VALUES (1,6);
 291     INSERT INTO t1 VALUES (1,7);
 292     INSERT INTO t1 VALUES (1,8);
 293     INSERT INTO t1 VALUES (1,9);
 294     INSERT INTO t1 VALUES (2,0);
 295     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 296   }
 297 } {1 2 3 4 5 6 7 8 9}
 298 do_test index-10.5 {
 299   execsql {
 300     DELETE FROM t1 WHERE b IN (2, 4, 6, 8);
 301     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 302   }
 303 } {1 3 5 7 9}
 304 do_test index-10.6 {
 305   execsql {
 306     DELETE FROM t1 WHERE b>2;
 307     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 308   }
 309 } {1}
 310 do_test index-10.7 {
 311   execsql {
 312     DELETE FROM t1 WHERE b=1;
 313     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 314   }
 315 } {}
 316 do_test index-10.8 {
 317   execsql {
 318     SELECT b FROM t1 ORDER BY b;
 319   }
 320 } {0}
 321 integrity_check index-10.9
 322
 323 # Automatically create an index when we specify a primary key.
 324 #
 325 do_test index-11.1 {
 326   execsql {
 327     CREATE TABLE t3(
 328       a text,
 329       b int,
 330       c float,
 331       PRIMARY KEY(b)
 332     );
 333   }
 334   for {set i 1} {$i<=50} {incr i} {
 335     execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
 336   }
 337   set sqlite_search_count 0
 338   concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
 339 } {0.10 3}
 340 integrity_check index-11.2
 341
 342
 343 # Numeric strings should compare as if they were numbers.  So even if the
 344 # strings are not character-by-character the same, if they represent the
 345 # same number they should compare equal to one another.  Verify that this
 346 # is true in indices.
 347 #
 348 do_test index-12.1 {
 349   execsql {
 350     CREATE TABLE t4(a,b);
 351     INSERT INTO t4 VALUES('0.0',1);
 352     INSERT INTO t4 VALUES('0.00',2);
 353     INSERT INTO t4 VALUES('abc',3);
 354     INSERT INTO t4 VALUES('-1.0',4);
 355     INSERT INTO t4 VALUES('+1.0',5);
 356     INSERT INTO t4 VALUES('0',6);
 357     INSERT INTO t4 VALUES('00000',7);
 358     SELECT a FROM t4 ORDER BY b;
 359   }
 360 } {0.0 0.00 abc -1.0 +1.0 0 00000}
 361 do_test index-12.2 {
 362   execsql {
 363     SELECT a FROM t4 WHERE a==0 ORDER BY b
 364   }
 365 } {0.0 0.00 0 00000}
 366 do_test index-12.3 {
 367   execsql {
 368     SELECT a FROM t4 WHERE a<0.5 ORDER BY b
 369   }
 370 } {0.0 0.00 -1.0 0 00000}
 371 do_test index-12.4 {
 372   execsql {
 373     SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
 374   }
 375 } {0.0 0.00 abc +1.0 0 00000}
 376 do_test index-12.5 {
 377   execsql {
 378     CREATE INDEX t4i1 ON t4(a);
 379     SELECT a FROM t4 WHERE a==0 ORDER BY b
 380   }
 381 } {0.0 0.00 0 00000}
 382 do_test index-12.6 {
 383   execsql {
 384     SELECT a FROM t4 WHERE a<0.5 ORDER BY b
 385   }
 386 } {0.0 0.00 -1.0 0 00000}
 387 do_test index-12.7 {
 388   execsql {
 389     SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
 390   }
 391 } {0.0 0.00 abc +1.0 0 00000}
 392 integrity_check index-12.8
 393
 394 # Make sure we cannot drop an automatically created index.
 395 #
 396 do_test index-13.1 {
 397   execsql {
 398    CREATE TABLE t5(
 399       a int UNIQUE,
 400       b float PRIMARY KEY,
 401       c varchar(10),
 402       UNIQUE(a,c)
 403    );
 404    INSERT INTO t5 VALUES(1,2,3);
 405    SELECT * FROM t5;
 406   }
 407 } {1 2 3}
 408 do_test index-13.2 {
 409   set ::idxlist [execsql {
 410     SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
 411   }]
 412   llength $::idxlist
 413 } {3}
 414 for {set i 0} {$i<[llength $::idxlist]} {incr i} {
 415   do_test index-13.3.$i {
 416     catchsql "
 417       DROP INDEX '[lindex $::idxlist $i]';
 418     "
 419   } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
 420 }
 421 do_test index-13.4 {
 422   execsql {
 423     INSERT INTO t5 VALUES('a','b','c');
 424     SELECT * FROM t5;
 425   }
 426 } {1 2 3 a b c}
 427 integrity_check index-13.5
 428
 429 # Check the sort order of data in an index.
 430 #
 431 do_test index-14.1 {
 432   execsql {
 433     CREATE TABLE t6(a,b,c);
 434     CREATE INDEX t6i1 ON t6(a,b);
 435     INSERT INTO t6 VALUES('','',1);
 436     INSERT INTO t6 VALUES('',NULL,2);
 437     INSERT INTO t6 VALUES(NULL,'',3);
 438     INSERT INTO t6 VALUES('abc',123,4);
 439     INSERT INTO t6 VALUES(123,'abc',5);
 440     SELECT c FROM t6 ORDER BY a,b;
 441   }
 442 } {3 5 2 1 4}
 443 do_test index-14.2 {
 444   execsql {
 445     SELECT c FROM t6 WHERE a='';
 446   }
 447 } {2 1}
 448 do_test index-14.3 {
 449   execsql {
 450     SELECT c FROM t6 WHERE b='';
 451   }
 452 } {1 3}
 453 do_test index-14.4 {
 454   execsql {
 455     SELECT c FROM t6 WHERE a>'';
 456   }
 457 } {4}
 458 do_test index-14.5 {
 459   execsql {
 460     SELECT c FROM t6 WHERE a>='';
 461   }
 462 } {2 1 4}
 463 do_test index-14.6 {
 464   execsql {
 465     SELECT c FROM t6 WHERE a>123;
 466   }
 467 } {2 1 4}
 468 do_test index-14.7 {
 469   execsql {
 470     SELECT c FROM t6 WHERE a>=123;
 471   }
 472 } {5 2 1 4}
 473 do_test index-14.8 {
 474   execsql {
 475     SELECT c FROM t6 WHERE a<'abc';
 476   }
 477 } {5 2 1}
 478 do_test index-14.9 {
 479   execsql {
 480     SELECT c FROM t6 WHERE a<='abc';
 481   }
 482 } {5 2 1 4}
 483 do_test index-14.10 {
 484   execsql {
 485     SELECT c FROM t6 WHERE a<='';
 486   }
 487 } {5 2 1}
 488 do_test index-14.11 {
 489   execsql {
 490     SELECT c FROM t6 WHERE a<'';
 491   }
 492 } {5}
 493 integrity_check index-14.12
 494
 495 do_test index-15.1 {
 496   execsql {
 497     DELETE FROM t1;
 498     SELECT * FROM t1;
 499   }
 500 } {}
 501 do_test index-15.2 {
 502   execsql {
 503     INSERT INTO t1 VALUES('1.234e5',1);
 504     INSERT INTO t1 VALUES('12.33e04',2);
 505     INSERT INTO t1 VALUES('12.35E4',3);
 506     INSERT INTO t1 VALUES('12.34e',4);
 507     INSERT INTO t1 VALUES('12.32e+4',5);
 508     INSERT INTO t1 VALUES('12.36E+04',6);
 509     INSERT INTO t1 VALUES('12.36E+',7);
 510     INSERT INTO t1 VALUES('+123.10000E+0003',8);
 511     INSERT INTO t1 VALUES('+',9);
 512     INSERT INTO t1 VALUES('+12347.E+02',10);
 513     INSERT INTO t1 VALUES('+12347E+02',11);
 514     SELECT b FROM t1 ORDER BY a;
 515   }
 516 } {8 5 2 1 3 6 11 9 10 4 7}
 517 integrity_check index-15.1
 518
 519 # Drop index with a quoted name.  Ticket #695.
 520 #
 521 do_test index-16.1 {
 522   execsql {
 523     CREATE INDEX "t6i2" ON t6(c);
 524     DROP INDEX "t6i2";
 525   }
 526 } {}
 527 do_test index-16.2 {
 528   execsql {
 529     DROP INDEX "t6i1";
 530   }
 531 } {}
 532
 533
 534 finish_test