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