Annotation of embedaddon/sqlite3/test/sqllimits1.test, revision 1.1.1.1
1.1 misho 1: # 2007 May 8
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: #
12: # This file contains tests to verify that the limits defined in
13: # sqlite source file limits.h are enforced.
14: #
15: # $Id: sqllimits1.test,v 1.33 2009/06/25 01:47:12 drh Exp $
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: # Verify that the default per-connection limits are the same as
21: # the compile-time hard limits.
22: #
23: sqlite3 db2 :memory:
24: do_test sqllimits1-1.1 {
25: sqlite3_limit db SQLITE_LIMIT_LENGTH -1
26: } $SQLITE_MAX_LENGTH
27: do_test sqllimits1-1.2 {
28: sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1
29: } $SQLITE_MAX_SQL_LENGTH
30: do_test sqllimits1-1.3 {
31: sqlite3_limit db SQLITE_LIMIT_COLUMN -1
32: } $SQLITE_MAX_COLUMN
33: do_test sqllimits1-1.4 {
34: sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1
35: } $SQLITE_MAX_EXPR_DEPTH
36: do_test sqllimits1-1.5 {
37: sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1
38: } $SQLITE_MAX_COMPOUND_SELECT
39: do_test sqllimits1-1.6 {
40: sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1
41: } $SQLITE_MAX_VDBE_OP
42: do_test sqllimits1-1.7 {
43: sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1
44: } $SQLITE_MAX_FUNCTION_ARG
45: do_test sqllimits1-1.8 {
46: sqlite3_limit db SQLITE_LIMIT_ATTACHED -1
47: } $SQLITE_MAX_ATTACHED
48: do_test sqllimits1-1.9 {
49: sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
50: } $SQLITE_MAX_LIKE_PATTERN_LENGTH
51: do_test sqllimits1-1.10 {
52: sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1
53: } $SQLITE_MAX_VARIABLE_NUMBER
54:
55: # Limit parameters out of range.
56: #
57: do_test sqllimits1-1.20 {
58: sqlite3_limit db SQLITE_LIMIT_TOOSMALL 123
59: } {-1}
60: do_test sqllimits1-1.21 {
61: sqlite3_limit db SQLITE_LIMIT_TOOSMALL 123
62: } {-1}
63: do_test sqllimits1-1.22 {
64: sqlite3_limit db SQLITE_LIMIT_TOOBIG 123
65: } {-1}
66: do_test sqllimits1-1.23 {
67: sqlite3_limit db SQLITE_LIMIT_TOOBIG 123
68: } {-1}
69:
70:
71: # Decrease all limits by half. Verify that the new limits take.
72: #
73: if {$SQLITE_MAX_LENGTH>=2} {
74: do_test sqllimits1-2.1.1 {
75: sqlite3_limit db SQLITE_LIMIT_LENGTH \
76: [expr {$::SQLITE_MAX_LENGTH/2}]
77: } $SQLITE_MAX_LENGTH
78: do_test sqllimits1-2.1.2 {
79: sqlite3_limit db SQLITE_LIMIT_LENGTH -1
80: } [expr {$SQLITE_MAX_LENGTH/2}]
81: }
82: if {$SQLITE_MAX_SQL_LENGTH>=2} {
83: do_test sqllimits1-2.2.1 {
84: sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH \
85: [expr {$::SQLITE_MAX_SQL_LENGTH/2}]
86: } $SQLITE_MAX_SQL_LENGTH
87: do_test sqllimits1-2.2.2 {
88: sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1
89: } [expr {$SQLITE_MAX_SQL_LENGTH/2}]
90: }
91: if {$SQLITE_MAX_COLUMN>=2} {
92: do_test sqllimits1-2.3.1 {
93: sqlite3_limit db SQLITE_LIMIT_COLUMN \
94: [expr {$::SQLITE_MAX_COLUMN/2}]
95: } $SQLITE_MAX_COLUMN
96: do_test sqllimits1-2.3.2 {
97: sqlite3_limit db SQLITE_LIMIT_COLUMN -1
98: } [expr {$SQLITE_MAX_COLUMN/2}]
99: }
100: if {$SQLITE_MAX_EXPR_DEPTH>=2} {
101: do_test sqllimits1-2.4.1 {
102: sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH \
103: [expr {$::SQLITE_MAX_EXPR_DEPTH/2}]
104: } $SQLITE_MAX_EXPR_DEPTH
105: do_test sqllimits1-2.4.2 {
106: sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1
107: } [expr {$SQLITE_MAX_EXPR_DEPTH/2}]
108: }
109: if {$SQLITE_MAX_COMPOUND_SELECT>=2} {
110: do_test sqllimits1-2.5.1 {
111: sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT \
112: [expr {$::SQLITE_MAX_COMPOUND_SELECT/2}]
113: } $SQLITE_MAX_COMPOUND_SELECT
114: do_test sqllimits1-2.5.2 {
115: sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1
116: } [expr {$SQLITE_MAX_COMPOUND_SELECT/2}]
117: }
118: if {$SQLITE_MAX_VDBE_OP>=2} {
119: do_test sqllimits1-2.6.1 {
120: sqlite3_limit db SQLITE_LIMIT_VDBE_OP \
121: [expr {$::SQLITE_MAX_VDBE_OP/2}]
122: } $SQLITE_MAX_VDBE_OP
123: do_test sqllimits1-2.6.2 {
124: sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1
125: } [expr {$SQLITE_MAX_VDBE_OP/2}]
126: }
127: if {$SQLITE_MAX_FUNCTION_ARG>=2} {
128: do_test sqllimits1-2.7.1 {
129: sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG \
130: [expr {$::SQLITE_MAX_FUNCTION_ARG/2}]
131: } $SQLITE_MAX_FUNCTION_ARG
132: do_test sqllimits1-2.7.2 {
133: sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1
134: } [expr {$SQLITE_MAX_FUNCTION_ARG/2}]
135: }
136: if {$SQLITE_MAX_ATTACHED>=2} {
137: do_test sqllimits1-2.8.1 {
138: sqlite3_limit db SQLITE_LIMIT_ATTACHED \
139: [expr {$::SQLITE_MAX_ATTACHED/2}]
140: } $SQLITE_MAX_ATTACHED
141: do_test sqllimits1-2.8.2 {
142: sqlite3_limit db SQLITE_LIMIT_ATTACHED -1
143: } [expr {$SQLITE_MAX_ATTACHED/2}]
144: }
145: if {$SQLITE_MAX_LIKE_PATTERN_LENGTH>=2} {
146: do_test sqllimits1-2.9.1 {
147: sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH \
148: [expr {$::SQLITE_MAX_LIKE_PATTERN_LENGTH/2}]
149: } $SQLITE_MAX_LIKE_PATTERN_LENGTH
150: do_test sqllimits1-2.9.2 {
151: sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
152: } [expr {$SQLITE_MAX_LIKE_PATTERN_LENGTH/2}]
153: }
154: if {$SQLITE_MAX_VARIABLE_NUMBER>=2} {
155: do_test sqllimits1-2.10.1 {
156: sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER \
157: [expr {$::SQLITE_MAX_VARIABLE_NUMBER/2}]
158: } $SQLITE_MAX_VARIABLE_NUMBER
159: do_test sqllimits1-2.10.2 {
160: sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1
161: } [expr {$SQLITE_MAX_VARIABLE_NUMBER/2}]
162: }
163:
164: # In a separate database connection, verify that the limits are unchanged.
165: #
166: do_test sqllimits1-3.1 {
167: sqlite3_limit db2 SQLITE_LIMIT_LENGTH -1
168: } $SQLITE_MAX_LENGTH
169: do_test sqllimits1-3.2 {
170: sqlite3_limit db2 SQLITE_LIMIT_SQL_LENGTH -1
171: } $SQLITE_MAX_SQL_LENGTH
172: do_test sqllimits1-3.3 {
173: sqlite3_limit db2 SQLITE_LIMIT_COLUMN -1
174: } $SQLITE_MAX_COLUMN
175: do_test sqllimits1-3.4 {
176: sqlite3_limit db2 SQLITE_LIMIT_EXPR_DEPTH -1
177: } $SQLITE_MAX_EXPR_DEPTH
178: do_test sqllimits1-3.5 {
179: sqlite3_limit db2 SQLITE_LIMIT_COMPOUND_SELECT -1
180: } $SQLITE_MAX_COMPOUND_SELECT
181: do_test sqllimits1-3.6 {
182: sqlite3_limit db2 SQLITE_LIMIT_VDBE_OP -1
183: } $SQLITE_MAX_VDBE_OP
184: do_test sqllimits1-3.7 {
185: sqlite3_limit db2 SQLITE_LIMIT_FUNCTION_ARG -1
186: } $SQLITE_MAX_FUNCTION_ARG
187: do_test sqllimits1-3.8 {
188: sqlite3_limit db2 SQLITE_LIMIT_ATTACHED -1
189: } $SQLITE_MAX_ATTACHED
190: do_test sqllimits1-3.9 {
191: sqlite3_limit db2 SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
192: } $SQLITE_MAX_LIKE_PATTERN_LENGTH
193: do_test sqllimits1-3.10 {
194: sqlite3_limit db2 SQLITE_LIMIT_VARIABLE_NUMBER -1
195: } $SQLITE_MAX_VARIABLE_NUMBER
196: db2 close
197:
198: # Attempt to set all limits to the maximum 32-bit integer. Verify
199: # that the limit does not exceed the compile-time upper bound.
200: #
201: do_test sqllimits1-4.1.1 {
202: sqlite3_limit db SQLITE_LIMIT_LENGTH 0x7fffffff
203: sqlite3_limit db SQLITE_LIMIT_LENGTH -1
204: } $SQLITE_MAX_LENGTH
205: do_test sqllimits1-4.2.1 {
206: sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 0x7fffffff
207: sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH -1
208: } $SQLITE_MAX_SQL_LENGTH
209: do_test sqllimits1-4.3.1 {
210: sqlite3_limit db SQLITE_LIMIT_COLUMN 0x7fffffff
211: sqlite3_limit db SQLITE_LIMIT_COLUMN -1
212: } $SQLITE_MAX_COLUMN
213: do_test sqllimits1-4.4.1 {
214: sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH 0x7fffffff
215: sqlite3_limit db SQLITE_LIMIT_EXPR_DEPTH -1
216: } $SQLITE_MAX_EXPR_DEPTH
217: do_test sqllimits1-4.5.1 {
218: sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT 0x7fffffff
219: sqlite3_limit db SQLITE_LIMIT_COMPOUND_SELECT -1
220: } $SQLITE_MAX_COMPOUND_SELECT
221: do_test sqllimits1-4.6.1 {
222: sqlite3_limit db SQLITE_LIMIT_VDBE_OP 0x7fffffff
223: sqlite3_limit db SQLITE_LIMIT_VDBE_OP -1
224: } $SQLITE_MAX_VDBE_OP
225: do_test sqllimits1-4.7.1 {
226: sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG 0x7fffffff
227: sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG -1
228: } $SQLITE_MAX_FUNCTION_ARG
229: do_test sqllimits1-4.8.1 {
230: sqlite3_limit db SQLITE_LIMIT_ATTACHED 0x7fffffff
231: sqlite3_limit db SQLITE_LIMIT_ATTACHED -1
232: } $SQLITE_MAX_ATTACHED
233: do_test sqllimits1-4.9.1 {
234: sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH 0x7fffffff
235: sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH -1
236: } $SQLITE_MAX_LIKE_PATTERN_LENGTH
237: do_test sqllimits1-4.10.1 {
238: sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER 0x7fffffff
239: sqlite3_limit db SQLITE_LIMIT_VARIABLE_NUMBER -1
240: } $SQLITE_MAX_VARIABLE_NUMBER
241:
242: #--------------------------------------------------------------------
243: # Test cases sqllimits1-5.* test that the SQLITE_MAX_LENGTH limit
244: # is enforced.
245: #
246: db close
247: sqlite3 db test.db
248: set LARGESIZE 99999
249: set SQLITE_LIMIT_LENGTH 100000
250: sqlite3_limit db SQLITE_LIMIT_LENGTH $SQLITE_LIMIT_LENGTH
251:
252: do_test sqllimits1-5.1.1 {
253: catchsql { SELECT randomblob(2147483647) }
254: } {1 {string or blob too big}}
255: do_test sqllimits1-5.1.2 {
256: catchsql { SELECT zeroblob(2147483647) }
257: } {1 {string or blob too big}}
258:
259: do_test sqllimits1-5.2 {
260: catchsql { SELECT LENGTH(randomblob($::LARGESIZE)) }
261: } [list 0 $LARGESIZE]
262:
263: do_test sqllimits1-5.3 {
264: catchsql { SELECT quote(randomblob($::LARGESIZE)) }
265: } {1 {string or blob too big}}
266:
267: do_test sqllimits1-5.4 {
268: catchsql { SELECT LENGTH(zeroblob($::LARGESIZE)) }
269: } [list 0 $LARGESIZE]
270:
271: do_test sqllimits1-5.5 {
272: catchsql { SELECT quote(zeroblob($::LARGESIZE)) }
273: } {1 {string or blob too big}}
274:
275: do_test sqllimits1-5.6 {
276: catchsql { SELECT zeroblob(-1) }
277: } {0 {{}}}
278:
279: do_test sqllimits1-5.9 {
280: set ::str [string repeat A 65537]
281: set ::rep [string repeat B 65537]
282: catchsql { SELECT replace($::str, 'A', $::rep) }
283: } {1 {string or blob too big}}
284:
285: do_test sqllimits1-5.10 {
286: set ::str [string repeat %J 2100]
287: catchsql { SELECT strftime($::str, '2003-10-31') }
288: } {1 {string or blob too big}}
289:
290: do_test sqllimits1-5.11 {
291: set ::str1 [string repeat A [expr {$SQLITE_LIMIT_LENGTH - 10}]]
292: set ::str2 [string repeat B [expr {$SQLITE_LIMIT_LENGTH - 10}]]
293: catchsql { SELECT $::str1 || $::str2 }
294: } {1 {string or blob too big}}
295:
296: do_test sqllimits1-5.12 {
297: set ::str1 [string repeat ' [expr {$SQLITE_LIMIT_LENGTH - 10}]]
298: catchsql { SELECT quote($::str1) }
299: } {1 {string or blob too big}}
300:
301: do_test sqllimits1-5.13 {
302: set ::str1 [string repeat ' [expr {$SQLITE_LIMIT_LENGTH - 10}]]
303: catchsql { SELECT hex($::str1) }
304: } {1 {string or blob too big}}
305:
306: do_test sqllimits1-5.14.1 {
307: set ::STMT [sqlite3_prepare db "SELECT ?" -1 TAIL]
308: sqlite3_bind_zeroblob $::STMT 1 [expr {$SQLITE_LIMIT_LENGTH + 1}]
309: } {}
310: do_test sqllimits1-5.14.2 {
311: sqlite3_step $::STMT
312: } {SQLITE_ERROR}
313: do_test sqllimits1-5.14.3 {
314: sqlite3_reset $::STMT
315: } {SQLITE_TOOBIG}
316: do_test sqllimits1-5.14.4 {
317: set np1 [expr {$SQLITE_LIMIT_LENGTH + 1}]
318: set ::str1 [string repeat A $np1]
319: catch {sqlite3_bind_text $::STMT 1 $::str1 -1} res
320: set res
321: } {SQLITE_TOOBIG}
322: ifcapable utf16 {
323: do_test sqllimits1-5.14.5 {
324: catch {sqlite3_bind_text16 $::STMT 1 $::str1 -1} res
325: set res
326: } {SQLITE_TOOBIG}
327: }
328: do_test sqllimits1-5.14.6 {
329: catch {sqlite3_bind_text $::STMT 1 $::str1 $np1} res
330: set res
331: } {SQLITE_TOOBIG}
332: ifcapable utf16 {
333: do_test sqllimits1-5.14.7 {
334: catch {sqlite3_bind_text16 $::STMT 1 $::str1 $np1} res
335: set res
336: } {SQLITE_TOOBIG}
337: }
338: do_test sqllimits1-5.14.8 {
339: set n [expr {$np1-1}]
340: catch {sqlite3_bind_text $::STMT 1 $::str1 $n} res
341: set res
342: } {}
343: do_test sqllimits1-5.14.9 {
344: catch {sqlite3_bind_text16 $::STMT 1 $::str1 $n} res
345: set res
346: } {}
347: sqlite3_finalize $::STMT
348:
349: do_test sqllimits1-5.15 {
350: execsql {
351: CREATE TABLE t4(x);
352: INSERT INTO t4 VALUES(1);
353: INSERT INTO t4 VALUES(2);
354: INSERT INTO t4 SELECT 2+x FROM t4;
355: }
356: catchsql {
357: SELECT group_concat(hex(randomblob(20000))) FROM t4;
358: }
359: } {1 {string or blob too big}}
360: db eval {DROP TABLE t4}
361:
362: sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 0x7fffffff
363: set strvalue [string repeat A $::SQLITE_LIMIT_LENGTH]
364: do_test sqllimits1-5.16 {
365: catchsql "SELECT '$strvalue'"
366: } [list 0 $strvalue]
367: do_test sqllimits1-5.17.1 {
368: catchsql "SELECT 'A$strvalue'"
369: } [list 1 {string or blob too big}]
370: do_test sqllimits1-5.17.2 {
371: sqlite3_limit db SQLITE_LIMIT_LENGTH 0x7fffffff
372: catchsql {SELECT 'A' || $::strvalue}
373: } [list 0 A$strvalue]
374: do_test sqllimits1-5.17.3 {
375: sqlite3_limit db SQLITE_LIMIT_LENGTH $SQLITE_LIMIT_LENGTH
376: catchsql {SELECT 'A' || $::strvalue}
377: } [list 1 {string or blob too big}]
378: set blobvalue [string repeat 41 $::SQLITE_LIMIT_LENGTH]
379: do_test sqllimits1-5.18 {
380: catchsql "SELECT x'$blobvalue'"
381: } [list 0 $strvalue]
382: do_test sqllimits1-5.19 {
383: catchsql "SELECT '41$blobvalue'"
384: } [list 1 {string or blob too big}]
385: unset blobvalue
386:
387: ifcapable datetime {
388: set strvalue [string repeat D [expr {$SQLITE_LIMIT_LENGTH-12}]]
389: do_test sqllimits1-5.20 {
390: catchsql {SELECT strftime('%Y ' || $::strvalue, '2008-01-02')}
391: } [list 0 [list "2008 $strvalue"]]
392: do_test sqllimits1-5.21 {
393: catchsql {SELECT strftime('%Y-%m-%d ' || $::strvalue, '2008-01-02')}
394: } {1 {string or blob too big}}
395: }
396: unset strvalue
397:
398: #--------------------------------------------------------------------
399: # Test cases sqllimits1-6.* test that the SQLITE_MAX_SQL_LENGTH limit
400: # is enforced.
401: #
402: do_test sqllimits1-6.1 {
403: sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 50000
404: set sql "SELECT 1 WHERE 1==1"
405: set tail " /* A comment to take up space in order to make the string\
406: longer without increasing the expression depth */\
407: AND 1 == 1"
408: set N [expr {(50000 / [string length $tail])+1}]
409: append sql [string repeat $tail $N]
410: catchsql $sql
411: } {1 {string or blob too big}}
412: do_test sqllimits1-6.3 {
413: sqlite3_limit db SQLITE_LIMIT_SQL_LENGTH 50000
414: set sql "SELECT 1 WHERE 1==1"
415: set tail " /* A comment to take up space in order to make the string\
416: longer without increasing the expression depth */\
417: AND 1 == 1"
418: set N [expr {(50000 / [string length $tail])+1}]
419: append sql [string repeat $tail $N]
420: set nbytes [string length $sql]
421: append sql { AND 0}
422: set rc [catch {sqlite3_prepare db $sql $nbytes TAIL} STMT]
423: lappend rc $STMT
424: } {1 {(18) statement too long}}
425: do_test sqllimits1-6.4 {
426: sqlite3_errmsg db
427: } {statement too long}
428:
429: #--------------------------------------------------------------------
430: # Test cases sqllimits1-7.* test that the limit set using the
431: # max_page_count pragma.
432: #
433: do_test sqllimits1-7.1 {
434: execsql {
435: PRAGMA max_page_count = 1000;
436: }
437: } {1000}
438: do_test sqllimits1-7.2 {
439: execsql { CREATE TABLE trig (a INTEGER, b INTEGER); }
440:
441: # Set up a tree of triggers to fire when a row is inserted
442: # into table "trig".
443: #
444: # INSERT -> insert_b -> update_b -> insert_a -> update_a (chain 1)
445: # -> update_a -> insert_a -> update_b (chain 2)
446: # -> insert_a -> update_b -> insert_b -> update_a (chain 3)
447: # -> update_a -> insert_b -> update_b (chain 4)
448: #
449: # Table starts with N rows.
450: #
451: # Chain 1: insert_b (update N rows)
452: # -> update_b (insert 1 rows)
453: # -> insert_a (update N rows)
454: # -> update_a (insert 1 rows)
455: #
456: # chains 2, 3 and 4 are similar. Each inserts more than N^2 rows, where
457: # N is the number of rows at the conclusion of the previous chain.
458: #
459: # Therefore, a single insert adds (N^16 plus some) rows to the database.
460: # A really long loop...
461: #
462: execsql {
463: CREATE TRIGGER update_b BEFORE UPDATE ON trig
464: FOR EACH ROW BEGIN
465: INSERT INTO trig VALUES (65, 'update_b');
466: END;
467:
468: CREATE TRIGGER update_a AFTER UPDATE ON trig
469: FOR EACH ROW BEGIN
470: INSERT INTO trig VALUES (65, 'update_a');
471: END;
472:
473: CREATE TRIGGER insert_b BEFORE INSERT ON trig
474: FOR EACH ROW BEGIN
475: UPDATE trig SET a = 1;
476: END;
477:
478: CREATE TRIGGER insert_a AFTER INSERT ON trig
479: FOR EACH ROW BEGIN
480: UPDATE trig SET a = 1;
481: END;
482: }
483: } {}
484:
485: do_test sqllimits1-7.3 {
486: execsql {
487: INSERT INTO trig VALUES (1,1);
488: }
489: } {}
490:
491: do_test sqllimits1-7.4 {
492: execsql {
493: SELECT COUNT(*) FROM trig;
494: }
495: } {7}
496:
497: # This tries to insert so many rows it fills up the database (limited
498: # to 1MB, so not that noteworthy an achievement).
499: #
500: do_test sqllimits1-7.5 {
501: catchsql {
502: INSERT INTO trig VALUES (1,10);
503: }
504: } {1 {database or disk is full}}
505:
506: do_test sqllimits1-7.6 {
507: catchsql {
508: SELECT COUNT(*) FROM trig;
509: }
510: } {0 7}
511:
512: # Now check the response of the library to opening a file larger than
513: # the current max_page_count value. The response is to change the
514: # internal max_page_count value to match the actual size of the file.
515: if {[db eval {PRAGMA auto_vacuum}]} {
516: set fsize 1700
517: } else {
518: set fsize 1691
519: }
520: do_test sqllimits1-7.7.1 {
521: execsql {
522: PRAGMA max_page_count = 1000000;
523: CREATE TABLE abc(a, b, c);
524: INSERT INTO abc VALUES(1, 2, 3);
525: INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
526: INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
527: INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
528: INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
529: INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
530: INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
531: INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
532: INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
533: INSERT INTO abc SELECT a, b, c FROM abc;
534: INSERT INTO abc SELECT b, a, c FROM abc;
535: INSERT INTO abc SELECT c, b, a FROM abc;
536: }
537: expr [file size test.db] / 1024
538: } $fsize
539: do_test sqllimits1-7.7.2 {
540: db close
541: sqlite3 db test.db
542: execsql {
543: PRAGMA max_page_count = 1000;
544: }
545: execsql {
546: SELECT count(*) FROM sqlite_master;
547: }
548: } {6}
549: do_test sqllimits1-7.7.3 {
550: execsql {
551: PRAGMA max_page_count;
552: }
553: } $fsize
554: do_test sqllimits1-7.7.4 {
555: execsql {
556: DROP TABLE abc;
557: }
558: } {}
559:
560: #--------------------------------------------------------------------
561: # Test cases sqllimits1-8.* test the SQLITE_MAX_COLUMN limit.
562: #
563: set SQLITE_LIMIT_COLUMN 200
564: sqlite3_limit db SQLITE_LIMIT_COLUMN $SQLITE_LIMIT_COLUMN
565: do_test sqllimits1-8.1 {
566: # Columns in a table.
567: set cols [list]
568: for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
569: lappend cols "c$i"
570: }
571: catchsql "CREATE TABLE t([join $cols ,])"
572: } {1 {too many columns on t}}
573:
574: do_test sqllimits1-8.2 {
575: # Columns in the result-set of a SELECT.
576: set cols [list]
577: for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
578: lappend cols "sql AS sql$i"
579: }
580: catchsql "SELECT [join $cols ,] FROM sqlite_master"
581: } {1 {too many columns in result set}}
582:
583: do_test sqllimits1-8.3 {
584: # Columns in the result-set of a sub-SELECT.
585: set cols [list]
586: for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
587: lappend cols "sql AS sql$i"
588: }
589: catchsql "SELECT sql4 FROM (SELECT [join $cols ,] FROM sqlite_master)"
590: } {1 {too many columns in result set}}
591:
592: do_test sqllimits1-8.4 {
593: # Columns in an index.
594: set cols [list]
595: for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
596: lappend cols c
597: }
598: set sql1 "CREATE TABLE t1(c);"
599: set sql2 "CREATE INDEX i1 ON t1([join $cols ,]);"
600: catchsql "$sql1 ; $sql2"
601: } {1 {too many columns in index}}
602:
603: do_test sqllimits1-8.5 {
604: # Columns in a GROUP BY clause.
605: catchsql "SELECT * FROM t1 GROUP BY [join $cols ,]"
606: } {1 {too many terms in GROUP BY clause}}
607:
608: do_test sqllimits1-8.6 {
609: # Columns in an ORDER BY clause.
610: catchsql "SELECT * FROM t1 ORDER BY [join $cols ,]"
611: } {1 {too many terms in ORDER BY clause}}
612:
613: do_test sqllimits1-8.7 {
614: # Assignments in an UPDATE statement.
615: set cols [list]
616: for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
617: lappend cols "c = 1"
618: }
619: catchsql "UPDATE t1 SET [join $cols ,];"
620: } {1 {too many columns in set list}}
621:
622: do_test sqllimits1-8.8 {
623: # Columns in a view definition:
624: set cols [list]
625: for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
626: lappend cols "c$i"
627: }
628: catchsql "CREATE VIEW v1 AS SELECT [join $cols ,] FROM t1;"
629: } {1 {too many columns in result set}}
630:
631: do_test sqllimits1-8.9 {
632: # Columns in a view definition (testing * expansion):
633: set cols [list]
634: for {set i 0} {$i < $SQLITE_LIMIT_COLUMN} {incr i} {
635: lappend cols "c$i"
636: }
637: catchsql "CREATE TABLE t2([join $cols ,])"
638: catchsql "CREATE VIEW v1 AS SELECT *, c1 AS o FROM t2;"
639: } {1 {too many columns in result set}}
640: do_test sqllimits1-8.10 {
641: # ORDER BY columns
642: set cols [list]
643: for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
644: lappend cols c
645: }
646: set sql "SELECT c FROM t1 ORDER BY [join $cols ,]"
647: catchsql $sql
648: } {1 {too many terms in ORDER BY clause}}
649: do_test sqllimits1-8.11 {
650: # ORDER BY columns
651: set cols [list]
652: for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
653: lappend cols [expr {$i%3 + 1}]
654: }
655: set sql "SELECT c, c+1, c+2 FROM t1 UNION SELECT c-1, c-2, c-3 FROM t1"
656: append sql " ORDER BY [join $cols ,]"
657: catchsql $sql
658: } {1 {too many terms in ORDER BY clause}}
659:
660:
661: #--------------------------------------------------------------------
662: # These tests - sqllimits1-9.* - test that the SQLITE_LIMIT_EXPR_DEPTH
663: # limit is enforced. The limit refers to the number of terms in
664: # the expression.
665: #
666: if {$SQLITE_MAX_EXPR_DEPTH==0} {
667: puts -nonewline stderr "WARNING: Compile with -DSQLITE_MAX_EXPR_DEPTH to run "
668: puts stderr "tests sqllimits1-9.X"
669: } else {
670: do_test sqllimits1-9.1 {
671: set max $::SQLITE_MAX_EXPR_DEPTH
672: set expr "(1 [string repeat {AND 1 } $max])"
673: catchsql [subst {
674: SELECT $expr
675: }]
676: } "1 {Expression tree is too large (maximum depth $::SQLITE_MAX_EXPR_DEPTH)}"
677:
678: # Attempting to beat the expression depth limit using nested SELECT
679: # queries causes a parser stack overflow.
680: do_test sqllimits1-9.2 {
681: set max $::SQLITE_MAX_EXPR_DEPTH
682: set expr "SELECT 1"
683: for {set i 0} {$i <= $max} {incr i} {
684: set expr "SELECT ($expr)"
685: }
686: catchsql [subst { $expr }]
687: } "1 {parser stack overflow}"
688:
689: if 0 {
690: do_test sqllimits1-9.3 {
691: execsql {
692: PRAGMA max_page_count = 1000000; -- 1 GB
693: CREATE TABLE v0(a);
694: INSERT INTO v0 VALUES(1);
695: }
696: db transaction {
697: for {set i 1} {$i < 200} {incr i} {
698: set expr "(a [string repeat {AND 1 } 50]) AS a"
699: execsql [subst {
700: CREATE VIEW v${i} AS SELECT $expr FROM v[expr {$i-1}]
701: }]
702: }
703: }
704: } {}
705:
706: do_test sqllimits1-9.4 {
707: catchsql {
708: SELECT a FROM v199
709: }
710: } "1 {Expression tree is too large (maximum depth $::SQLITE_MAX_EXPR_DEPTH)}"
711: }
712: }
713:
714: #--------------------------------------------------------------------
715: # Test cases sqllimits1-10.* test that the SQLITE_MAX_VDBE_OP
716: # limit works as expected. The limit refers to the number of opcodes
717: # in a single VDBE program.
718: #
719: # TODO
720:
721: #--------------------------------------------------------------------
722: # Test the SQLITE_LIMIT_FUNCTION_ARG limit works. Test case names
723: # match the pattern "sqllimits1-11.*".
724: #
725: for {set max 5} {$max<=$SQLITE_MAX_FUNCTION_ARG} {incr max} {
726: do_test sqllimits1-11.$max.1 {
727: set vals [list]
728: sqlite3_limit db SQLITE_LIMIT_FUNCTION_ARG $::max
729: for {set i 0} {$i < $::max} {incr i} {
730: lappend vals $i
731: }
732: catchsql "SELECT max([join $vals ,])"
733: } "0 [expr {$::max - 1}]"
734: do_test sqllimits1-11.$max.2 {
735: set vals [list]
736: for {set i 0} {$i <= $::max} {incr i} {
737: lappend vals $i
738: }
739: catchsql "SELECT max([join $vals ,])"
740: } {1 {too many arguments on function max}}
741:
742: # Test that it is SQLite, and not the implementation of the
743: # user function that is throwing the error.
744: proc myfunc {args} {error "I don't like to be called!"}
745: do_test sqllimits1-11.$max.2 {
746: db function myfunc myfunc
747: set vals [list]
748: for {set i 0} {$i <= $::max} {incr i} {
749: lappend vals $i
750: }
751: catchsql "SELECT myfunc([join $vals ,])"
752: } {1 {too many arguments on function myfunc}}
753: }
754:
755: #--------------------------------------------------------------------
756: # Test cases sqllimits1-12.*: Test the SQLITE_MAX_ATTACHED limit.
757: #
758: ifcapable attach {
759: do_test sqllimits1-12.1 {
760: set max $::SQLITE_MAX_ATTACHED
761: for {set i 0} {$i < ($max)} {incr i} {
762: forcedelete test${i}.db test${i}.db-journal
763: }
764: for {set i 0} {$i < ($max)} {incr i} {
765: execsql "ATTACH 'test${i}.db' AS aux${i}"
766: }
767: catchsql "ATTACH 'test${i}.db' AS aux${i}"
768: } "1 {too many attached databases - max $::SQLITE_MAX_ATTACHED}"
769: do_test sqllimits1-12.2 {
770: set max $::SQLITE_MAX_ATTACHED
771: for {set i 0} {$i < ($max)} {incr i} {
772: execsql "DETACH aux${i}"
773: }
774: } {}
775: }
776:
777: #--------------------------------------------------------------------
778: # Test cases sqllimits1-13.*: Check that the SQLITE_MAX_VARIABLE_NUMBER
779: # limit works.
780: #
781: do_test sqllimits1-13.1 {
782: set max $::SQLITE_MAX_VARIABLE_NUMBER
783: catchsql "SELECT ?[expr {$max+1}] FROM t1"
784: } "1 {variable number must be between ?1 and ?$::SQLITE_MAX_VARIABLE_NUMBER}"
785: do_test sqllimits1-13.2 {
786: set max $::SQLITE_MAX_VARIABLE_NUMBER
787: set vals [list]
788: for {set i 0} {$i < ($max+3)} {incr i} {
789: lappend vals ?
790: }
791: catchsql "SELECT [join $vals ,] FROM t1"
792: } "1 {too many SQL variables}"
793:
794:
795: #--------------------------------------------------------------------
796: # Test cases sqllimits1-15.* verify that the
797: # SQLITE_MAX_LIKE_PATTERN_LENGTH limit is enforced. This limit only
798: # applies to the built-in LIKE operator, supplying an external
799: # implementation by overriding the like() scalar function bypasses
800: # this limitation.
801: #
802: # These tests check that the limit is not incorrectly applied to
803: # the left-hand-side of the LIKE operator (the string being tested
804: # against the pattern).
805: #
806: set SQLITE_LIMIT_LIKE_PATTERN 1000
807: sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH $SQLITE_LIMIT_LIKE_PATTERN
808: do_test sqllimits1-15.1 {
809: set max $::SQLITE_LIMIT_LIKE_PATTERN
810: set ::pattern [string repeat "A%" [expr $max/2]]
811: set ::string [string repeat "A" [expr {$max*2}]]
812: execsql {
813: SELECT $::string LIKE $::pattern;
814: }
815: } {1}
816: do_test sqllimits1-15.2 {
817: set max $::SQLITE_LIMIT_LIKE_PATTERN
818: set ::pattern [string repeat "A%" [expr {($max/2) + 1}]]
819: set ::string [string repeat "A" [expr {$max*2}]]
820: catchsql {
821: SELECT $::string LIKE $::pattern;
822: }
823: } {1 {LIKE or GLOB pattern too complex}}
824:
825: #--------------------------------------------------------------------
826: # This test case doesn't really belong with the other limits tests.
827: # It is in this file because it is taxing to run, like the limits tests.
828: #
829: do_test sqllimits1-16.1 {
830: set ::N [expr int(([expr pow(2,32)]/50) + 1)]
831: expr (($::N*50) & 0xffffffff)<55
832: } {1}
833: do_test sqllimits1-16.2 {
834: set ::format "[string repeat A 60][string repeat "%J" $::N]"
835: catchsql {
836: SELECT strftime($::format, 1);
837: }
838: } {1 {string or blob too big}}
839:
840:
841: foreach {key value} [array get saved] {
842: catch {set $key $value}
843: }
844: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to sqllimits1.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test