test/index.test

   1 # 2001 September 15
   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 # This file implements regression tests for SQLite library.  The
  12 # focus of this file is testing the CREATE INDEX statement.
  13 #
  14 # $Id: index.test,v 1.43 2008/01/16 18:20:42 danielk1977 Exp $
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18
  19 # Create a basic index and verify it is added to sqlite_master
  20 #
  21 do_test index-1.1 {
  22   execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
  23   execsql {CREATE INDEX index1 ON test1(f1)}
  24   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
  25 } {index1 test1}
  26 do_test index-1.1b {
  27   execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
  28            WHERE name='index1'}
  29 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
  30 do_test index-1.1c {
  31   db close
  32   sqlite3 db test.db
  33   execsql {SELECT name, sql, tbl_name, type FROM sqlite_master
  34            WHERE name='index1'}
  35 } {index1 {CREATE INDEX index1 ON test1(f1)} test1 index}
  36 do_test index-1.1d {
  37   db close
  38   sqlite3 db test.db
  39   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
  40 } {index1 test1}
  41
  42 # Verify that the index dies with the table
  43 #
  44 do_test index-1.2 {
  45   execsql {DROP TABLE test1}
  46   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
  47 } {}
  48
  49 # Try adding an index to a table that does not exist
  50 #
  51 do_test index-2.1 {
  52   set v [catch {execsql {CREATE INDEX index1 ON test1(f1)}} msg]
  53   lappend v $msg
  54 } {1 {no such table: main.test1}}
  55
  56 # Try adding an index on a column of a table where the table
  57 # exists but the column does not.
  58 #
  59 do_test index-2.1b {
  60   execsql {CREATE TABLE test1(f1 int, f2 int, f3 int)}
  61   set v [catch {execsql {CREATE INDEX index1 ON test1(f4)}} msg]
  62   lappend v $msg
  63 } {1 {no such column: f4}}
  64
  65 # Try an index with some columns that match and others that do now.
  66 #
  67 do_test index-2.2 {
  68   set v [catch {execsql {CREATE INDEX index1 ON test1(f1, f2, f4, f3)}} msg]
  69   execsql {DROP TABLE test1}
  70   lappend v $msg
  71 } {1 {no such column: f4}}
  72
  73 # Try creating a bunch of indices on the same table
  74 #
  75 set r {}
  76 for {set i 1} {$i<100} {incr i} {
  77   lappend r [format index%02d $i]
  78 }
  79 do_test index-3.1 {
  80   execsql {CREATE TABLE test1(f1 int, f2 int, f3 int, f4 int, f5 int)}
  81   for {set i 1} {$i<100} {incr i} {
  82     set sql "CREATE INDEX [format index%02d $i] ON test1(f[expr {($i%5)+1}])"
  83     execsql $sql
  84   }
  85   execsql {SELECT name FROM sqlite_master
  86            WHERE type='index' AND tbl_name='test1'
  87            ORDER BY name}
  88 } $r
  89 integrity_check index-3.2.1
  90 ifcapable {reindex} {
  91   do_test index-3.2.2 {
  92     execsql REINDEX
  93   } {}
  94 }
  95 integrity_check index-3.2.3
  96
  97
  98 # Verify that all the indices go away when we drop the table.
  99 #
 100 do_test index-3.3 {
 101   execsql {DROP TABLE test1}
 102   execsql {SELECT name FROM sqlite_master
 103            WHERE type='index' AND tbl_name='test1'
 104            ORDER BY name}
 105 } {}
 106
 107 # Create a table and insert values into that table.  Then create
 108 # an index on that table.  Verify that we can select values
 109 # from the table correctly using the index.
 110 #
 111 # Note that the index names "index9" and "indext" are chosen because
 112 # they both have the same hash.
 113 #
 114 do_test index-4.1 {
 115   execsql {CREATE TABLE test1(cnt int, power int)}
 116   for {set i 1} {$i<20} {incr i} {
 117     execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])"
 118   }
 119   execsql {CREATE INDEX index9 ON test1(cnt)}
 120   execsql {CREATE INDEX indext ON test1(power)}
 121   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 122 } {index9 indext test1}
 123 do_test index-4.2 {
 124   execsql {SELECT cnt FROM test1 WHERE power=4}
 125 } {2}
 126 do_test index-4.3 {
 127   execsql {SELECT cnt FROM test1 WHERE power=1024}
 128 } {10}
 129 do_test index-4.4 {
 130   execsql {SELECT power FROM test1 WHERE cnt=6}
 131 } {64}
 132 do_test index-4.5 {
 133   execsql {DROP INDEX indext}
 134   execsql {SELECT power FROM test1 WHERE cnt=6}
 135 } {64}
 136 do_test index-4.6 {
 137   execsql {SELECT cnt FROM test1 WHERE power=1024}
 138 } {10}
 139 do_test index-4.7 {
 140   execsql {CREATE INDEX indext ON test1(cnt)}
 141   execsql {SELECT power FROM test1 WHERE cnt=6}
 142 } {64}
 143 do_test index-4.8 {
 144   execsql {SELECT cnt FROM test1 WHERE power=1024}
 145 } {10}
 146 do_test index-4.9 {
 147   execsql {DROP INDEX index9}
 148   execsql {SELECT power FROM test1 WHERE cnt=6}
 149 } {64}
 150 do_test index-4.10 {
 151   execsql {SELECT cnt FROM test1 WHERE power=1024}
 152 } {10}
 153 do_test index-4.11 {
 154   execsql {DROP INDEX indext}
 155   execsql {SELECT power FROM test1 WHERE cnt=6}
 156 } {64}
 157 do_test index-4.12 {
 158   execsql {SELECT cnt FROM test1 WHERE power=1024}
 159 } {10}
 160 do_test index-4.13 {
 161   execsql {DROP TABLE test1}
 162   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 163 } {}
 164 integrity_check index-4.14
 165
 166 # Do not allow indices to be added to sqlite_master
 167 #
 168 do_test index-5.1 {
 169   set v [catch {execsql {CREATE INDEX index1 ON sqlite_master(name)}} msg]
 170   lappend v $msg
 171 } {1 {table sqlite_master may not be indexed}}
 172 do_test index-5.2 {
 173   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 174 } {}
 175
 176 # Do not allow indices with duplicate names to be added
 177 #
 178 do_test index-6.1 {
 179   execsql {CREATE TABLE test1(f1 int, f2 int)}
 180   execsql {CREATE TABLE test2(g1 real, g2 real)}
 181   execsql {CREATE INDEX index1 ON test1(f1)}
 182   set v [catch {execsql {CREATE INDEX index1 ON test2(g1)}} msg]
 183   lappend v $msg
 184 } {1 {index index1 already exists}}
 185 do_test index-6.1.1 {
 186   catchsql {CREATE INDEX [index1] ON test2(g1)}
 187 } {1 {index index1 already exists}}
 188 do_test index-6.1b {
 189   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 190 } {index1 test1 test2}
 191 do_test index-6.1c {
 192   catchsql {CREATE INDEX IF NOT EXISTS index1 ON test1(f1)}
 193 } {0 {}}
 194 do_test index-6.2 {
 195   set v [catch {execsql {CREATE INDEX test1 ON test2(g1)}} msg]
 196   lappend v $msg
 197 } {1 {there is already a table named test1}}
 198 do_test index-6.2b {
 199   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 200 } {index1 test1 test2}
 201 do_test index-6.3 {
 202   execsql {DROP TABLE test1}
 203   execsql {DROP TABLE test2}
 204   execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
 205 } {}
 206 do_test index-6.4 {
 207   execsql {
 208     CREATE TABLE test1(a,b);
 209     CREATE INDEX index1 ON test1(a);
 210     CREATE INDEX index2 ON test1(b);
 211     CREATE INDEX index3 ON test1(a,b);
 212     DROP TABLE test1;
 213     SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name;
 214   }
 215 } {}
 216 integrity_check index-6.5
 217
 218
 219 # Create a primary key
 220 #
 221 do_test index-7.1 {
 222   execsql {CREATE TABLE test1(f1 int, f2 int primary key)}
 223   for {set i 1} {$i<20} {incr i} {
 224     execsql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])"
 225   }
 226   execsql {SELECT count(*) FROM test1}
 227 } {19}
 228 do_test index-7.2 {
 229   execsql {SELECT f1 FROM test1 WHERE f2=65536}
 230 } {16}
 231 do_test index-7.3 {
 232   execsql {
 233     SELECT name FROM sqlite_master
 234     WHERE type='index' AND tbl_name='test1'
 235   }
 236 } {sqlite_autoindex_test1_1}
 237 do_test index-7.4 {
 238   execsql {DROP table test1}
 239   execsql {SELECT name FROM sqlite_master WHERE type!='meta'}
 240 } {}
 241 integrity_check index-7.5
 242
 243 # Make sure we cannot drop a non-existant index.
 244 #
 245 do_test index-8.1 {
 246   set v [catch {execsql {DROP INDEX index1}} msg]
 247   lappend v $msg
 248 } {1 {no such index: index1}}
 249
 250 # Make sure we don't actually create an index when the EXPLAIN keyword
 251 # is used.
 252 #
 253 do_test index-9.1 {
 254   execsql {CREATE TABLE tab1(a int)}
 255   ifcapable {explain} {
 256     execsql {EXPLAIN CREATE INDEX idx1 ON tab1(a)}
 257   }
 258   execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1'}
 259 } {tab1}
 260 do_test index-9.2 {
 261   execsql {CREATE INDEX idx1 ON tab1(a)}
 262   execsql {SELECT name FROM sqlite_master WHERE tbl_name='tab1' ORDER BY name}
 263 } {idx1 tab1}
 264 integrity_check index-9.3
 265
 266 # Allow more than one entry with the same key.
 267 #
 268 do_test index-10.0 {
 269   execsql {
 270     CREATE TABLE t1(a int, b int);
 271     CREATE INDEX i1 ON t1(a);
 272     INSERT INTO t1 VALUES(1,2);
 273     INSERT INTO t1 VALUES(2,4);
 274     INSERT INTO t1 VALUES(3,8);
 275     INSERT INTO t1 VALUES(1,12);
 276     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 277   }
 278 } {2 12}
 279 do_test index-10.1 {
 280   execsql {
 281     SELECT b FROM t1 WHERE a=2 ORDER BY b;
 282   }
 283 } {4}
 284 do_test index-10.2 {
 285   execsql {
 286     DELETE FROM t1 WHERE b=12;
 287     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 288   }
 289 } {2}
 290 do_test index-10.3 {
 291   execsql {
 292     DELETE FROM t1 WHERE b=2;
 293     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 294   }
 295 } {}
 296 do_test index-10.4 {
 297   execsql {
 298     DELETE FROM t1;
 299     INSERT INTO t1 VALUES (1,1);
 300     INSERT INTO t1 VALUES (1,2);
 301     INSERT INTO t1 VALUES (1,3);
 302     INSERT INTO t1 VALUES (1,4);
 303     INSERT INTO t1 VALUES (1,5);
 304     INSERT INTO t1 VALUES (1,6);
 305     INSERT INTO t1 VALUES (1,7);
 306     INSERT INTO t1 VALUES (1,8);
 307     INSERT INTO t1 VALUES (1,9);
 308     INSERT INTO t1 VALUES (2,0);
 309     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 310   }
 311 } {1 2 3 4 5 6 7 8 9}
 312 do_test index-10.5 {
 313   ifcapable subquery {
 314     execsql { DELETE FROM t1 WHERE b IN (2, 4, 6, 8); }
 315   } else {
 316     execsql { DELETE FROM t1 WHERE b = 2 OR b = 4 OR b = 6 OR b = 8; }
 317   }
 318   execsql {
 319     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 320   }
 321 } {1 3 5 7 9}
 322 do_test index-10.6 {
 323   execsql {
 324     DELETE FROM t1 WHERE b>2;
 325     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 326   }
 327 } {1}
 328 do_test index-10.7 {
 329   execsql {
 330     DELETE FROM t1 WHERE b=1;
 331     SELECT b FROM t1 WHERE a=1 ORDER BY b;
 332   }
 333 } {}
 334 do_test index-10.8 {
 335   execsql {
 336     SELECT b FROM t1 ORDER BY b;
 337   }
 338 } {0}
 339 integrity_check index-10.9
 340
 341 # Automatically create an index when we specify a primary key.
 342 #
 343 do_test index-11.1 {
 344   execsql {
 345     CREATE TABLE t3(
 346       a text,
 347       b int,
 348       c float,
 349       PRIMARY KEY(b)
 350     );
 351   }
 352   for {set i 1} {$i<=50} {incr i} {
 353     execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
 354   }
 355   set sqlite_search_count 0
 356   concat [execsql {SELECT c FROM t3 WHERE b==10}] $sqlite_search_count
 357 } {0.1 2}
 358 integrity_check index-11.2
 359
 360
 361 # Numeric strings should compare as if they were numbers.  So even if the
 362 # strings are not character-by-character the same, if they represent the
 363 # same number they should compare equal to one another.  Verify that this
 364 # is true in indices.
 365 #
 366 # Updated for sqlite3 v3: SQLite will now store these values as numbers
 367 # (because the affinity of column a is NUMERIC) so the quirky
 368 # representations are not retained. i.e. '+1.0' becomes '1'.
 369 do_test index-12.1 {
 370   execsql {
 371     CREATE TABLE t4(a NUM,b);
 372     INSERT INTO t4 VALUES('0.0',1);
 373     INSERT INTO t4 VALUES('0.00',2);
 374     INSERT INTO t4 VALUES('abc',3);
 375     INSERT INTO t4 VALUES('-1.0',4);
 376     INSERT INTO t4 VALUES('+1.0',5);
 377     INSERT INTO t4 VALUES('0',6);
 378     INSERT INTO t4 VALUES('00000',7);
 379     SELECT a FROM t4 ORDER BY b;
 380   }
 381 } {0 0 abc -1 1 0 0}
 382 do_test index-12.2 {
 383   execsql {
 384     SELECT a FROM t4 WHERE a==0 ORDER BY b
 385   }
 386 } {0 0 0 0}
 387 do_test index-12.3 {
 388   execsql {
 389     SELECT a FROM t4 WHERE a<0.5 ORDER BY b
 390   }
 391 } {0 0 -1 0 0}
 392 do_test index-12.4 {
 393   execsql {
 394     SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
 395   }
 396 } {0 0 abc 1 0 0}
 397 do_test index-12.5 {
 398   execsql {
 399     CREATE INDEX t4i1 ON t4(a);
 400     SELECT a FROM t4 WHERE a==0 ORDER BY b
 401   }
 402 } {0 0 0 0}
 403 do_test index-12.6 {
 404   execsql {
 405     SELECT a FROM t4 WHERE a<0.5 ORDER BY b
 406   }
 407 } {0 0 -1 0 0}
 408 do_test index-12.7 {
 409   execsql {
 410     SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
 411   }
 412 } {0 0 abc 1 0 0}
 413 integrity_check index-12.8
 414
 415 # Make sure we cannot drop an automatically created index.
 416 #
 417 do_test index-13.1 {
 418   execsql {
 419    CREATE TABLE t5(
 420       a int UNIQUE,
 421       b float PRIMARY KEY,
 422       c varchar(10),
 423       UNIQUE(a,c)
 424    );
 425    INSERT INTO t5 VALUES(1,2,3);
 426    SELECT * FROM t5;
 427   }
 428 } {1 2.0 3}
 429 do_test index-13.2 {
 430   set ::idxlist [execsql {
 431     SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='t5';
 432   }]
 433   llength $::idxlist
 434 } {3}
 435 for {set i 0} {$i<[llength $::idxlist]} {incr i} {
 436   do_test index-13.3.$i {
 437     catchsql "
 438       DROP INDEX '[lindex $::idxlist $i]';
 439     "
 440   } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
 441 }
 442 do_test index-13.4 {
 443   execsql {
 444     INSERT INTO t5 VALUES('a','b','c');
 445     SELECT * FROM t5;
 446   }
 447 } {1 2.0 3 a b c}
 448 integrity_check index-13.5
 449
 450 # Check the sort order of data in an index.
 451 #
 452 do_test index-14.1 {
 453   execsql {
 454     CREATE TABLE t6(a,b,c);
 455     CREATE INDEX t6i1 ON t6(a,b);
 456     INSERT INTO t6 VALUES('','',1);
 457     INSERT INTO t6 VALUES('',NULL,2);
 458     INSERT INTO t6 VALUES(NULL,'',3);
 459     INSERT INTO t6 VALUES('abc',123,4);
 460     INSERT INTO t6 VALUES(123,'abc',5);
 461     SELECT c FROM t6 ORDER BY a,b;
 462   }
 463 } {3 5 2 1 4}
 464 do_test index-14.2 {
 465   execsql {
 466     SELECT c FROM t6 WHERE a='';
 467   }
 468 } {2 1}
 469 do_test index-14.3 {
 470   execsql {
 471     SELECT c FROM t6 WHERE b='';
 472   }
 473 } {1 3}
 474 do_test index-14.4 {
 475   execsql {
 476     SELECT c FROM t6 WHERE a>'';
 477   }
 478 } {4}
 479 do_test index-14.5 {
 480   execsql {
 481     SELECT c FROM t6 WHERE a>='';
 482   }
 483 } {2 1 4}
 484 do_test index-14.6 {
 485   execsql {
 486     SELECT c FROM t6 WHERE a>123;
 487   }
 488 } {2 1 4}
 489 do_test index-14.7 {
 490   execsql {
 491     SELECT c FROM t6 WHERE a>=123;
 492   }
 493 } {5 2 1 4}
 494 do_test index-14.8 {
 495   execsql {
 496     SELECT c FROM t6 WHERE a<'abc';
 497   }
 498 } {5 2 1}
 499 do_test index-14.9 {
 500   execsql {
 501     SELECT c FROM t6 WHERE a<='abc';
 502   }
 503 } {5 2 1 4}
 504 do_test index-14.10 {
 505   execsql {
 506     SELECT c FROM t6 WHERE a<='';
 507   }
 508 } {5 2 1}
 509 do_test index-14.11 {
 510   execsql {
 511     SELECT c FROM t6 WHERE a<'';
 512   }
 513 } {5}
 514 integrity_check index-14.12
 515
 516 do_test index-15.1 {
 517   execsql {
 518     DELETE FROM t1;
 519     SELECT * FROM t1;
 520   }
 521 } {}
 522 do_test index-15.2 {
 523   execsql {
 524     INSERT INTO t1 VALUES('1.234e5',1);
 525     INSERT INTO t1 VALUES('12.33e04',2);
 526     INSERT INTO t1 VALUES('12.35E4',3);
 527     INSERT INTO t1 VALUES('12.34e',4);
 528     INSERT INTO t1 VALUES('12.32e+4',5);
 529     INSERT INTO t1 VALUES('12.36E+04',6);
 530     INSERT INTO t1 VALUES('12.36E+',7);
 531     INSERT INTO t1 VALUES('+123.10000E+0003',8);
 532     INSERT INTO t1 VALUES('+',9);
 533     INSERT INTO t1 VALUES('+12347.E+02',10);
 534     INSERT INTO t1 VALUES('+12347E+02',11);
 535     INSERT INTO t1 VALUES('+.125E+04',12);
 536     INSERT INTO t1 VALUES('-.125E+04',13);
 537     INSERT INTO t1 VALUES('.125E+0',14);
 538     INSERT INTO t1 VALUES('.125',15);
 539     SELECT b FROM t1 ORDER BY a, b;
 540   }
 541 } {13 14 15 12 8 5 2 1 3 6 10 11 9 4 7}
 542 do_test index-15.3 {
 543   execsql {
 544     SELECT b FROM t1 WHERE typeof(a) IN ('integer','real') ORDER BY b;
 545   }
 546 } {1 2 3 5 6 8 10 11 12 13 14 15}
 547 integrity_check index-15.4
 548
 549 # The following tests - index-16.* - test that when a table definition
 550 # includes qualifications that specify the same constraint twice only a
 551 # single index is generated to enforce the constraint.
 552 #
 553 # For example: "CREATE TABLE abc( x PRIMARY KEY, UNIQUE(x) );"
 554 #
 555 do_test index-16.1 {
 556   execsql {
 557     CREATE TABLE t7(c UNIQUE PRIMARY KEY);
 558     SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
 559   }
 560 } {1}
 561 do_test index-16.2 {
 562   execsql {
 563     DROP TABLE t7;
 564     CREATE TABLE t7(c UNIQUE PRIMARY KEY);
 565     SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
 566   }
 567 } {1}
 568 do_test index-16.3 {
 569   execsql {
 570     DROP TABLE t7;
 571     CREATE TABLE t7(c PRIMARY KEY, UNIQUE(c) );
 572     SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
 573   }
 574 } {1}
 575 do_test index-16.4 {
 576   execsql {
 577     DROP TABLE t7;
 578     CREATE TABLE t7(c, d , UNIQUE(c, d), PRIMARY KEY(c, d) );
 579     SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
 580   }
 581 } {1}
 582 do_test index-16.5 {
 583   execsql {
 584     DROP TABLE t7;
 585     CREATE TABLE t7(c, d , UNIQUE(c), PRIMARY KEY(c, d) );
 586     SELECT count(*) FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
 587   }
 588 } {2}
 589
 590 # Test that automatically create indices are named correctly. The current
 591 # convention is: "sqlite_autoindex_<table name>_<integer>"
 592 #
 593 # Then check that it is an error to try to drop any automtically created
 594 # indices.
 595 do_test index-17.1 {
 596   execsql {
 597     DROP TABLE t7;
 598     CREATE TABLE t7(c, d UNIQUE, UNIQUE(c), PRIMARY KEY(c, d) );
 599     SELECT name FROM sqlite_master WHERE tbl_name = 't7' AND type = 'index';
 600   }
 601 } {sqlite_autoindex_t7_1 sqlite_autoindex_t7_2 sqlite_autoindex_t7_3}
 602 do_test index-17.2 {
 603   catchsql {
 604     DROP INDEX sqlite_autoindex_t7_1;
 605   }
 606 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
 607 do_test index-17.3 {
 608   catchsql {
 609     DROP INDEX IF EXISTS sqlite_autoindex_t7_1;
 610   }
 611 } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
 612 do_test index-17.4 {
 613   catchsql {
 614     DROP INDEX IF EXISTS no_such_index;
 615   }
 616 } {0 {}}
 617
 618
 619 # The following tests ensure that it is not possible to explicitly name
 620 # a schema object with a name beginning with "sqlite_". Granted that is a
 621 # little outside the focus of this test scripts, but this has got to be
 622 # tested somewhere.
 623 do_test index-18.1 {
 624   catchsql {
 625     CREATE TABLE sqlite_t1(a, b, c);
 626   }
 627 } {1 {object name reserved for internal use: sqlite_t1}}
 628 do_test index-18.1.2 {
 629   catchsql {
 630     CREATE TABLE sqlite_t1(a, b, c);
 631   }
 632 } {1 {object name reserved for internal use: sqlite_t1}}
 633 sqlite3_db_config db DEFENSIVE 0
 634 do_test index-18.2 {
 635   catchsql {
 636     CREATE INDEX sqlite_i1 ON t7(c);
 637   }
 638 } {1 {object name reserved for internal use: sqlite_i1}}
 639 ifcapable view {
 640 do_test index-18.3 {
 641   catchsql {
 642     CREATE VIEW sqlite_v1 AS SELECT * FROM t7;
 643   }
 644 } {1 {object name reserved for internal use: sqlite_v1}}
 645 } ;# ifcapable view
 646 ifcapable {trigger} {
 647   do_test index-18.4 {
 648     catchsql {
 649       CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END;
 650     }
 651   } {1 {object name reserved for internal use: sqlite_tr1}}
 652 }
 653 do_test index-18.5 {
 654   execsql {
 655     DROP TABLE t7;
 656   }
 657 } {}
 658
 659 # These tests ensure that if multiple table definition constraints are
 660 # implemented by a single indice, the correct ON CONFLICT policy applies.
 661 ifcapable conflict {
 662   do_test index-19.1 {
 663     execsql {
 664       CREATE TABLE t7(a UNIQUE PRIMARY KEY);
 665       CREATE TABLE t8(a UNIQUE PRIMARY KEY ON CONFLICT ROLLBACK);
 666       INSERT INTO t7 VALUES(1);
 667       INSERT INTO t8 VALUES(1);
 668     }
 669   } {}
 670   do_test index-19.2 {
 671     catchsql {
 672       BEGIN;
 673       INSERT INTO t7 VALUES(1);
 674     }
 675   } {1 {UNIQUE constraint failed: t7.a}}
 676   do_test index-19.3 {
 677     catchsql {
 678       BEGIN;
 679     }
 680   } {1 {cannot start a transaction within a transaction}}
 681   do_test index-19.4 {
 682     catchsql {
 683       INSERT INTO t8 VALUES(1);
 684     }
 685   } {1 {UNIQUE constraint failed: t8.a}}
 686   do_test index-19.5 {
 687     catchsql {
 688       BEGIN;
 689       COMMIT;
 690     }
 691   } {0 {}}
 692   do_test index-19.6 {
 693     catchsql {
 694       DROP TABLE t7;
 695       DROP TABLE t8;
 696       CREATE TABLE t7(
 697          a PRIMARY KEY ON CONFLICT FAIL,
 698          UNIQUE(a) ON CONFLICT IGNORE
 699       );
 700     }
 701   } {1 {conflicting ON CONFLICT clauses specified}}
 702 } ; # end of "ifcapable conflict" block
 703
 704 ifcapable {reindex} {
 705   do_test index-19.7 {
 706     execsql REINDEX
 707   } {}
 708 }
 709 integrity_check index-19.8
 710
 711 # Drop index with a quoted name.  Ticket #695.
 712 #
 713 do_test index-20.1 {
 714   execsql {
 715     CREATE INDEX "t6i2" ON t6(c);
 716     DROP INDEX "t6i2";
 717   }
 718 } {}
 719 do_test index-20.2 {
 720   execsql {
 721     DROP INDEX "t6i1";
 722   }
 723 } {}
 724
 725 # Try to create a TEMP index on a non-TEMP table. */
 726 #
 727 do_test index-21.1 {
 728   catchsql {
 729      CREATE INDEX temp.i21 ON t6(c);
 730   }
 731 } {1 {cannot create a TEMP index on non-TEMP table "t6"}}
 732 do_test index-21.2 {
 733   catchsql {
 734      CREATE TEMP TABLE t6(x);
 735      INSERT INTO temp.t6 values(1),(5),(9);
 736      CREATE INDEX temp.i21 ON t6(x);
 737      SELECT x FROM t6 ORDER BY x DESC;
 738   }
 739 } {0 {9 5 1}}
 740
 741 # 2019-05-01 ticket https://www.sqlite.org/src/info/3be1295b264be2fa
 742 do_execsql_test index-22.0 {
 743   DROP TABLE IF EXISTS t1;
 744   CREATE TABLE t1(a, b TEXT);
 745   CREATE UNIQUE INDEX IF NOT EXISTS x1 ON t1(b==0);
 746   CREATE INDEX IF NOT EXISTS x2 ON t1(a || 0) WHERE b;
 747   INSERT INTO t1(a,b) VALUES('a',1),('a',0);
 748   SELECT a, b, '|' FROM t1;
 749 } {a 1 | a 0 |}
 750
 751 # 2019-05-10 ticket https://www.sqlite.org/src/info/ae0f637bddc5290b
 752 do_execsql_test index-23.0 {
 753   DROP TABLE t1;
 754   CREATE TABLE t1(a TEXT, b REAL);
 755   CREATE UNIQUE INDEX t1x1 ON t1(a GLOB b);
 756   INSERT INTO t1(a,b) VALUES('0.0','1'),('1.0','1');
 757   SELECT * FROM t1;
 758   REINDEX;
 759 } {0.0 1.0 1.0 1.0}
 760 do_execsql_test index-23.1 {
 761   DROP TABLE t1;
 762   CREATE TABLE t1(a REAL);
 763   CREATE UNIQUE INDEX index_0 ON t1(TYPEOF(a));
 764   INSERT OR IGNORE INTO t1(a) VALUES (0.1),(FALSE);
 765   SELECT * FROM t1;
 766   REINDEX;
 767 } {0.1}
 768
 769 finish_test