test/wherelimit2.test

   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