Debian Codegraph @

test/zeroblob.test

raw
1 # 2007 May 02
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 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing of the zero-filled blob functionality
13 # including the sqlite3_bind_zeroblob(), sqlite3_result_zeroblob(),
14 # and the built-in zeroblob() SQL function.
15 #
16 # $Id: zeroblob.test,v 1.14 2009/07/14 02:33:02 drh Exp $
17
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20 set testprefix zeroblob
21
22 # ifcapable !incrblob { finish_test return }
23
24 test_set_config_pagecache 0 0
25
26 # When zeroblob() is used for the last field of a column, then the
27 # content of the zeroblob is never instantiated on the VDBE stack.
28 # But it does get inserted into the database correctly.
29 #
30 db eval {PRAGMA cache_size=10}
31 sqlite3_memory_highwater 1
32 unset -nocomplain memused
33 set memused [sqlite3_memory_used]
34 do_test zeroblob-1.1 {
35 execsql {
36 CREATE TABLE t1(a,b,c,d);
37 }
38 set ::sqlite3_max_blobsize 0
39 execsql {
40 INSERT INTO t1 VALUES(2,3,4,zeroblob(1000000));
41 }
42 } {}
43
44 ifcapable incrblob {
45 do_test zeroblob-1.1.1 {
46 set ::sqlite3_max_blobsize
47 } {10}
48 do_test zeroblob-1.1.2 {
49 expr {[sqlite3_memory_highwater]<$::memused+35000}
50 } {1}
51 }
52
53 do_test zeroblob-1.2 {
54 execsql {
55 SELECT length(d) FROM t1
56 }
57 } {1000000}
58
59 # If a non-NULL column follows the zeroblob, then the content of
60 # the zeroblob must be instantiated.
61 #
62 do_test zeroblob-1.3 {
63 set ::sqlite3_max_blobsize 0
64 execsql {
65 INSERT INTO t1 VALUES(3,4,zeroblob(10000),5);
66 }
67 set ::sqlite3_max_blobsize
68 } {10010}
69 do_test zeroblob-1.4 {
70 execsql {
71 SELECT length(c), length(d) FROM t1
72 }
73 } {1 1000000 10000 1}
74
75 # Multiple zeroblobs can appear at the end of record. No instantiation
76 # of the blob content occurs on the stack.
77 #
78 do_test zeroblob-1.5 {
79 set ::sqlite3_max_blobsize 0
80 execsql {
81 INSERT INTO t1 VALUES(4,5,zeroblob(10000),zeroblob(10000));
82 }
83 } {}
84 ifcapable incrblob {
85 do_test zeroblob-1.5.1 {
86 set ::sqlite3_max_blobsize
87 } {11}
88 }
89 do_test zeroblob-1.6 {
90 execsql {
91 SELECT length(c), length(d) FROM t1
92 }
93 } {1 1000000 10000 1 10000 10000}
94
95 # NULLs can follow the zeroblob() or be intermixed with zeroblobs and
96 # no instantiation of the zeroblobs occurs on the stack.
97 #
98 do_test zeroblob-1.7 {
99 set ::sqlite3_max_blobsize 0
100 execsql {
101 INSERT INTO t1 VALUES(5,zeroblob(10000),NULL,zeroblob(10000));
102 }
103 } {}
104 ifcapable incrblob {
105 do_test zeroblob-1.7.1 {
106 set ::sqlite3_max_blobsize
107 } {10}
108 }
109 do_test zeroblob-1.8 {
110 execsql {
111 SELECT length(b), length(d) FROM t1 WHERE a=5
112 }
113 } {10000 10000}
114
115 # Comparisons against zeroblobs work.
116 #
117 do_test zeroblob-2.1 {
118 execsql {
119 SELECT a FROM t1 WHERE b=zeroblob(10000)
120 }
121 } {5}
122
123 # Comparisons against zeroblobs work even when indexed.
124 #
125 do_test zeroblob-2.2 {
126 execsql {
127 CREATE INDEX i1_1 ON t1(b);
128 SELECT a FROM t1 WHERE b=zeroblob(10000);
129 }
130 } {5}
131
132 # DISTINCT works for zeroblobs
133 #
134 ifcapable bloblit&&subquery&&compound {
135 do_test zeroblob-3.1 {
136 execsql {
137 SELECT count(DISTINCT a) FROM (
138 SELECT x'00000000000000000000' AS a
139 UNION ALL
140 SELECT zeroblob(10) AS a
141 )
142 }
143 } {1}
144 }
145
146 # Concatentation works with zeroblob
147 #
148 ifcapable bloblit {
149 do_test zeroblob-4.1 {
150 execsql {
151 SELECT hex(zeroblob(2) || x'61')
152 }
153 } {000061}
154 }
155
156 # Check various CAST(...) operations on zeroblob.
157 #
158 do_test zeroblob-5.1 {
159 execsql {
160 SELECT CAST (zeroblob(100) AS REAL);
161 }
162 } {0.0}
163 do_test zeroblob-5.2 {
164 execsql {
165 SELECT CAST (zeroblob(100) AS INTEGER);
166 }
167 } {0}
168 do_test zeroblob-5.3 {
169 execsql {
170 SELECT CAST (zeroblob(100) AS TEXT);
171 }
172 } {{}}
173 do_test zeroblob-5.4 {
174 execsql {
175 SELECT CAST(zeroblob(100) AS BLOB);
176 }
177 } [execsql {SELECT zeroblob(100)}]
178
179
180 # Check for malicious use of zeroblob. Make sure nothing crashes.
181 #
182 do_test zeroblob-6.1.1 {
183 execsql {select zeroblob(-1)}
184 } {{}}
185 do_test zeroblob-6.1.2 {
186 execsql {select zeroblob(-10)}
187 } {{}}
188 do_test zeroblob-6.1.3 {
189 execsql {select zeroblob(-100)}
190 } {{}}
191 do_test zeroblob-6.2 {
192 execsql {select length(zeroblob(-1))}
193 } {0}
194 do_test zeroblob-6.3 {
195 execsql {select zeroblob(-1)|1}
196 } {1}
197 do_test zeroblob-6.4 {
198 catchsql {select length(zeroblob(2147483648))}
199 } {1 {string or blob too big}}
200 do_test zeroblob-6.5 {
201 catchsql {select zeroblob(2147483648)}
202 } {1 {string or blob too big}}
203 do_test zeroblob-6.6 {
204 execsql {select hex(zeroblob(-1))}
205 } {{}}
206 do_test zeroblob-6.7 {
207 execsql {select typeof(zeroblob(-1))}
208 } {blob}
209
210 # Test bind_zeroblob()
211 #
212 sqlite3_memory_highwater 1
213 unset -nocomplain memused
214 set memused [sqlite3_memory_used]
215 do_test zeroblob-7.1 {
216 set ::STMT [sqlite3_prepare $::DB "SELECT length(?)" -1 DUMMY]
217 set ::sqlite3_max_blobsize 0
218 sqlite3_bind_zeroblob $::STMT 1 450000
219 sqlite3_step $::STMT
220 } {SQLITE_ROW}
221 do_test zeroblob-7.2 {
222 sqlite3_column_int $::STMT 0
223 } {450000}
224 do_test zeroblob-7.3 {
225 sqlite3_finalize $::STMT
226 } {SQLITE_OK}
227 ifcapable incrblob {
228 do_test zeroblob-7.4 {
229 set ::sqlite3_max_blobsize
230 } {0}
231 do_test zeroblob-7.5 {
232 expr {[sqlite3_memory_highwater]<$::memused+10000}
233 } {1}
234 }
235
236 # Test that MakeRecord can handle a value with some real content
237 # and a zero-blob tail.
238 #
239 do_test zeroblob-8.1 {
240 llength [execsql {
241 SELECT 'hello' AS a, zeroblob(10) as b from t1 ORDER BY a, b;
242 }]
243 } {8}
244
245
246 # Ticket #3965
247 # zeroblobs on either size of an IN operator
248 #
249 do_test zeroblob-9.1 {
250 db eval {SELECT x'0000' IN (x'000000')}
251 } {0}
252 do_test zeroblob-9.2 {
253 db eval {SELECT x'0000' IN (x'0000')}
254 } {1}
255 do_test zeroblob-9.3 {
256 db eval {SELECT zeroblob(2) IN (x'000000')}
257 } {0}
258 do_test zeroblob-9.4 {
259 db eval {SELECT zeroblob(2) IN (x'0000')}
260 } {1}
261 do_test zeroblob-9.5 {
262 db eval {SELECT x'0000' IN (zeroblob(3))}
263 } {0}
264 do_test zeroblob-9.6 {
265 db eval {SELECT x'0000' IN (zeroblob(2))}
266 } {1}
267 do_test zeroblob-9.7 {
268 db eval {SELECT zeroblob(2) IN (zeroblob(3))}
269 } {0}
270 do_test zeroblob-9.8 {
271 db eval {SELECT zeroblob(2) IN (zeroblob(2))}
272 } {1}
273
274 # Oversized zeroblob records
275 #
276 do_test zeroblob-10.1 {
277 db eval {
278 CREATE TABLE t10(a,b,c);
279 }
280 catchsql {INSERT INTO t10 VALUES(zeroblob(1e9),zeroblob(1e9),zeroblob(1e9))}
281 } {1 {string or blob too big}}
282
283 #-------------------------------------------------------------------------
284 # Test the zeroblob() function on its own with negative or oversized
285 # arguments.
286 #
287 do_execsql_test 11.0 {
288 SELECT length(zeroblob(-1444444444444444));
289 } {0}
290 do_catchsql_test 11.1 {
291 SELECT zeroblob(5000 * 1024 * 1024);
292 } {1 {string or blob too big}}
293 do_catchsql_test 11.2 {
294 SELECT quote(zeroblob(5000 * 1024 * 1024));
295 } {1 {string or blob too big}}
296 do_catchsql_test 11.3 {
297 SELECT quote(zeroblob(-1444444444444444));
298 } {0 X''}
299 do_catchsql_test 11.4 {
300 SELECT quote(test_zeroblob(-1));
301 } {0 X''}
302
303 #-------------------------------------------------------------------------
304 # Test the sqlite3_bind_zeroblob64() API.
305 #
306 proc bind_and_run {stmt nZero} {
307 sqlite3_bind_zeroblob64 $stmt 1 $nZero
308 sqlite3_step $stmt
309 set ret [sqlite3_column_int $stmt 0]
310 sqlite3_reset $stmt
311 set ret
312 }
313 set stmt [sqlite3_prepare db "SELECT length(?)" -1 dummy]
314
315 do_test 12.1 { bind_and_run $stmt 40 } 40
316 do_test 12.2 { bind_and_run $stmt 0 } 0
317 do_test 12.3 { bind_and_run $stmt 1000 } 1000
318
319 do_test 12.4 {
320 list [catch { bind_and_run $stmt [expr 5000 * 1024 * 1024] } msg] $msg
321 } {1 SQLITE_TOOBIG}
322 do_test 12.5 {
323 sqlite3_step $stmt
324 set ret [sqlite3_column_int $stmt 0]
325 sqlite3_reset $stmt
326 set ret
327 } {1000}
328
329 sqlite3_finalize $stmt
330
331 # 2019-01-25 https://sqlite.org/src/tktview/bb4bdb9f7f654b0bb9f34cfbac
332 # Zeroblob truncated by an index on expression
333 #
334 do_execsql_test 13.100 {
335 DROP TABLE IF EXISTS t1;
336 CREATE TABLE t1(a,b,c);
337 CREATE INDEX t1bbc ON t1(b, b+c);
338 INSERT INTO t1(a,b,c) VALUES(1,zeroblob(8),3);
339 SELECT a, quote(b), length(b), c FROM t1;
340 } {1 X'0000000000000000' 8 3}
341
342 test_restore_config_pagecache
343 finish_test