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.2 2008/10/10 18:25:46 shane Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20proc 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
39ifcapable {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
284finish_test
285