# 2012 September 18 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test in5-1.1 { execsql { CREATE TABLE t1x(x INTEGER PRIMARY KEY); INSERT INTO t1x VALUES(1),(3),(5),(7),(9); CREATE TABLE t1y(y INTEGER UNIQUE); INSERT INTO t1y VALUES(2),(4),(6),(8); CREATE TABLE t1z(z TEXT UNIQUE); INSERT INTO t1z VALUES('a'),('c'),('e'),('g'); CREATE TABLE t2(a INTEGER, b INTEGER, c TEXT, d TEXT); INSERT INTO t2 VALUES(1,2,'a','12a'),(1,2,'b','12b'), (2,3,'g','23g'),(3,5,'c','35c'), (4,6,'h','46h'),(5,6,'e','56e'); CREATE TABLE t3x AS SELECT x FROM t1x; CREATE TABLE t3y AS SELECT y FROM t1y; CREATE TABLE t3z AS SELECT z FROM t1z; SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY c; } } {12a 56e} do_test in5-1.2 { execsql { SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d; } } {23g} do_test in5-1.3 { execsql { SELECT d FROM t2 WHERE a IN t3x AND b IN t3y AND c IN t3z ORDER BY d; } } {12a 56e} do_test in5-2.1 { execsql { CREATE INDEX t2abc ON t2(a,b,c); SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d; } } {12a 56e} do_test in5-2.2 { execsql { SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d; } } {23g} do_test in5-2.3 { regexp {OpenEphemeral} [db eval { EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z }] } {0} do_test in5-2.4 { execsql { SELECT d FROM t2 WHERE a IN t3x AND b IN t3y AND c IN t3z ORDER BY d; } } {12a 56e} do_test in5-2.5.1 { regexp {OpenEphemeral} [db eval { EXPLAIN SELECT d FROM t2 WHERE a IN t3x AND b IN t1y AND c IN t1z }] } {1} do_test in5-2.5.2 { regexp {OpenEphemeral} [db eval { EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t3y AND c IN t1z }] } {1} do_test in5-2.5.3 { regexp {OpenEphemeral} [db eval { EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t3z }] } {1} do_test in5-3.1 { execsql { DROP INDEX t2abc; CREATE INDEX t2ab ON t2(a,b); SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d; } } {12a 56e} do_test in5-3.2 { execsql { SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d; } } {23g} do_test in5-3.3 { regexp {OpenEphemeral} [db eval { EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z }] } {0} do_test in5-4.1 { execsql { DROP INDEX t2ab; CREATE INDEX t2abcd ON t2(a,b,c,d); SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d; } } {12a 56e} do_test in5-4.2 { execsql { SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d; } } {23g} do_test in5-4.3 { regexp {OpenEphemeral} [db eval { EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z }] } {0} do_test in5-5.1 { execsql { DROP INDEX t2abcd; CREATE INDEX t2cbad ON t2(c,b,a,d); SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z ORDER BY d; } } {12a 56e} do_test in5-5.2 { execsql { SELECT d FROM t2 WHERE a IN t1y AND b IN t1x AND c IN t1z ORDER BY d; } } {23g} do_test in5-5.3 { regexp {OpenEphemeral} [db eval { EXPLAIN SELECT d FROM t2 WHERE a IN t1x AND b IN t1y AND c IN t1z }] } {0} finish_test