SQLite

Check-in [4b8230e8fe]
Login

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

Overview
Comment:Add further tests to skipscan5.test.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | stat4-skipscan
Files: files | file ages | folders
SHA1: 4b8230e8fe93e73a615a46708aed5fa3557b6228
User & Date: dan 2014-06-28 17:35:15.306
Context
2014-06-28
19:06
Add an OOM fault injection test for the new code on this branch. (Closed-Leaf check-in: c96de490ac user: dan tags: stat4-skipscan)
17:35
Add further tests to skipscan5.test. (check-in: 4b8230e8fe 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: ef5cdf949b user: drh tags: stat4-skipscan)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/skipscan5.test.
︙︙
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59




60
61
62
63
64
65


66

67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

99
100
101
102
103
104
105

    set a [expr int(rand()*4.0) + 1]
    set b [expr int(rand()*20.0) + 1]
    execsql { INSERT INTO t1 VALUES($a, $b, NULL) }
  }
  execsql ANALYZE
} {}

do_eqp_test 1.3 {
  SELECT * FROM t1 WHERE b = 5;
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b=?)}
}

do_eqp_test 1.4 {
  SELECT * FROM t1 WHERE b > 12 AND b < 16;
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>? AND b<?)}
}

do_eqp_test 1.5 {
  SELECT * FROM t1 WHERE b > 2 AND b < 16;
} {
  0 0 0 {SCAN TABLE t1}
}

do_eqp_test 1.6 {




  SELECT * FROM t1 WHERE b > 18 AND b < 25;
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>? AND b<?)}
}

do_eqp_test 1.7 {


  SELECT * FROM t1 WHERE b > 18 AND b < 25;

} {
  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>? AND b<?)}
}

do_eqp_test 1.8 {
  SELECT * FROM t1 WHERE b > 15;
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>?)}
}

do_eqp_test 1.9 {
  SELECT * FROM t1 WHERE b > 5;
} {
  0 0 0 {SCAN TABLE t1}
}

do_eqp_test 1.10 {
  SELECT * FROM t1 WHERE b < 5;
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b<?)}
}

do_eqp_test 1.11 {
  SELECT * FROM t1 WHERE b < 15;
} {
  0 0 0 {SCAN TABLE t1}
}

#-------------------------------------------------------------------------
# Test that range-query/skip-scan estimation works with text values.
# And on UTF-16 databases when there is no UTF-16 collation sequence
# available.


proc test_collate {enc lhs rhs} {
  string compare $lhs $rhs
}

foreach {tn dbenc coll} {
  1 UTF-8   { add_test_collate db 0 0 1 }








<
<
|
|
<
|
<
|
<
|
<
|
<
<
<
|
<
|
<
>
>
>
>
|
<
|
<
|
<
>
>
|
>

<
<
<
<
<
<
<
<
<
<
|
<
<
<
|
<
<
<
<


<
<
<
<
<





>








34
35
36
37
38
39
40


41
42

43

44

45

46



47

48

49
50
51
52
53

54

55

56
57
58
59
60










61



62




63
64





65
66
67
68
69
70
71
72
73
74
75
76
77

    set a [expr int(rand()*4.0) + 1]
    set b [expr int(rand()*20.0) + 1]
    execsql { INSERT INTO t1 VALUES($a, $b, NULL) }
  }
  execsql ANALYZE
} {}



foreach {tn q res} {
  1  "b = 5"                   {/*ANY(a) AND b=?*/}

  2  "b > 12 AND b < 16"       {/*ANY(a) AND b>? AND b<?*/}

  3  "b > 2 AND b < 16"        {/*SCAN TABLE t1*/}

  4  "b > 18 AND b < 25"       {/*ANY(a) AND b>? AND b<?*/}

  5  "b > 15"                  {/*ANY(a) AND b>?*/}



  6  "b > 5"                   {/*SCAN TABLE t1*/}

  7  "b < 15"                  {/*SCAN TABLE t1*/}

  8  "b < 5"                   {/*ANY(a) AND b<?*/}
  9  "5 > b"                   {/*ANY(a) AND b<?*/}
  10 "b = '5'"                 {/*ANY(a) AND b=?*/}
  11 "b > '12' AND b < '16'"   {/*ANY(a) AND b>? AND b<?*/}
  12 "b > '2' AND b < '16'"    {/*SCAN TABLE t1*/}

  13 "b > '18' AND b < '25'"   {/*ANY(a) AND b>? AND b<?*/}

  14 "b > '15'"                {/*ANY(a) AND b>?*/}

  15 "b > '5'"                 {/*SCAN TABLE t1*/}
  16 "b < '15'"                {/*SCAN TABLE t1*/}
  17 "b < '5'"                 {/*ANY(a) AND b<?*/}
  18 "'5' > b"                 {/*ANY(a) AND b<?*/}
} {










  set sql "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE $q"



  do_execsql_test 1.3.$tn $sql $res




}







#-------------------------------------------------------------------------
# Test that range-query/skip-scan estimation works with text values.
# And on UTF-16 databases when there is no UTF-16 collation sequence
# available.
#

proc test_collate {enc lhs rhs} {
  string compare $lhs $rhs
}

foreach {tn dbenc coll} {
  1 UTF-8   { add_test_collate db 0 0 1 }
︙︙
139
140
141
142
143
144
145
146































































147
148
149
150
151

    4 { c < 'e' }                     {/*ANY(a) AND ANY(b) AND c<?*/}
  } {
    set sql "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE $q" 
    do_execsql_test 2.$tn.$tn2 $sql $res
  }

}
































































finish_test













>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>






111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186

    4 { c < 'e' }                     {/*ANY(a) AND ANY(b) AND c<?*/}
  } {
    set sql "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE $q" 
    do_execsql_test 2.$tn.$tn2 $sql $res
  }

}

#-------------------------------------------------------------------------
# Test that range-query/skip-scan estimation works on columns that contain
# a variety of types.
#

reset_db
do_execsql_test 3.1 {
  CREATE TABLE t3(a, b, c);
  CREATE INDEX i3 ON t3(a, b);
}

set values {
    NULL NULL NULL
    NULL -9567 -9240
    -8725 -8659 -8248.340244520614
    -8208 -7939 -7746.985758536954
    -7057 -6550 -5916
    -5363 -4935.781822975623 -4935.063633571875
    -3518.4554911770183 -2537 -2026
    -1511.2603881914456 -1510.4195994839156 -1435
    -1127.4210136045804 -1045 99
    1353 1457 1563.2908193223611
    2245 2286 2552
    2745.18831295203 2866.279926554429 3075.0468527316334
    3447 3867 4237.892420141907
    4335 5052.9775000424015 5232.178240656935
    5541.784919585003 5749.725576373621 5758
    6005 6431 7263.477992854769
    7441 7541 8667.279760663994
    8857 9199.638673662972 'dl'
    'dro' 'h' 'igprfq'
    'jnbd' 'k' 'kordee'
    'lhwcv' 'mzlb' 'nbjked'
    'nufpo' 'nxqkdq' 'shelln'
    'tvzn' 'wpnt' 'wylf'
    'ydkgu' 'zdb' X''
    X'0a' X'203f6429f1f33f' X'23858e324545e0362b'
    X'3f9f8a' X'516f7ddd4b' X'68f1df0930ac6b'
    X'9ea60d' X'a06f' X'aefd342a39ce36df'
    X'afaa020fe2' X'be201c' X'c47d97b209601e45'
}

do_test 3.2 {
  set c 0
  foreach v $values {
    execsql "INSERT INTO t3 VALUES($c % 2, $v, $c)"
    incr c
  }
  execsql ANALYZE
} {}

foreach {tn q res} {
  1 "b BETWEEN -10000 AND -8000"       {/*ANY(a) AND b>? AND b<?*/}
  2 "b BETWEEN -10000 AND 'qqq'"       {/*SCAN TABLE t3*/}
  3 "b < X'5555'"                      {/*SCAN TABLE t3*/}
  4 "b > X'5555'"                      {/*ANY(a) AND b>?*/}
  5 "b > 'zzz'"                        {/*ANY(a) AND b>?*/}
  6 "b < 'zzz'"                        {/*SCAN TABLE t3*/}
} {
  set sql "EXPLAIN QUERY PLAN SELECT * FROM t3 WHERE $q" 
  do_execsql_test 3.3.$tn $sql $res
}

finish_test