Debian Codegraph @

test/wherelimit2.test

raw
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
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set testprefix wherelimit2
19
20 ifcapable !update_delete_limit {
21 finish_test
22 return
23 }
24
25 #-------------------------------------------------------------------------
26 # Test with views and INSTEAD OF triggers.
27 #
28 do_execsql_test 1.0 {
29 CREATE TABLE t1(a, b);
30 INSERT INTO t1 VALUES(1, 'f');
31 INSERT INTO t1 VALUES(2, 'e');
32 INSERT INTO t1 VALUES(3, 'd');
33 INSERT INTO t1 VALUES(4, 'c');
34 INSERT INTO t1 VALUES(5, 'b');
35 INSERT INTO t1 VALUES(6, 'a');
36
37 CREATE VIEW v1 AS SELECT a,b FROM t1;
38 CREATE TABLE log(op, a);
39
40 CREATE TRIGGER v1del INSTEAD OF DELETE ON v1 BEGIN
41 INSERT INTO log VALUES('delete', old.a);
42 END;
43
44 CREATE TRIGGER v1upd INSTEAD OF UPDATE ON v1 BEGIN
45 INSERT INTO log VALUES('update', old.a);
46 END;
47 }
48
49 do_execsql_test 1.1 {
50 DELETE FROM v1 ORDER BY a LIMIT 3;
51 SELECT * FROM log; DELETE FROM log;
52 } {
53 delete 1 delete 2 delete 3
54 }
55 do_execsql_test 1.2 {
56 DELETE FROM v1 ORDER BY b LIMIT 3;
57 SELECT * FROM log; DELETE FROM log;
58 } {
59 delete 6 delete 5 delete 4
60 }
61 do_execsql_test 1.3 {
62 UPDATE v1 SET b = 555 ORDER BY a LIMIT 3;
63 SELECT * FROM log; DELETE FROM log;
64 } {
65 update 1 update 2 update 3
66 }
67 do_execsql_test 1.4 {
68 UPDATE v1 SET b = 555 ORDER BY b LIMIT 3;
69 SELECT * FROM log; DELETE FROM log;
70 } {
71 update 6 update 5 update 4
72 }
73
74 #-------------------------------------------------------------------------
75 # Simple test using WITHOUT ROWID table.
76 #
77 do_execsql_test 2.1.0 {
78 CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;
79 INSERT INTO t2 VALUES(1, 1, 'h');
80 INSERT INTO t2 VALUES(1, 2, 'g');
81 INSERT INTO t2 VALUES(2, 1, 'f');
82 INSERT INTO t2 VALUES(2, 2, 'e');
83 INSERT INTO t2 VALUES(3, 1, 'd');
84 INSERT INTO t2 VALUES(3, 2, 'c');
85 INSERT INTO t2 VALUES(4, 1, 'b');
86 INSERT INTO t2 VALUES(4, 2, 'a');
87 }
88
89 do_execsql_test 2.1.1 {
90 BEGIN;
91 DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2;
92 SELECT c FROM t2 ORDER BY 1;
93 ROLLBACK;
94 } {a c e f g h}
95
96 do_execsql_test 2.1.2 {
97 BEGIN;
98 UPDATE t2 SET c=NULL ORDER BY a, b DESC LIMIT 3 OFFSET 1;
99 SELECT a, b, c FROM t2;
100 ROLLBACK;
101 } {
102 1 1 {}
103 1 2 g
104 2 1 {}
105 2 2 {}
106 3 1 d
107 3 2 c
108 4 1 b
109 4 2 a
110 }
111
112 do_execsql_test 2.2.0 {
113 DROP TABLE t2;
114 CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c) WITHOUT ROWID;
115 INSERT INTO t2 VALUES(1, 1, 'h');
116 INSERT INTO t2 VALUES(2, 2, 'g');
117 INSERT INTO t2 VALUES(3, 1, 'f');
118 INSERT INTO t2 VALUES(4, 2, 'e');
119 INSERT INTO t2 VALUES(5, 1, 'd');
120 INSERT INTO t2 VALUES(6, 2, 'c');
121 INSERT INTO t2 VALUES(7, 1, 'b');
122 INSERT INTO t2 VALUES(8, 2, 'a');
123 }
124
125 do_execsql_test 2.2.1 {
126 BEGIN;
127 DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2;
128 SELECT c FROM t2 ORDER BY 1;
129 ROLLBACK;
130 } {a c e f g h}
131
132 do_execsql_test 2.2.2 {
133 BEGIN;
134 UPDATE t2 SET c=NULL ORDER BY a DESC LIMIT 3 OFFSET 1;
135 SELECT a, b, c FROM t2;
136 ROLLBACK;
137 } {
138 1 1 h
139 2 2 g
140 3 1 f
141 4 2 e
142 5 1 {}
143 6 2 {}
144 7 1 {}
145 8 2 a
146 }
147
148 #-------------------------------------------------------------------------
149 # Test using a virtual table
150 #
151 ifcapable fts5 {
152 do_execsql_test 3.0 {
153 CREATE VIRTUAL TABLE ft USING fts5(x);
154 INSERT INTO ft(rowid, x) VALUES(-45, 'a a');
155 INSERT INTO ft(rowid, x) VALUES(12, 'a b');
156 INSERT INTO ft(rowid, x) VALUES(444, 'a c');
157 INSERT INTO ft(rowid, x) VALUES(12300, 'a d');
158 INSERT INTO ft(rowid, x) VALUES(25400, 'a c');
159 INSERT INTO ft(rowid, x) VALUES(25401, 'a b');
160 INSERT INTO ft(rowid, x) VALUES(50000, 'a a');
161 }
162
163 do_execsql_test 3.1.1 {
164 BEGIN;
165 DELETE FROM ft ORDER BY rowid LIMIT 3;
166 SELECT x FROM ft;
167 ROLLBACK;
168 } {{a d} {a c} {a b} {a a}}
169
170 do_execsql_test 3.1.2 {
171 BEGIN;
172 DELETE FROM ft WHERE ft MATCH 'a' ORDER BY rowid LIMIT 3;
173 SELECT x FROM ft;
174 ROLLBACK;
175 } {{a d} {a c} {a b} {a a}}
176
177 do_execsql_test 3.1.3 {
178 BEGIN;
179 DELETE FROM ft WHERE ft MATCH 'b' ORDER BY rowid ASC LIMIT 1 OFFSET 1;
180 SELECT rowid FROM ft;
181 ROLLBACK;
182 } {-45 12 444 12300 25400 50000}
183
184 do_execsql_test 3.2.1 {
185 BEGIN;
186 UPDATE ft SET x='hello' ORDER BY rowid LIMIT 2 OFFSET 2;
187 SELECT x FROM ft;
188 ROLLBACK;
189 } {{a a} {a b} hello hello {a c} {a b} {a a}}
190
191 do_execsql_test 3.2.2 {
192 BEGIN;
193 UPDATE ft SET x='hello' WHERE ft MATCH 'a'
194 ORDER BY rowid DESC LIMIT 2 OFFSET 2;
195 SELECT x FROM ft;
196 ROLLBACK;
197 } {{a a} {a b} {a c} hello hello {a b} {a a}}
198 } ;# fts5
199
200 #-------------------------------------------------------------------------
201 # Test using INDEXED BY clauses.
202 #
203 do_execsql_test 4.0 {
204 CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c, d);
205 CREATE INDEX x1bc ON x1(b, c);
206 INSERT INTO x1 VALUES(1,1,1,1);
207 INSERT INTO x1 VALUES(2,1,2,2);
208 INSERT INTO x1 VALUES(3,2,1,3);
209 INSERT INTO x1 VALUES(4,2,2,3);
210 INSERT INTO x1 VALUES(5,3,1,2);
211 INSERT INTO x1 VALUES(6,3,2,1);
212 }
213
214 do_execsql_test 4.1 {
215 BEGIN;
216 DELETE FROM x1 ORDER BY a LIMIT 2;
217 SELECT a FROM x1;
218 ROLLBACK;
219 } {3 4 5 6}
220
221 # 2020-06-03: Query planner improved so that a solution is possible.
222 #
223 #do_catchsql_test 4.2 {
224 # DELETE FROM x1 INDEXED BY x1bc WHERE d=3 LIMIT 1;
225 #} {1 {no query solution}}
226
227 do_execsql_test 4.3 {
228 DELETE FROM x1 INDEXED BY x1bc WHERE b=3 LIMIT 1;
229 SELECT a FROM x1;
230 } {1 2 3 4 6}
231
232 # 2020-06-03: Query planner improved so that a solution is possible.
233 #
234 #do_catchsql_test 4.4 {
235 # UPDATE x1 INDEXED BY x1bc SET d=5 WHERE d=3 LIMIT 1;
236 #} {1 {no query solution}}
237
238 do_execsql_test 4.5 {
239 UPDATE x1 INDEXED BY x1bc SET d=5 WHERE b=2 LIMIT 1;
240 SELECT a, d FROM x1;
241 } {1 1 2 2 3 5 4 3 6 1}
242
243 #-------------------------------------------------------------------------
244 # Test using object names that require quoting.
245 #
246 do_execsql_test 5.0 {
247 CREATE TABLE "x y"("a b" PRIMARY KEY, "c d") WITHOUT ROWID;
248 CREATE INDEX xycd ON "x y"("c d");
249
250 INSERT INTO "x y" VALUES('a', 'a');
251 INSERT INTO "x y" VALUES('b', 'b');
252 INSERT INTO "x y" VALUES('c', 'c');
253 INSERT INTO "x y" VALUES('d', 'd');
254 INSERT INTO "x y" VALUES('e', 'a');
255 INSERT INTO "x y" VALUES('f', 'b');
256 INSERT INTO "x y" VALUES('g', 'c');
257 INSERT INTO "x y" VALUES('h', 'd');
258 }
259
260 do_execsql_test 5.1 {
261 BEGIN;
262 DELETE FROM "x y" WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
263 SELECT * FROM "x y" ORDER BY 1;
264 ROLLBACK;
265 } {
266 a a c c d d e a g c h d
267 }
268
269 do_execsql_test 5.2 {
270 BEGIN;
271 UPDATE "x y" SET "c d"='e' WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
272 SELECT * FROM "x y" ORDER BY 1;
273 ROLLBACK;
274 } {
275 a a b e c c d d e a f e g c h d
276 }
277
278 proc log {args} { lappend ::log {*}$args }
279 db func log log
280 do_execsql_test 5.3 {
281 CREATE VIEW "v w" AS SELECT * FROM "x y";
282 CREATE TRIGGER tr1 INSTEAD OF DELETE ON "v w" BEGIN
283 SELECT log(old."a b", old."c d");
284 END;
285 CREATE TRIGGER tr2 INSTEAD OF UPDATE ON "v w" BEGIN
286 SELECT log(new."a b", new."c d");
287 END;
288 }
289
290 do_test 5.4 {
291 set ::log {}
292 execsql { DELETE FROM "v w" ORDER BY "a b" LIMIT 3 }
293 set ::log
294 } {a a b b c c}
295
296 do_test 5.5 {
297 set ::log {}
298 execsql { UPDATE "v w" SET "a b" = "a b" || 'x' ORDER BY "a b" LIMIT 5; }
299 set ::log
300 } {ax a bx b cx c dx d ex a}
301
302 #-----------------------------------------------------------------------
303 reset_db
304 do_execsql_test 6.0 {
305 CREATE TABLE t2(x);
306 INSERT INTO t2(x) VALUES(1),(2),(3),(5),(8),(13);
307 } {}
308
309 do_execsql_test 6.1 {
310 WITH t2 AS MATERIALIZED (VALUES(5))
311 DELETE FROM t2 ORDER BY rank()OVER() LIMIT 2;
312 }
313
314 do_execsql_test 6.2 {
315 SELECT * FROM t2;
316 } {3 5 8 13}
317
318 #-------------------------------------------------------------------------
319
320 do_execsql_test 7.0 {
321 CREATE TABLE t1(a INT); INSERT INTO t1(a) VALUES(0);
322 } {}
323
324 do_execsql_test 7.1 {
325 WITH t1(b) AS (SELECT * FROM (SELECT * FROM (VALUES(2))))
326 UPDATE t1 SET a=3 LIMIT 1;
327 }
328
329 do_execsql_test 7.2 {
330 SELECT * FROM t1;
331 } {3}
332
333 finish_test