000001  # 2008-10-04
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  
000013  set testdir [file dirname $argv0]
000014  source $testdir/tester.tcl
000015  set ::testprefix indexedby
000016  
000017  # Create a schema with some indexes.
000018  #
000019  do_test indexedby-1.1 {
000020    execsql {
000021      CREATE TABLE t1(a, b);
000022      CREATE INDEX i1 ON t1(a);
000023      CREATE INDEX i2 ON t1(b);
000024  
000025      CREATE TABLE t2(c, d);
000026      CREATE INDEX i3 ON t2(c);
000027      CREATE INDEX i4 ON t2(d);
000028  
000029      CREATE TABLE t3(e PRIMARY KEY, f);
000030  
000031      CREATE VIEW v1 AS SELECT * FROM t1;
000032    }
000033  } {}
000034  
000035  # Explain Query Plan
000036  #
000037  proc EQP {sql} {
000038    uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
000039  }
000040  
000041  # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
000042  #
000043  do_execsql_test indexedby-1.2 {
000044    EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; 
000045  } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000046  do_execsql_test indexedby-1.3 {
000047    EXPLAIN QUERY PLAN select * from t1 ; 
000048  } {0 0 0 {SCAN TABLE t1}}
000049  do_execsql_test indexedby-1.4 {
000050    EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; 
000051  } {
000052    0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)} 
000053    0 1 0 {SCAN TABLE t1}
000054  }
000055  
000056  # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 
000057  # attached to a table in the FROM clause, but not to a sub-select or
000058  # SQL view. Also test that specifying an index that does not exist or
000059  # is attached to a different table is detected as an error.
000060  #
000061  # EVIDENCE-OF: R-07004-11522 -- syntax diagram qualified-table-name
000062  # 
000063  # EVIDENCE-OF: R-58230-57098 The "INDEXED BY index-name" phrase
000064  # specifies that the named index must be used in order to look up values
000065  # on the preceding table.
000066  #
000067  do_test indexedby-2.1 {
000068    execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
000069  } {}
000070  do_test indexedby-2.1b {
000071    execsql { SELECT * FROM main.t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
000072  } {}
000073  do_test indexedby-2.2 {
000074    execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
000075  } {}
000076  do_test indexedby-2.2b {
000077    execsql { SELECT * FROM main.t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
000078  } {}
000079  do_test indexedby-2.3 {
000080    execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'}
000081  } {}
000082  # EVIDENCE-OF: R-44699-55558 The INDEXED BY clause does not give the
000083  # optimizer hints about which index to use; it gives the optimizer a
000084  # requirement of which index to use.
000085  # EVIDENCE-OF: R-15800-25719 If index-name does not exist or cannot be
000086  # used for the query, then the preparation of the SQL statement fails.
000087  #
000088  do_test indexedby-2.4 {
000089    catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'}
000090  } {1 {no such index: i3}}
000091  
000092  # EVIDENCE-OF: R-62112-42456 If the query optimizer is unable to use the
000093  # index specified by the INDEX BY clause, then the query will fail with
000094  # an error.
000095  do_test indexedby-2.4.1 {
000096    catchsql { SELECT b FROM t1 INDEXED BY i1 WHERE b = 'two' }
000097  } {1 {no query solution}}
000098  
000099  do_test indexedby-2.5 {
000100    catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'}
000101  } {1 {no such index: i5}}
000102  do_test indexedby-2.6 {
000103    catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'}
000104  } {1 {near "WHERE": syntax error}}
000105  do_test indexedby-2.7 {
000106    catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
000107  } {1 {no such index: i1}}
000108  
000109  
000110  # Tests for single table cases.
000111  #
000112  # EVIDENCE-OF: R-37002-28871 The "NOT INDEXED" clause specifies that no
000113  # index shall be used when accessing the preceding table, including
000114  # implied indices create by UNIQUE and PRIMARY KEY constraints. However,
000115  # the rowid can still be used to look up entries even when "NOT INDEXED"
000116  # is specified.
000117  #
000118  do_execsql_test indexedby-3.1 {
000119    EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a = 'one' AND b = 'two'
000120  } {/SEARCH TABLE t1 USING INDEX/}
000121  do_execsql_test indexedby-3.1.1 {
000122    EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
000123  } {0 0 0 {SCAN TABLE t1}}
000124  do_execsql_test indexedby-3.1.2 {
000125    EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE rowid=1
000126  } {/SEARCH TABLE t1 USING INTEGER PRIMARY KEY .rowid=/}
000127  
000128  
000129  do_execsql_test indexedby-3.2 {
000130    EXPLAIN QUERY PLAN 
000131    SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'
000132  } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000133  do_execsql_test indexedby-3.3 {
000134    EXPLAIN QUERY PLAN 
000135    SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'
000136  } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
000137  do_test indexedby-3.4 {
000138    catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
000139  } {1 {no query solution}}
000140  do_test indexedby-3.5 {
000141    catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
000142  } {1 {no query solution}}
000143  do_test indexedby-3.6 {
000144    catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
000145  } {0 {}}
000146  do_test indexedby-3.7 {
000147    catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
000148  } {0 {}}
000149  
000150  do_execsql_test indexedby-3.8 {
000151    EXPLAIN QUERY PLAN 
000152    SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e 
000153  } {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1}}
000154  do_execsql_test indexedby-3.9 {
000155    EXPLAIN QUERY PLAN 
000156    SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 
000157  } {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?)}}
000158  do_test indexedby-3.10 {
000159    catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
000160  } {1 {no query solution}}
000161  do_test indexedby-3.11 {
000162    catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
000163  } {1 {no such index: sqlite_autoindex_t3_2}}
000164  
000165  # Tests for multiple table cases.
000166  #
000167  do_execsql_test indexedby-4.1 {
000168    EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c 
000169  } {
000170    0 0 0 {SCAN TABLE t1} 
000171    0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)}
000172  }
000173  do_execsql_test indexedby-4.2 {
000174    EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 
000175  } {
000176    0 0 1 {SCAN TABLE t2} 
000177    0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
000178  }
000179  do_test indexedby-4.3 {
000180    catchsql {
000181      SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c
000182    }
000183  } {1 {no query solution}}
000184  do_test indexedby-4.4 {
000185    catchsql {
000186      SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c
000187    }
000188  } {1 {no query solution}}
000189  
000190  # Test embedding an INDEXED BY in a CREATE VIEW statement. This block
000191  # also tests that nothing bad happens if an index refered to by
000192  # a CREATE VIEW statement is dropped and recreated.
000193  #
000194  do_execsql_test indexedby-5.1 {
000195    CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
000196    EXPLAIN QUERY PLAN SELECT * FROM v2 
000197  } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
000198  do_execsql_test indexedby-5.2 {
000199    EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 
000200  } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
000201  do_test indexedby-5.3 {
000202    execsql { DROP INDEX i1 }
000203    catchsql { SELECT * FROM v2 }
000204  } {1 {no such index: i1}}
000205  do_test indexedby-5.4 {
000206    # Recreate index i1 in such a way as it cannot be used by the view query.
000207    execsql { CREATE INDEX i1 ON t1(b) }
000208    catchsql { SELECT * FROM v2 }
000209  } {1 {no query solution}}
000210  do_test indexedby-5.5 {
000211    # Drop and recreate index i1 again. This time, create it so that it can
000212    # be used by the query.
000213    execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
000214    catchsql { SELECT * FROM v2 }
000215  } {0 {}}
000216  
000217  # Test that "NOT INDEXED" may use the rowid index, but not others.
000218  # 
000219  do_execsql_test indexedby-6.1 {
000220    EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 
000221  } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
000222  do_execsql_test indexedby-6.2 {
000223    EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 
000224  } {0 0 0 {SCAN TABLE t1}}
000225  
000226  # EVIDENCE-OF: R-40297-14464 The INDEXED BY phrase forces the SQLite
000227  # query planner to use a particular named index on a DELETE, SELECT, or
000228  # UPDATE statement.
000229  #
000230  # Test that "INDEXED BY" can be used in a DELETE statement.
000231  # 
000232  do_execsql_test indexedby-7.1 {
000233    EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 
000234  } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000235  do_execsql_test indexedby-7.2 {
000236    EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 
000237  } {0 0 0 {SCAN TABLE t1}}
000238  do_execsql_test indexedby-7.3 {
000239    EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 
000240  } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000241  do_execsql_test indexedby-7.4 {
000242    EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
000243  } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000244  do_execsql_test indexedby-7.5 {
000245    EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
000246  } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
000247  do_test indexedby-7.6 {
000248    catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
000249  } {1 {no query solution}}
000250  
000251  # Test that "INDEXED BY" can be used in an UPDATE statement.
000252  # 
000253  do_execsql_test indexedby-8.1 {
000254    EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 
000255  } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
000256  do_execsql_test indexedby-8.2 {
000257    EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 
000258  } {0 0 0 {SCAN TABLE t1}}
000259  do_execsql_test indexedby-8.3 {
000260    EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 
000261  } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
000262  do_execsql_test indexedby-8.4 {
000263    EXPLAIN QUERY PLAN 
000264    UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10
000265  } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
000266  do_execsql_test indexedby-8.5 {
000267    EXPLAIN QUERY PLAN 
000268    UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10
000269  } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
000270  do_test indexedby-8.6 {
000271    catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
000272  } {1 {no query solution}}
000273  
000274  # Test that bug #3560 is fixed.
000275  #
000276  do_test indexedby-9.1 {
000277    execsql {
000278      CREATE TABLE maintable( id integer);
000279      CREATE TABLE joinme(id_int integer, id_text text);
000280      CREATE INDEX joinme_id_text_idx on joinme(id_text);
000281      CREATE INDEX joinme_id_int_idx on joinme(id_int);
000282    }
000283  } {}
000284  do_test indexedby-9.2 {
000285    catchsql {
000286      select * from maintable as m inner join
000287      joinme as j indexed by joinme_id_text_idx
000288      on ( m.id  = j.id_int)
000289    }
000290  } {1 {no query solution}}
000291  do_test indexedby-9.3 {
000292    catchsql { select * from maintable, joinme INDEXED by joinme_id_text_idx }
000293  } {1 {no query solution}}
000294  
000295  # Make sure we can still create tables, indices, and columns whose name
000296  # is "indexed".
000297  #
000298  do_test indexedby-10.1 {
000299    execsql {
000300      CREATE TABLE indexed(x,y);
000301      INSERT INTO indexed VALUES(1,2);
000302      SELECT * FROM indexed;
000303    }
000304  } {1 2}
000305  do_test indexedby-10.2 {
000306    execsql {
000307      CREATE INDEX i10 ON indexed(x);
000308      SELECT * FROM indexed indexed by i10 where x>0;
000309    }
000310  } {1 2}
000311  do_test indexedby-10.3 {
000312    execsql {
000313      DROP TABLE indexed;
000314      CREATE TABLE t10(indexed INTEGER);
000315      INSERT INTO t10 VALUES(1);
000316      CREATE INDEX indexed ON t10(indexed);
000317      SELECT * FROM t10 indexed by indexed WHERE indexed>0
000318    }
000319  } {1}
000320  
000321  #-------------------------------------------------------------------------
000322  # Ensure that the rowid at the end of each index entry may be used
000323  # for equality constraints in the same way as other indexed fields.
000324  #
000325  do_execsql_test 11.1 {
000326    CREATE TABLE x1(a, b TEXT);
000327    CREATE INDEX x1i ON x1(a, b);
000328    INSERT INTO x1 VALUES(1, 1);
000329    INSERT INTO x1 VALUES(1, 1);
000330    INSERT INTO x1 VALUES(1, 1);
000331    INSERT INTO x1 VALUES(1, 1);
000332  }
000333  do_execsql_test 11.2 {
000334    SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid=3;
000335  } {1 1 3}
000336  do_execsql_test 11.3 {
000337    SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3';
000338  } {1 1 3}
000339  do_execsql_test 11.4 {
000340    SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0';
000341  } {1 1 3}
000342  do_eqp_test 11.5 {
000343    SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0';
000344  } {0 0 0 {SEARCH TABLE x1 USING COVERING INDEX x1i (a=? AND b=? AND rowid=?)}}
000345  
000346  do_execsql_test 11.6 {
000347    CREATE TABLE x2(c INTEGER PRIMARY KEY, a, b TEXT);
000348    CREATE INDEX x2i ON x2(a, b);
000349    INSERT INTO x2 VALUES(1, 1, 1);
000350    INSERT INTO x2 VALUES(2, 1, 1);
000351    INSERT INTO x2 VALUES(3, 1, 1);
000352    INSERT INTO x2 VALUES(4, 1, 1);
000353  }
000354  do_execsql_test 11.7 {
000355    SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c=3;
000356  } {1 1 3}
000357  do_execsql_test 11.8 {
000358    SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3';
000359  } {1 1 3}
000360  do_execsql_test 11.9 {
000361    SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
000362  } {1 1 3}
000363  do_eqp_test 11.10 {
000364    SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
000365  } {0 0 0 {SEARCH TABLE x2 USING COVERING INDEX x2i (a=? AND b=? AND rowid=?)}}
000366  
000367  finish_test