1# 2014-06-17 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# This file implements regression tests for SQLite library. The 13# focus of this script is testing automatic index creation logic, 14# and specifically that an automatic index will not be created that 15# shadows a declared index. 16# 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21# The t1b and t2d indexes are not very selective. It used to be that 22# the autoindex mechanism would create automatic indexes on t1(b) or 23# t2(d), make assumptions that they were reasonably selective, and use 24# them instead of t1b or t2d. But that would be cheating, because the 25# automatic index cannot be any more selective than the real index. 26# 27# This test verifies that the cheat is no longer allowed. 28# 29do_execsql_test autoindex3-100 { 30 CREATE TABLE t1(a,b,x); 31 CREATE TABLE t2(c,d,y); 32 CREATE INDEX t1b ON t1(b); 33 CREATE INDEX t2d ON t2(d); 34 ANALYZE sqlite_master; 35 INSERT INTO sqlite_stat1 VALUES('t1','t1b','10000 500'); 36 INSERT INTO sqlite_stat1 VALUES('t2','t2d','10000 500'); 37 ANALYZE sqlite_master; 38 EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d=b; 39} {~/AUTO/} 40 41# Automatic indexes can still be used if existing indexes do not 42# participate in == constraints. 43# 44do_execsql_test autoindex3-110 { 45 EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d>b AND x=y; 46} {/AUTO/} 47do_execsql_test autoindex3-120 { 48 EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d<b AND x=y; 49} {/AUTO/} 50do_execsql_test autoindex3-130 { 51 EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IS NULL AND x=y; 52} {/AUTO/} 53do_execsql_test autoindex3-140 { 54 EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE d IN (5,b) AND x=y; 55} {/AUTO/} 56 57 58finish_test 59