1: # 2008 October 6
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 the LIMIT ... OFFSET ... clause
13: # of UPDATE and DELETE statements.
14: #
15: # $Id: wherelimit.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
16:
17: set testdir [file dirname $argv0]
18: source $testdir/tester.tcl
19:
20: proc create_test_data {size} {
21: # Build some test data
22: #
23: execsql {
24: DROP TABLE IF EXISTS t1;
25: CREATE TABLE t1(x int, y int);
26: BEGIN;
27: }
28: for {set i 1} {$i<=$size} {incr i} {
29: for {set j 1} {$j<=$size} {incr j} {
30: execsql "INSERT INTO t1 VALUES([expr {$i}],[expr {$j}])"
31: }
32: }
33: execsql {
34: COMMIT;
35: }
36: return {}
37: }
38:
39: ifcapable {update_delete_limit} {
40:
41: # check syntax error support
42: do_test wherelimit-0.1 {
43: catchsql {DELETE FROM t1 ORDER BY x}
44: } {1 {ORDER BY without LIMIT on DELETE}}
45: do_test wherelimit-0.2 {
46: catchsql {DELETE FROM t1 WHERE x=1 ORDER BY x}
47: } {1 {ORDER BY without LIMIT on DELETE}}
48: do_test wherelimit-0.3 {
49: catchsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x}
50: } {1 {ORDER BY without LIMIT on UPDATE}}
51:
52: # no AS on table sources
53: do_test wherelimit-0.4 {
54: catchsql {DELETE FROM t1 AS a WHERE x=1}
55: } {1 {near "AS": syntax error}}
56: do_test wherelimit-0.5 {
57: catchsql {UPDATE t1 AS a SET y=1 WHERE x=1}
58: } {1 {near "AS": syntax error}}
59:
60: # OFFSET w/o LIMIT
61: do_test wherelimit-0.6 {
62: catchsql {DELETE FROM t1 WHERE x=1 OFFSET 2}
63: } {1 {near "OFFSET": syntax error}}
64: do_test wherelimit-0.7 {
65: catchsql {UPDATE t1 SET y=1 WHERE x=1 OFFSET 2}
66: } {1 {near "OFFSET": syntax error}}
67:
68:
69: # check deletes w/o where clauses but with limit/offsets
70: create_test_data 5
71: do_test wherelimit-1.0 {
72: execsql {SELECT count(*) FROM t1}
73: } {25}
74: do_test wherelimit-1.1 {
75: execsql {DELETE FROM t1}
76: execsql {SELECT count(*) FROM t1}
77: } {0}
78: create_test_data 5
79: do_test wherelimit-1.2 {
80: execsql {DELETE FROM t1 LIMIT 5}
81: execsql {SELECT count(*) FROM t1}
82: } {20}
83: do_test wherelimit-1.3 {
84: # limit 5
85: execsql {DELETE FROM t1 ORDER BY x LIMIT 5}
86: execsql {SELECT count(*) FROM t1}
87: } {15}
88: do_test wherelimit-1.4 {
89: # limit 5, offset 2
90: execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 2}
91: execsql {SELECT count(*) FROM t1}
92: } {10}
93: do_test wherelimit-1.5 {
94: # limit 5, offset -2
95: execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET -2}
96: execsql {SELECT count(*) FROM t1}
97: } {5}
98: do_test wherelimit-1.6 {
99: # limit -5 (no limit), offset 2
100: execsql {DELETE FROM t1 ORDER BY x LIMIT 2, -5}
101: execsql {SELECT count(*) FROM t1}
102: } {2}
103: do_test wherelimit-1.7 {
104: # limit 5, offset -2 (no offset)
105: execsql {DELETE FROM t1 ORDER BY x LIMIT -2, 5}
106: execsql {SELECT count(*) FROM t1}
107: } {0}
108: create_test_data 5
109: do_test wherelimit-1.8 {
110: # limit -5 (no limit), offset -2 (no offset)
111: execsql {DELETE FROM t1 ORDER BY x LIMIT -2, -5}
112: execsql {SELECT count(*) FROM t1}
113: } {0}
114: create_test_data 3
115: do_test wherelimit-1.9 {
116: # limit 5, offset 2
117: execsql {DELETE FROM t1 ORDER BY x LIMIT 2, 5}
118: execsql {SELECT count(*) FROM t1}
119: } {4}
120: do_test wherelimit-1.10 {
121: # limit 5, offset 5
122: execsql {DELETE FROM t1 ORDER BY x LIMIT 5 OFFSET 5}
123: execsql {SELECT count(*) FROM t1}
124: } {4}
125: do_test wherelimit-1.11 {
126: # limit 50, offset 30
127: execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 30}
128: execsql {SELECT count(*) FROM t1}
129: } {4}
130: do_test wherelimit-1.12 {
131: # limit 50, offset 30
132: execsql {DELETE FROM t1 ORDER BY x LIMIT 30, 50}
133: execsql {SELECT count(*) FROM t1}
134: } {4}
135: do_test wherelimit-1.13 {
136: execsql {DELETE FROM t1 ORDER BY x LIMIT 50 OFFSET 50}
137: execsql {SELECT count(*) FROM t1}
138: } {4}
139:
140:
141: create_test_data 6
142: do_test wherelimit-2.0 {
143: execsql {SELECT count(*) FROM t1}
144: } {36}
145: do_test wherelimit-2.1 {
146: execsql {DELETE FROM t1 WHERE x=1}
147: execsql {SELECT count(*) FROM t1}
148: } {30}
149: create_test_data 6
150: do_test wherelimit-2.2 {
151: execsql {DELETE FROM t1 WHERE x=1 LIMIT 5}
152: execsql {SELECT count(*) FROM t1}
153: } {31}
154: do_test wherelimit-2.3 {
155: # limit 5
156: execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 5}
157: execsql {SELECT count(*) FROM t1}
158: } {30}
159: do_test wherelimit-2.4 {
160: # limit 5, offset 2
161: execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2}
162: execsql {SELECT count(*) FROM t1}
163: } {26}
164: do_test wherelimit-2.5 {
165: # limit 5, offset -2
166: execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2}
167: execsql {SELECT count(*) FROM t1}
168: } {24}
169: do_test wherelimit-2.6 {
170: # limit -5 (no limit), offset 2
171: execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 2, -5}
172: execsql {SELECT count(*) FROM t1}
173: } {20}
174: do_test wherelimit-2.7 {
175: # limit 5, offset -2 (no offset)
176: execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT -2, 5}
177: execsql {SELECT count(*) FROM t1}
178: } {18}
179: do_test wherelimit-2.8 {
180: # limit -5 (no limit), offset -2 (no offset)
181: execsql {DELETE FROM t1 WHERE x=4 ORDER BY x LIMIT -2, -5}
182: execsql {SELECT count(*) FROM t1}
183: } {12}
184: create_test_data 6
185: do_test wherelimit-2.9 {
186: # limit 5, offset 2
187: execsql {DELETE FROM t1 WHERE x=5 ORDER BY x LIMIT 2, 5}
188: execsql {SELECT count(*) FROM t1}
189: } {32}
190: do_test wherelimit-2.10 {
191: # limit 5, offset 5
192: execsql {DELETE FROM t1 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5}
193: execsql {SELECT count(*) FROM t1}
194: } {31}
195: do_test wherelimit-2.11 {
196: # limit 50, offset 30
197: execsql {DELETE FROM t1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30}
198: execsql {SELECT count(*) FROM t1}
199: } {31}
200: do_test wherelimit-2.12 {
201: # limit 50, offset 30
202: execsql {DELETE FROM t1 WHERE x=2 ORDER BY x LIMIT 30, 50}
203: execsql {SELECT count(*) FROM t1}
204: } {31}
205: do_test wherelimit-2.13 {
206: execsql {DELETE FROM t1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
207: execsql {SELECT count(*) FROM t1}
208: } {31}
209:
210:
211: create_test_data 6
212: do_test wherelimit-3.0 {
213: execsql {SELECT count(*) FROM t1}
214: } {36}
215: do_test wherelimit-3.1 {
216: execsql {UPDATE t1 SET y=1 WHERE x=1}
217: execsql {SELECT count(*) FROM t1 WHERE y=1}
218: } {11}
219: create_test_data 6
220: do_test wherelimit-3.2 {
221: execsql {UPDATE t1 SET y=1 WHERE x=1 LIMIT 5}
222: execsql {SELECT count(*) FROM t1 WHERE y=1}
223: } {10}
224: do_test wherelimit-3.3 {
225: # limit 5
226: execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5}
227: execsql {SELECT count(*) FROM t1 WHERE y=2}
228: } {9}
229: create_test_data 6
230: do_test wherelimit-3.4 {
231: # limit 5, offset 2
232: execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET 2}
233: execsql {SELECT count(*) FROM t1 WHERE y=1}
234: } {6}
235: do_test wherelimit-3.5 {
236: # limit 5, offset -2
237: execsql {UPDATE t1 SET y=2 WHERE x=2 ORDER BY x LIMIT 5 OFFSET -2}
238: execsql {SELECT count(*) FROM t1 WHERE y=1}
239: } {5}
240: do_test wherelimit-3.6 {
241: # limit -5 (no limit), offset 2
242: execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT 2, -5}
243: execsql {SELECT count(*) FROM t1 WHERE y=3}
244: } {8}
245: do_test wherelimit-3.7 {
246: # limit 5, offset -2 (no offset)
247: execsql {UPDATE t1 SET y=3 WHERE x=3 ORDER BY x LIMIT -2, 5}
248: execsql {SELECT count(*) FROM t1 WHERE y=3}
249: } {10}
250:
251: do_test wherelimit-3.8 {
252: # limit -5 (no limit), offset -2 (no offset)
253: execsql {UPDATE t1 SET y=4 WHERE x=4 ORDER BY x LIMIT -2, -5}
254: execsql {SELECT count(*) FROM t1 WHERE y=4}
255: } {9}
256: create_test_data 6
257: do_test wherelimit-3.9 {
258: # limit 5, offset 2
259: execsql {UPDATE t1 SET y=4 WHERE x=5 ORDER BY x LIMIT 2, 5}
260: execsql {SELECT count(*) FROM t1 WHERE y=4}
261: } {9}
262: do_test wherelimit-3.10 {
263: # limit 5, offset 5
264: execsql {UPDATE t1 SET y=4 WHERE x=6 ORDER BY x LIMIT 5 OFFSET 5}
265: execsql {SELECT count(*) FROM t1 WHERE y=1}
266: } {6}
267: do_test wherelimit-3.11 {
268: # limit 50, offset 30
269: execsql {UPDATE t1 SET y=1 WHERE x=1 ORDER BY x LIMIT 50 OFFSET 30}
270: execsql {SELECT count(*) FROM t1 WHERE y=1}
271: } {6}
272: do_test wherelimit-3.12 {
273: # limit 50, offset 30
274: execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50}
275: execsql {SELECT count(*) FROM t1 WHERE y=1}
276: } {6}
277: do_test wherelimit-3.13 {
278: execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
279: execsql {SELECT count(*) FROM t1 WHERE y=1}
280: } {6}
281:
282: }
283:
284: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to wherelimit.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