gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/skipscan3.test (about) 1 # 2014-08-20 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 tests of the "skip-scan" query strategy. 13 # In particular, this file looks at skipping intermediate terms 14 # in an index. For example, if (a,b,c) are indexed, and we have 15 # "WHERE a=?1 AND c=?2" - verify that skip-scan can still be used. 16 # 17 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 21 do_execsql_test skipscan3-1.1 { 22 CREATE TABLE t1(a,b,c,d,PRIMARY KEY(a,b,c)); 23 WITH RECURSIVE 24 c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000) 25 INSERT INTO t1(a,b,c,d) 26 SELECT 1, 1, x, printf('x%04d',x) FROM c; 27 ANALYZE; 28 } {} 29 30 # This version has long used skip-scan because of the "+a" 31 # 32 do_execsql_test skipscan3-1.2eqp { 33 EXPLAIN QUERY PLAN SELECT d FROM t1 WHERE +a=1 AND c=32; 34 } {/*ANY(a) AND ANY(b)*/} 35 do_execsql_test skipscan3-1.2 { 36 SELECT d FROM t1 WHERE +a=1 AND c=32; 37 } {x0032} 38 39 # This version (with "a" instead of "+a") should use skip-scan but 40 # did not prior to changes implemented on 2014-08-20 41 # 42 do_execsql_test skipscan3-1.3eqp { 43 EXPLAIN QUERY PLAN SELECT d FROM t1 WHERE a=1 AND c=32; 44 } {/*ANY(a) AND ANY(b)*/} 45 do_execsql_test skipscan3-1.3 { 46 SELECT d FROM t1 WHERE a=1 AND c=32; 47 } {x0032} 48 49 # Repeat the test on a WITHOUT ROWID table 50 # 51 do_execsql_test skipscan3-2.1 { 52 CREATE TABLE t2(a,b,c,d,PRIMARY KEY(a,b,c)) WITHOUT ROWID; 53 WITH RECURSIVE 54 c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000) 55 INSERT INTO t2(a,b,c,d) 56 SELECT 1, 1, x, printf('x%04d',x) FROM c; 57 ANALYZE; 58 } {} 59 do_execsql_test skipscan3-2.2eqp { 60 EXPLAIN QUERY PLAN SELECT d FROM t2 WHERE +a=1 AND c=32; 61 } {/*ANY(a) AND ANY(b)*/} 62 do_execsql_test skipscan3-2.2 { 63 SELECT d FROM t2 WHERE +a=1 AND c=32; 64 } {x0032} 65 do_execsql_test skipscan3-2.3eqp { 66 EXPLAIN QUERY PLAN SELECT d FROM t2 WHERE a=1 AND c=32; 67 } {/*ANY(a) AND ANY(b)*/} 68 do_execsql_test skipscan3-2.3 { 69 SELECT d FROM t2 WHERE a=1 AND c=32; 70 } {x0032} 71 72 73 finish_test