gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/wherelimit2.test (about) 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 finish_test