SQLite

Check-in [2504bcfb0c]
Login

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

Overview
Comment:Update some test cases to work with sqlite_stat3 instead of sqlite_stat2.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | stat3-enhancement
Files: files | file ages | folders
SHA1: 2504bcfb0cf14b5ce51db0af1269ac28384714e0
User & Date: drh 2011-08-15 12:58:23.538
Context
2011-08-16
01:15
Add the analyze8.test test module for sqlite_stat3. (check-in: 2c83ac89dc user: drh tags: stat3-enhancement)
2011-08-15
12:58
Update some test cases to work with sqlite_stat3 instead of sqlite_stat2. (check-in: 2504bcfb0c user: drh tags: stat3-enhancement)
12:02
Fix a couple of typos in comments in analyze.c. (check-in: ae31dc67aa user: dan tags: stat3-enhancement)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to test/alter.test.
︙
843
844
845
846
847
848
849

850
851
852
853
854
855
856

843
844
845
846
847
848
849
850
851
852
853
854
855
856
857








+








#-------------------------------------------------------------------------
# Test that it is not possible to use ALTER TABLE on any system table.
#
set system_table_list {1 sqlite_master}
catchsql ANALYZE
ifcapable analyze { lappend system_table_list 2 sqlite_stat1 }
ifcapable stat2   { lappend system_table_list 3 sqlite_stat2 }
ifcapable stat3   { lappend system_table_list 4 sqlite_stat3 }

foreach {tn tbl} $system_table_list {
  do_test alter-15.$tn.1 {
    catchsql "ALTER TABLE $tbl RENAME TO xyz"
  } [list 1 "table $tbl may not be altered"]

  do_test alter-15.$tn.2 {
︙
Changes to test/analyze3.test.
︙
13
14
15
16
17
18
19
20

21
22
23
24
25
26
27

13
14
15
16
17
18
19

20
21
22
23
24
25
26
27








-
+








# implements tests for range and LIKE constraints that use bound variables
# instead of literal constant arguments.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !stat2 {
ifcapable !stat3 {
  finish_test
  return
}

#----------------------------------------------------------------------
# Test Organization:
#
︙
96
97
98
99
100
101
102
103

104
105
106
107
108
109
110
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
187
188
189
190
191
192
193
194
195
196

197
198
199

200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216

217
218
219
220
221

222
223
224
225
226

227
228
229
230
231
232
233

96
97
98
99
100
101
102

103
104
105
106
107
108
109
110
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
187
188
189
190
191
192
193
194
195

196
197
198

199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215

216
217
218
219
220

221
222
223
224
225

226
227
228
229
230
231
232
233








-
+
















-
+




-
+




-
+


















-
+


-
+















-
+




-
+




-
+

















-
+


-
+
















-
+




-
+




-
+








} {}

do_eqp_test analyze3-1.1.2 {
  SELECT sum(y) FROM t1 WHERE x>200 AND x<300
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~179 rows)}}
do_eqp_test analyze3-1.1.3 {
  SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? and x<?) {~959 rows)}}
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~959 rows)}}

do_test analyze3-1.1.4 {
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
} {199 0 14850}
do_test analyze3-1.1.5 {
  set l [string range "200" 0 end]
  set u [string range "300" 0 end]
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
} {199 0 14850}
do_test analyze3-1.1.6 {
  set l [expr int(200)]
  set u [expr int(300)]
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
} {199 0 14850}
do_test analyze3-1.1.7 {
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 }
} {999 999 499500}
} {2000 0 499500}
do_test analyze3-1.1.8 {
  set l [string range "0" 0 end]
  set u [string range "1100" 0 end]
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
} {999 999 499500}
} {2000 0 499500}
do_test analyze3-1.1.9 {
  set l [expr int(0)]
  set u [expr int(1100)]
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>$l AND x<$u }
} {999 999 499500}
} {2000 0 499500}


# The following tests are similar to the block above. The difference is
# that the indexed column has TEXT affinity in this case. In the tests
# above the affinity is INTEGER.
#
do_test analyze3-1.2.1 {
  execsql {
    BEGIN;
      CREATE TABLE t2(x TEXT, y);
      INSERT INTO t2 SELECT * FROM t1;
      CREATE INDEX i2 ON t2(x);
    COMMIT;
    ANALYZE;
  }
} {}
do_eqp_test analyze3-1.2.2 {
  SELECT sum(y) FROM t2 WHERE x>1 AND x<2
} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~200 rows)}}
} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~196 rows)}}
do_eqp_test analyze3-1.2.3 {
  SELECT sum(y) FROM t2 WHERE x>0 AND x<99
} {0 0 0 {SCAN TABLE t2 (~111 rows)}}
} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~982 rows)}}
do_test analyze3-1.2.4 {
  sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
} {161 0 4760}
do_test analyze3-1.2.5 {
  set l [string range "12" 0 end]
  set u [string range "20" 0 end]
  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
} {161 0 text text 4760}
do_test analyze3-1.2.6 {
  set l [expr int(12)]
  set u [expr int(20)]
  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
} {161 0 integer integer 4760}
do_test analyze3-1.2.7 {
  sf_execsql { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 }
} {999 999 490555}
} {1981 0 490555}
do_test analyze3-1.2.8 {
  set l [string range "0" 0 end]
  set u [string range "99" 0 end]
  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
} {999 999 text text 490555}
} {1981 0 text text 490555}
do_test analyze3-1.2.9 {
  set l [expr int(0)]
  set u [expr int(99)]
  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}
} {999 999 integer integer 490555}
} {1981 0 integer integer 490555}

# Same tests a third time. This time, column x has INTEGER affinity and
# is not the leftmost column of the table. This triggered a bug causing
# SQLite to use sub-optimal query plans in 3.6.18 and earlier.
#
do_test analyze3-1.3.1 {
  execsql {
    BEGIN;
      CREATE TABLE t3(y TEXT, x INTEGER);
      INSERT INTO t3 SELECT y, x FROM t1;
      CREATE INDEX i3 ON t3(x);
    COMMIT;
    ANALYZE;
  }
} {}
do_eqp_test analyze3-1.3.2 {
  SELECT sum(y) FROM t3 WHERE x>200 AND x<300
} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~166 rows)}}
} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~156 rows)}}
do_eqp_test analyze3-1.3.3 {
  SELECT sum(y) FROM t3 WHERE x>0 AND x<1100
} {0 0 0 {SCAN TABLE t3 (~111 rows)}}
} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~989 rows)}}

do_test analyze3-1.3.4 {
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
} {199 0 14850}
do_test analyze3-1.3.5 {
  set l [string range "200" 0 end]
  set u [string range "300" 0 end]
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
} {199 0 14850}
do_test analyze3-1.3.6 {
  set l [expr int(200)]
  set u [expr int(300)]
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
} {199 0 14850}
do_test analyze3-1.3.7 {
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 }
} {999 999 499500}
} {2000 0 499500}
do_test analyze3-1.3.8 {
  set l [string range "0" 0 end]
  set u [string range "1100" 0 end]
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
} {999 999 499500}
} {2000 0 499500}
do_test analyze3-1.3.9 {
  set l [expr int(0)]
  set u [expr int(1100)]
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>$l AND x<$u }
} {999 999 499500}
} {2000 0 499500}

#-------------------------------------------------------------------------
# Test that the values of bound SQL variables may be used for the LIKE
# optimization.
#
drop_all_tables
do_test analyze3-2.1 {
︙
Changes to test/analyze6.test.
︙
13
14
15
16
17
18
19
20

21
22
23
24
25
26
27

13
14
15
16
17
18
19

20
21
22
23
24
25
26
27








-
+








# in this file a corner-case query planner optimization involving the
# join order of two tables of different sizes.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !stat2 {
ifcapable !stat3 {
  finish_test
  return
}

set testprefix analyze6

proc eqp {sql {db db}} {
︙
Changes to test/tkt-cbd054fa6b.test.
︙
12
13
14
15
16
17
18
19

20
21
22
23
24
25
26

12
13
14
15
16
17
18

19
20
21
22
23
24
25
26








-
+








# This file implements tests to verify that ticket [cbd054fa6b] has been
# fixed.  
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !stat2 {
ifcapable !stat3 {
  finish_test
  return
}

do_test tkt-cbd05-1.1 {
  db eval {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT UNIQUE NOT NULL);
︙
42
43
44
45
46
47
48
49

50
51
52
53
54
55
56

42
43
44
45
46
47
48

49
50
51
52
53
54
55
56








-
+








  db eval {
    ANALYZE;
  }
} {}
do_test tkt-cbd05-1.3 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    FROM sqlite_stat3 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {t1 t1_x { A B C D E F G H I}}

do_test tkt-cbd05-2.1 {
  db eval {
︙
74
75
76
77
78
79
80
81

82
83
84
85
86
87

74
75
76
77
78
79
80

81
82
83
84
85
86
87








-
+







  db eval {
    ANALYZE;
  }
} {}
do_test tkt-cbd05-2.3 {
  execsql { 
    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    FROM sqlite_stat3 
    WHERE idx = 't1_x' 
    GROUP BY tbl,idx
  }
} {t1 t1_x { A B C D E F G H I}}

finish_test