AryaWu/sqlite

1# 2013-11-132#3# The author disclaims copyright to this source code.  In place of4# 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 query14# that uses the skip-scan approach.15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set testprefix skipscan520 21ifcapable !stat4 {22  finish_test23  return24}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 ANALYZE39} {}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 $res63}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 sequence69# available.70#71 72proc test_collate {enc lhs rhs} {73  string compare $lhs $rhs74}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_db82  eval $coll83 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 ANALYZE103  } {}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 $res115  }116 117}118 119#-------------------------------------------------------------------------120# Test that range-query/skip-scan estimation works on columns that contain121# a variety of types.122#123 124reset_db125do_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 NULL132    NULL -9567 -9240133    -8725 -8659 -8248.340244520614134    -8208 -7939 -7746.985758536954135    -7057 -6550 -5916136    -5363 -4935.781822975623 -4935.063633571875137    -3518.4554911770183 -2537 -2026138    -1511.2603881914456 -1510.4195994839156 -1435139    -1127.4210136045804 -1045 99140    1353 1457 1563.2908193223611141    2245 2286 2552142    2745.18831295203 2866.279926554429 3075.0468527316334143    3447 3867 4237.892420141907144    4335 5052.9775000424015 5232.178240656935145    5541.784919585003 5749.725576373621 5758146    6005 6431 7263.477992854769147    7441 7541 8667.279760663994148    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 0163  foreach v $values {164    execsql "INSERT INTO t3 VALUES($c % 2, $v, $c)"165    incr c166  }167  execsql ANALYZE168} {}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 $res180}181 182finish_test183