test/skipscan5.test

   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
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19 set testprefix skipscan5
  20
  21 ifcapable !stat4 {
  22   finish_test
  23   return
  24 }
  25
  26 do_execsql_test 1.1 {
  27   CREATE TABLE t1(a INT, b INT, c INT);
  28   CREATE INDEX i1 ON t1(a, b);
  29 } {}
  30
  31 expr srand(4)
  32 do_test 1.2 {
  33   for {set i 0} {$i < 100} {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
  41 foreach {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 TABLE t1*/}
  45   4  "b > 18 AND b < 25"       {/*ANY(a) AND b>? AND b<?*/}
  46   5  "b > 15"                  {/*ANY(a) AND b>?*/}
  47   6  "b > 5"                   {/*SCAN TABLE t1*/}
  48   7  "b < 15"                  {/*SCAN TABLE 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 TABLE t1*/}
  54   13 "b > '18' AND b < '25'"   {/*ANY(a) AND b>? AND b<?*/}
  55   14 "b > '15'"                {/*ANY(a) AND b>?*/}
  56   15 "b > '5'"                 {/*SCAN TABLE t1*/}
  57   16 "b < '15'"                {/*SCAN TABLE 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
  72 proc test_collate {enc lhs rhs} {
  73   string compare $lhs $rhs
  74 }
  75
  76 foreach {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 TABLE t2*/}
 108     3 { c > 'q' }                     {/*ANY(a) AND ANY(b) AND c>?*/}
 109     4 { c > 'e' }                     {/*SCAN TABLE t2*/}
 110     5 { c < 'q' }                     {/*SCAN TABLE t2*/}
 111     6 { c < 'c' }                     {/*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
 124 reset_db
 125 do_execsql_test 3.1 {
 126   CREATE TABLE t3(a, b, c);
 127   CREATE INDEX i3 ON t3(a, b);
 128 }
 129
 130 set 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
 161 do_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
 170 foreach {tn q res} {
 171   1 "b BETWEEN -10000 AND -8000"       {/*ANY(a) AND b>? AND b<?*/}
 172   2 "b BETWEEN -10000 AND 'qqq'"       {/*SCAN TABLE t3*/}
 173   3 "b < X'5555'"                      {/*SCAN TABLE t3*/}
 174   4 "b > X'5555'"                      {/*ANY(a) AND b>?*/}
 175   5 "b > 'zzz'"                        {/*ANY(a) AND b>?*/}
 176   6 "b < 'zzz'"                        {/*SCAN TABLE t3*/}
 177 } {
 178   set sql "EXPLAIN QUERY PLAN SELECT * FROM t3 WHERE $q"
 179   do_execsql_test 3.3.$tn $sql $res
 180 }
 181
 182 finish_test