1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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
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
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
|
# 2007 May 10
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is generating semi-random strings of SQL
# (a.k.a. "fuzz") and sending it into the parser to try to
# generate errors.
#
# The tests in this file are really about testing fuzzily generated
# SQL parse-trees. The majority of the fuzzily generated SQL is
# valid as far as the parser is concerned.
#
# The most complicated trees are for SELECT statements.
#
# $Id: fuzz.test,v 1.14 2007/05/30 10:36:47 danielk1977 Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::REPEATS 5000
# If running quick.test, don't do so many iterations.
if {[info exists ::ISQUICK]} {
if {$::ISQUICK} { set ::REPEATS 20 }
}
source $testdir/fuzz_common.tcl
#----------------------------------------------------------------
# These tests caused errors that were first caught by the tests
# in this file. They are still here.
do_test fuzz-1.1 {
execsql {
SELECT 'abc' LIKE X'ABCD';
}
} {0}
do_test fuzz-1.2 {
execsql {
SELECT 'abc' LIKE zeroblob(10);
}
} {0}
do_test fuzz-1.3 {
execsql {
SELECT zeroblob(10) LIKE 'abc';
}
} {0}
do_test fuzz-1.4 {
execsql {
SELECT (- -21) % NOT (456 LIKE zeroblob(10));
}
} {0}
do_test fuzz-1.5 {
execsql {
SELECT (SELECT (
SELECT (SELECT -2147483648) FROM (SELECT 1) ORDER BY 1
))
}
} {-2147483648}
do_test fuzz-1.6 {
execsql {
SELECT 'abc', zeroblob(1) FROM (SELECT 1) ORDER BY 1
}
} [execsql {SELECT 'abc', zeroblob(1)}]
do_test fuzz-1.7 {
execsql {
SELECT ( SELECT zeroblob(1000) FROM (
SELECT * FROM (SELECT 'first') ORDER BY NOT 'in')
)
}
} [execsql {SELECT zeroblob(1000)}]
do_test fuzz-1.8 {
# Problems with opcode OP_ToText (did not account for MEM_Zero).
# Also MemExpandBlob() was marking expanded blobs as nul-terminated.
# They are not.
execsql {
SELECT CAST(zeroblob(1000) AS text);
}
} {{}}
do_test fuzz-1.9 {
# This was causing a NULL pointer dereference of Expr.pList.
execsql {
SELECT 1 FROM (SELECT * FROM sqlite_master WHERE random())
}
} {}
do_test fuzz-1.10 {
# Bug in calculation of Parse.ckOffset causing an assert()
# to fail. Probably harmless.
execsql {
SELECT coalesce(1, substr( 1, 2, length('in' IN (SELECT 1))))
}
} {1}
do_test fuzz-1.11 {
# The literals (A, B, C, D) are not important, they are just used
# to make the EXPLAIN output easier to read.
#
# The problem here is that the EXISTS(...) expression leaves an
# extra value on the VDBE stack. This is confusing the parent and
# leads to an assert() failure when OP_Insert encounters an integer
# when it expects a record blob.
#
# Update: Any query with (LIMIT 0) was leaking stack.
#
execsql {
SELECT 'A' FROM (SELECT 'B') ORDER BY EXISTS (
SELECT 'C' FROM (SELECT 'D' LIMIT 0)
)
}
} {A}
do_test fuzz-1.12.1 {
# Create a table with a single row.
execsql {
CREATE TABLE abc(b);
INSERT INTO abc VALUES('ABCDE');
}
# The following query was crashing. The later subquery (in the FROM)
# clause was flattened into the parent, but the code was not repairng
# the "b" reference in the other sub-query. When the query was executed,
# that "b" refered to a non-existant vdbe table-cursor.
#
execsql {
SELECT 1 IN ( SELECT b UNION SELECT 1 ) FROM (SELECT b FROM abc);
}
} {1}
do_test fuzz-1.12.2 {
# Clean up after the previous query.
execsql {
DROP TABLE abc;
}
} {}
do_test fuzz-1.13 {
# The problem here was that when there were more expressions in
# the ORDER BY list than the result-set list. The temporary b-tree
# used for sorting was being misconfigured in this case.
#
execsql {
SELECT 'abcd' UNION SELECT 'efgh' ORDER BY 1 ASC, 1 ASC;
}
} {abcd efgh}
do_test fuzz-1.14.1 {
execsql {
CREATE TABLE abc(a, b, c);
INSERT INTO abc VALUES(123, 456, 789);
}
# The [a] reference in the sub-select was causing a problem. Because
# the internal walkSelectExpr() function was not considering compound
# SELECT operators.
execsql {
SELECT 1 FROM abc
GROUP BY c HAVING EXISTS (SELECT a UNION SELECT 123);
}
} {1}
do_test fuzz-1.14.2 {
execsql {
DROP TABLE abc;
}
} {}
#----------------------------------------------------------------
# Test some fuzzily generated expressions.
#
do_fuzzy_test fuzz-2 -template { SELECT [Expr] }
do_test fuzz-3.1 {
execsql {
CREATE TABLE abc(a, b, c);
CREATE TABLE def(a, b, c);
CREATE TABLE ghi(a, b, c);
}
} {}
set ::TableList [list abc def ghi]
#----------------------------------------------------------------
# Test some fuzzily generated SELECT statements.
#
do_fuzzy_test fuzz-3.2 -template {[Select]}
#----------------------------------------------------------------
# Insert a small amount of data into the database and then run
# some more generated SELECT statements.
#
do_test fuzz-4.1 {
execsql {
INSERT INTO abc VALUES(1, 2, 3);
INSERT INTO abc VALUES(4, 5, 6);
INSERT INTO abc VALUES(7, 8, 9);
INSERT INTO def VALUES(1, 2, 3);
INSERT INTO def VALUES(4, 5, 6);
INSERT INTO def VALUES(7, 8, 9);
INSERT INTO ghi VALUES(1, 2, 3);
INSERT INTO ghi VALUES(4, 5, 6);
INSERT INTO ghi VALUES(7, 8, 9);
CREATE INDEX abc_i ON abc(a, b, c);
CREATE INDEX def_i ON def(c, a, b);
CREATE INDEX ghi_i ON ghi(b, c, a);
}
} {}
do_fuzzy_test fuzz-4.2 -template {[Select]}
#----------------------------------------------------------------
# Test some fuzzy INSERT statements:
#
do_test fuzz-5.1 {execsql BEGIN} {}
do_fuzzy_test fuzz-5.2 -template {[Insert]} -errorlist table
integrity_check fuzz-5.2.integrity
do_test fuzz-5.3 {execsql COMMIT} {}
integrity_check fuzz-5.4.integrity
#----------------------------------------------------------------
# Now that there is data in the database, run some more SELECT
# statements
#
set ::ColumnList [list a b c]
set E {{no such col} {ambiguous column name}}
do_fuzzy_test fuzz-6.1 -template {[Select]} -errorlist $E
#----------------------------------------------------------------
# Run some SELECTs, INSERTs, UPDATEs and DELETEs in a transaction.
#
set E {{no such col} {ambiguous column name} {table}}
do_test fuzz-7.1 {execsql BEGIN} {}
do_fuzzy_test fuzz-7.2 -template {[Statement]} -errorlist $E
integrity_check fuzz-7.3.integrity
do_test fuzz-7.4 {execsql COMMIT} {}
integrity_check fuzz-7.5.integrity
#----------------------------------------------------------------
# Many CREATE and DROP TABLE statements:
#
set E [list table duplicate {no such col} {ambiguous column name} {use DROP}]
do_fuzzy_test fuzz-8.1 -template {[CreateOrDropTableOrView]} -errorlist $E
close $::log
finish_test
|