1# 2013-11-13 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. In 13# particular it tests that stat4 data can be used by a range query 14# that uses the skip-scan approach. 15# 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19set testprefix skipscan5 20 21ifcapable !stat4 { 22 finish_test 23 return 24} 25 26do_execsql_test 1.1 { 27 CREATE TABLE t1(a INT, b INT, c INT); 28 CREATE INDEX i1 ON t1(a, b); 29} {} 30 31expr srand(4) 32do_test 1.2 { 33 for {set i 0} {$i < 1000} {incr i} { 34 set a [expr int(rand()*4.0) + 1] 35 set b [expr int(rand()*20.0) + 1] 36 execsql { INSERT INTO t1 VALUES($a, $b, NULL) } 37 } 38 execsql ANALYZE 39} {} 40 41foreach {tn q res} { 42 1 "b = 5" {/*ANY(a) AND b=?*/} 43 2 "b > 12 AND b < 16" {/*ANY(a) AND b>? AND b<?*/} 44 3 "b > 2 AND b < 16" {/*SCAN t1*/} 45 4 "b > 18 AND b < 25" {/*ANY(a) AND b>? AND b<?*/} 46 5 "b > 16" {/*ANY(a) AND b>?*/} 47 6 "b > 5" {/*SCAN t1*/} 48 7 "b < 15" {/*SCAN t1*/} 49 8 "b < 5" {/*ANY(a) AND b<?*/} 50 9 "5 > b" {/*ANY(a) AND b<?*/} 51 10 "b = '5'" {/*ANY(a) AND b=?*/} 52 11 "b > '12' AND b < '16'" {/*ANY(a) AND b>? AND b<?*/} 53 12 "b > '2' AND b < '16'" {/*SCAN t1*/} 54 13 "b > '18' AND b < '25'" {/*ANY(a) AND b>? AND b<?*/} 55 14 "b > '16'" {/*ANY(a) AND b>?*/} 56 15 "b > '5'" {/*SCAN t1*/} 57 16 "b < '15'" {/*SCAN t1*/} 58 17 "b < '5'" {/*ANY(a) AND b<?*/} 59 18 "'5' > b" {/*ANY(a) AND b<?*/} 60} { 61 set sql "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE $q" 62 do_execsql_test 1.3.$tn $sql $res 63} 64 65 66#------------------------------------------------------------------------- 67# Test that range-query/skip-scan estimation works with text values. 68# And on UTF-16 databases when there is no UTF-16 collation sequence 69# available. 70# 71 72proc test_collate {enc lhs rhs} { 73 string compare $lhs $rhs 74} 75 76foreach {tn dbenc coll} { 77 1 UTF-8 { add_test_collate db 0 0 1 } 78 2 UTF-16 { add_test_collate db 1 0 0 } 79 3 UTF-8 { add_test_collate db 0 1 0 } 80} { 81 reset_db 82 eval $coll 83 84 do_execsql_test 2.$tn.1 " PRAGMA encoding = '$dbenc' " 85 do_execsql_test 2.$tn.2 { 86 CREATE TABLE t2(a TEXT, b TEXT, c TEXT COLLATE test_collate, d TEXT); 87 CREATE INDEX i2 ON t2(a, b, c); 88 } 89 90 set vocab(d) { :) } 91 set vocab(c) { a b c d e f g h i j k l m n o p q r s t } 92 set vocab(b) { one two three } 93 set vocab(a) { sql } 94 95 do_test 2.$tn.3 { 96 for {set i 0} {$i < 100} {incr i} { 97 foreach var {a b c d} { 98 set $var [lindex $vocab($var) [expr $i % [llength $vocab($var)]]] 99 } 100 execsql { INSERT INTO t2 VALUES($a, $b, $c, $d) } 101 } 102 execsql ANALYZE 103 } {} 104 105 foreach {tn2 q res} { 106 1 { c BETWEEN 'd' AND 'e' } {/*ANY(a) AND ANY(b) AND c>? AND c<?*/} 107 2 { c BETWEEN 'b' AND 'r' } {/*SCAN t2*/} 108 3 { c > 'q' } {/*ANY(a) AND ANY(b) AND c>?*/} 109 4 { c > 'e' } {/*SCAN t2*/} 110 5 { c < 'q' } {/*SCAN t2*/} 111 6 { c < 'b' } {/*ANY(a) AND ANY(b) AND c<?*/} 112 } { 113 set sql "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE $q" 114 do_execsql_test 2.$tn.$tn2 $sql $res 115 } 116 117} 118 119#------------------------------------------------------------------------- 120# Test that range-query/skip-scan estimation works on columns that contain 121# a variety of types. 122# 123 124reset_db 125do_execsql_test 3.1 { 126 CREATE TABLE t3(a, b, c); 127 CREATE INDEX i3 ON t3(a, b); 128} 129 130set values { 131 NULL NULL NULL 132 NULL -9567 -9240 133 -8725 -8659 -8248.340244520614 134 -8208 -7939 -7746.985758536954 135 -7057 -6550 -5916 136 -5363 -4935.781822975623 -4935.063633571875 137 -3518.4554911770183 -2537 -2026 138 -1511.2603881914456 -1510.4195994839156 -1435 139 -1127.4210136045804 -1045 99 140 1353 1457 1563.2908193223611 141 2245 2286 2552 142 2745.18831295203 2866.279926554429 3075.0468527316334 143 3447 3867 4237.892420141907 144 4335 5052.9775000424015 5232.178240656935 145 5541.784919585003 5749.725576373621 5758 146 6005 6431 7263.477992854769 147 7441 7541 8667.279760663994 148 8857 9199.638673662972 'dl' 149 'dro' 'h' 'igprfq' 150 'jnbd' 'k' 'kordee' 151 'lhwcv' 'mzlb' 'nbjked' 152 'nufpo' 'nxqkdq' 'shelln' 153 'tvzn' 'wpnt' 'wylf' 154 'ydkgu' 'zdb' X'' 155 X'0a' X'203f6429f1f33f' X'23858e324545e0362b' 156 X'3f9f8a' X'516f7ddd4b' X'68f1df0930ac6b' 157 X'9ea60d' X'a06f' X'aefd342a39ce36df' 158 X'afaa020fe2' X'be201c' X'c47d97b209601e45' 159} 160 161do_test 3.2 { 162 set c 0 163 foreach v $values { 164 execsql "INSERT INTO t3 VALUES($c % 2, $v, $c)" 165 incr c 166 } 167 execsql ANALYZE 168} {} 169 170foreach {tn q res} { 171 1 "b BETWEEN -10000 AND -8000" {/*ANY(a) AND b>? AND b<?*/} 172 2 "b BETWEEN -10000 AND 'qqq'" {/*SCAN t3*/} 173 3 "b < X'5555'" {/*SCAN t3*/} 174 4 "b > X'5555'" {/*ANY(a) AND b>?*/} 175 5 "b > 'zzz'" {/*ANY(a) AND b>?*/} 176 6 "b < 'zzz'" {/*SCAN t3*/} 177} { 178 set sql "EXPLAIN QUERY PLAN SELECT * FROM t3 WHERE $q" 179 do_execsql_test 3.3.$tn $sql $res 180} 181 182finish_test 183