/ Check-in [4b8230e8]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add further tests to skipscan5.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | stat4-skipscan
Files: files | file ages | folders
SHA1: 4b8230e8fe93e73a615a46708aed5fa3557b6228
User & Date: dan 2014-06-28 17:35:15
Context
2014-06-28
19:06
Add an OOM fault injection test for the new code on this branch. Closed-Leaf check-in: c96de490 user: dan tags: stat4-skipscan
17:35
Add further tests to skipscan5.test. check-in: 4b8230e8 user: dan tags: stat4-skipscan
16:06
Add header comments on new routines. Rework the sqlite3Stat4Column() routine so that is (in theory) able to deal with corrupt samples. check-in: ef5cdf94 user: drh tags: stat4-skipscan
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/skipscan5.test.

    34     34       set a [expr int(rand()*4.0) + 1]
    35     35       set b [expr int(rand()*20.0) + 1]
    36     36       execsql { INSERT INTO t1 VALUES($a, $b, NULL) }
    37     37     }
    38     38     execsql ANALYZE
    39     39   } {}
    40     40   
    41         -do_eqp_test 1.3 {
    42         -  SELECT * FROM t1 WHERE b = 5;
    43         -} {
    44         -  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b=?)}
    45         -}
    46         -
    47         -do_eqp_test 1.4 {
    48         -  SELECT * FROM t1 WHERE b > 12 AND b < 16;
    49         -} {
    50         -  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>? AND b<?)}
    51         -}
    52         -
    53         -do_eqp_test 1.5 {
    54         -  SELECT * FROM t1 WHERE b > 2 AND b < 16;
           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<?*/}
    55     60   } {
    56         -  0 0 0 {SCAN TABLE t1}
    57         -}
    58         -
    59         -do_eqp_test 1.6 {
    60         -  SELECT * FROM t1 WHERE b > 18 AND b < 25;
    61         -} {
    62         -  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>? AND b<?)}
           61  +  set sql "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE $q"
           62  +  do_execsql_test 1.3.$tn $sql $res
    63     63   }
    64     64   
    65         -do_eqp_test 1.7 {
    66         -  SELECT * FROM t1 WHERE b > 18 AND b < 25;
    67         -} {
    68         -  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>? AND b<?)}
    69         -}
    70         -
    71         -do_eqp_test 1.8 {
    72         -  SELECT * FROM t1 WHERE b > 15;
    73         -} {
    74         -  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>?)}
    75         -}
    76         -
    77         -do_eqp_test 1.9 {
    78         -  SELECT * FROM t1 WHERE b > 5;
    79         -} {
    80         -  0 0 0 {SCAN TABLE t1}
    81         -}
    82         -
    83         -do_eqp_test 1.10 {
    84         -  SELECT * FROM t1 WHERE b < 5;
    85         -} {
    86         -  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b<?)}
    87         -}
    88         -
    89         -do_eqp_test 1.11 {
    90         -  SELECT * FROM t1 WHERE b < 15;
    91         -} {
    92         -  0 0 0 {SCAN TABLE t1}
    93         -}
    94     65   
    95     66   #-------------------------------------------------------------------------
    96     67   # Test that range-query/skip-scan estimation works with text values.
    97     68   # And on UTF-16 databases when there is no UTF-16 collation sequence
    98     69   # available.
           70  +#
    99     71   
   100     72   proc test_collate {enc lhs rhs} {
   101     73     string compare $lhs $rhs
   102     74   }
   103     75   
   104     76   foreach {tn dbenc coll} {
   105     77     1 UTF-8   { add_test_collate db 0 0 1 }
................................................................................
   139    111       4 { c < 'e' }                     {/*ANY(a) AND ANY(b) AND c<?*/}
   140    112     } {
   141    113       set sql "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE $q" 
   142    114       do_execsql_test 2.$tn.$tn2 $sql $res
   143    115     }
   144    116   
   145    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  +}
   146    181   
   147    182   finish_test
   148    183   
   149    184   
   150    185   
   151    186