Adding upstream version 3.45.1.upstream/3.45.1

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 333 insertions, 0 deletions

diff --git a/test/wherelimit2.test b/test/wherelimit2.test
new file mode 100644
index 0000000..57288bf
--- /dev/null
+++ b/test/wherelimit2.test
@@ -0,0 +1,333 @@
+# 2008 October 6
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.  The
+# focus of this file is testing the LIMIT ... OFFSET ... clause
+#  of UPDATE and DELETE statements.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix wherelimit2
+
+ifcapable !update_delete_limit {
+  finish_test
+  return
+}
+
+#-------------------------------------------------------------------------
+# Test with views and INSTEAD OF triggers.
+#
+do_execsql_test 1.0 {
+  CREATE TABLE t1(a, b);
+  INSERT INTO t1 VALUES(1, 'f');
+  INSERT INTO t1 VALUES(2, 'e');
+  INSERT INTO t1 VALUES(3, 'd');
+  INSERT INTO t1 VALUES(4, 'c');
+  INSERT INTO t1 VALUES(5, 'b');
+  INSERT INTO t1 VALUES(6, 'a');
+
+  CREATE VIEW v1 AS SELECT a,b FROM t1;
+  CREATE TABLE log(op, a);
+
+  CREATE TRIGGER v1del INSTEAD OF DELETE ON v1 BEGIN
+    INSERT INTO log VALUES('delete', old.a);
+  END;
+
+  CREATE TRIGGER v1upd INSTEAD OF UPDATE ON v1 BEGIN
+    INSERT INTO log VALUES('update', old.a);
+  END;
+}
+
+do_execsql_test 1.1 {
+  DELETE FROM v1 ORDER BY a LIMIT 3;
+  SELECT * FROM log; DELETE FROM log;
+} {
+  delete 1 delete 2 delete 3
+}
+do_execsql_test 1.2 {
+  DELETE FROM v1 ORDER BY b LIMIT 3;
+  SELECT * FROM log; DELETE FROM log;
+} {
+  delete 6 delete 5 delete 4
+}
+do_execsql_test 1.3 {
+  UPDATE v1 SET b = 555 ORDER BY a LIMIT 3;
+  SELECT * FROM log; DELETE FROM log;
+} {
+  update 1 update 2 update 3
+}
+do_execsql_test 1.4 {
+  UPDATE v1 SET b = 555 ORDER BY b LIMIT 3;
+  SELECT * FROM log; DELETE FROM log;
+} {
+  update 6 update 5 update 4
+}
+
+#-------------------------------------------------------------------------
+# Simple test using WITHOUT ROWID table.
+#
+do_execsql_test 2.1.0 {
+  CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;
+  INSERT INTO t2 VALUES(1, 1, 'h');
+  INSERT INTO t2 VALUES(1, 2, 'g');
+  INSERT INTO t2 VALUES(2, 1, 'f');
+  INSERT INTO t2 VALUES(2, 2, 'e');
+  INSERT INTO t2 VALUES(3, 1, 'd');
+  INSERT INTO t2 VALUES(3, 2, 'c');
+  INSERT INTO t2 VALUES(4, 1, 'b');
+  INSERT INTO t2 VALUES(4, 2, 'a');
+}
+
+do_execsql_test 2.1.1 {
+  BEGIN;
+    DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2;
+    SELECT c FROM t2 ORDER BY 1;
+  ROLLBACK;
+} {a c e f g h}
+
+do_execsql_test 2.1.2 {
+  BEGIN;
+    UPDATE t2 SET c=NULL ORDER BY a, b DESC LIMIT 3 OFFSET 1;
+    SELECT a, b, c FROM t2;
+  ROLLBACK;
+} {
+  1 1 {} 
+  1 2 g 
+  2 1 {} 
+  2 2 {} 
+  3 1 d 
+  3 2 c 
+  4 1 b 
+  4 2 a
+}
+
+do_execsql_test 2.2.0 {
+  DROP TABLE t2;
+  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c) WITHOUT ROWID;
+  INSERT INTO t2 VALUES(1, 1, 'h');
+  INSERT INTO t2 VALUES(2, 2, 'g');
+  INSERT INTO t2 VALUES(3, 1, 'f');
+  INSERT INTO t2 VALUES(4, 2, 'e');
+  INSERT INTO t2 VALUES(5, 1, 'd');
+  INSERT INTO t2 VALUES(6, 2, 'c');
+  INSERT INTO t2 VALUES(7, 1, 'b');
+  INSERT INTO t2 VALUES(8, 2, 'a');
+}
+
+do_execsql_test 2.2.1 {
+  BEGIN;
+    DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2;
+    SELECT c FROM t2 ORDER BY 1;
+  ROLLBACK;
+} {a c e f g h}
+
+do_execsql_test 2.2.2 {
+  BEGIN;
+    UPDATE t2 SET c=NULL ORDER BY a DESC LIMIT 3 OFFSET 1;
+    SELECT a, b, c FROM t2;
+  ROLLBACK;
+} {
+  1 1 h
+  2 2 g 
+  3 1 f
+  4 2 e
+  5 1 {}
+  6 2 {} 
+  7 1 {} 
+  8 2 a
+}
+
+#-------------------------------------------------------------------------
+# Test using a virtual table
+#
+ifcapable fts5 {
+  do_execsql_test 3.0 {
+    CREATE VIRTUAL TABLE ft USING fts5(x);
+    INSERT INTO ft(rowid, x) VALUES(-45,   'a a');
+    INSERT INTO ft(rowid, x) VALUES(12,    'a b');
+    INSERT INTO ft(rowid, x) VALUES(444,   'a c');
+    INSERT INTO ft(rowid, x) VALUES(12300, 'a d');
+    INSERT INTO ft(rowid, x) VALUES(25400, 'a c');
+    INSERT INTO ft(rowid, x) VALUES(25401, 'a b');
+    INSERT INTO ft(rowid, x) VALUES(50000, 'a a');
+  }
+
+  do_execsql_test 3.1.1 {
+    BEGIN;
+      DELETE FROM ft ORDER BY rowid LIMIT 3;
+      SELECT x FROM ft;
+    ROLLBACK;
+  } {{a d} {a c} {a b} {a a}}
+
+  do_execsql_test 3.1.2 {
+    BEGIN;
+      DELETE FROM ft WHERE ft MATCH 'a' ORDER BY rowid LIMIT 3;
+      SELECT x FROM ft;
+    ROLLBACK;
+  } {{a d} {a c} {a b} {a a}}
+  
+  do_execsql_test 3.1.3 {
+    BEGIN;
+      DELETE FROM ft WHERE ft MATCH 'b' ORDER BY rowid ASC LIMIT 1 OFFSET 1;
+      SELECT rowid FROM ft;
+    ROLLBACK;
+  } {-45 12 444 12300 25400 50000}
+
+  do_execsql_test 3.2.1 {
+    BEGIN;
+      UPDATE ft SET x='hello' ORDER BY rowid LIMIT 2 OFFSET 2;
+      SELECT x FROM ft;
+    ROLLBACK;
+  } {{a a} {a b} hello hello {a c} {a b} {a a}}
+
+  do_execsql_test 3.2.2 {
+    BEGIN;
+      UPDATE ft SET x='hello' WHERE ft MATCH 'a' 
+          ORDER BY rowid DESC LIMIT 2 OFFSET 2;
+      SELECT x FROM ft;
+    ROLLBACK;
+  } {{a a} {a b} {a c} hello hello {a b} {a a}}
+} ;# fts5
+
+#-------------------------------------------------------------------------
+# Test using INDEXED BY clauses.
+#
+do_execsql_test 4.0 {
+  CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c, d);
+  CREATE INDEX x1bc ON x1(b, c);
+  INSERT INTO x1 VALUES(1,1,1,1);
+  INSERT INTO x1 VALUES(2,1,2,2);
+  INSERT INTO x1 VALUES(3,2,1,3);
+  INSERT INTO x1 VALUES(4,2,2,3);
+  INSERT INTO x1 VALUES(5,3,1,2);
+  INSERT INTO x1 VALUES(6,3,2,1);
+}
+
+do_execsql_test 4.1 {
+  BEGIN;
+    DELETE FROM x1 ORDER BY a LIMIT 2;
+    SELECT a FROM x1;
+  ROLLBACK;
+} {3 4 5 6}
+
+# 2020-06-03: Query planner improved so that a solution is possible.
+#
+#do_catchsql_test 4.2 {
+#  DELETE FROM x1 INDEXED BY x1bc WHERE d=3 LIMIT 1;
+#} {1 {no query solution}}
+
+do_execsql_test 4.3 {
+  DELETE FROM x1 INDEXED BY x1bc WHERE b=3 LIMIT 1;
+  SELECT a FROM x1;
+} {1 2 3 4 6}
+
+# 2020-06-03: Query planner improved so that a solution is possible.
+#
+#do_catchsql_test 4.4 {
+#  UPDATE x1 INDEXED BY x1bc SET d=5 WHERE d=3 LIMIT 1;
+#} {1 {no query solution}}
+
+do_execsql_test 4.5 {
+  UPDATE x1 INDEXED BY x1bc SET d=5 WHERE b=2 LIMIT 1;
+  SELECT a, d FROM x1;
+} {1 1 2 2 3 5 4 3 6 1}
+
+#-------------------------------------------------------------------------
+# Test using object names that require quoting.
+#
+do_execsql_test 5.0 {
+  CREATE TABLE "x y"("a b" PRIMARY KEY, "c d") WITHOUT ROWID;
+  CREATE INDEX xycd ON "x y"("c d");
+
+  INSERT INTO "x y" VALUES('a', 'a');
+  INSERT INTO "x y" VALUES('b', 'b');
+  INSERT INTO "x y" VALUES('c', 'c');
+  INSERT INTO "x y" VALUES('d', 'd');
+  INSERT INTO "x y" VALUES('e', 'a');
+  INSERT INTO "x y" VALUES('f', 'b');
+  INSERT INTO "x y" VALUES('g', 'c');
+  INSERT INTO "x y" VALUES('h', 'd');
+}
+
+do_execsql_test 5.1 {
+  BEGIN;
+    DELETE FROM "x y" WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
+    SELECT * FROM "x y" ORDER BY 1;
+  ROLLBACK;
+} {
+  a a c c d d e a g c h d
+}
+
+do_execsql_test 5.2 {
+  BEGIN;
+    UPDATE "x y" SET "c d"='e' WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
+    SELECT * FROM "x y" ORDER BY 1;
+  ROLLBACK;
+} {
+  a a b e c c d d e a f e g c h d
+}
+
+proc log {args} { lappend ::log {*}$args }
+db func log log
+do_execsql_test 5.3 {
+  CREATE VIEW "v w" AS SELECT * FROM "x y";
+  CREATE TRIGGER tr1 INSTEAD OF DELETE ON "v w" BEGIN
+    SELECT log(old."a b", old."c d");
+  END;
+  CREATE TRIGGER tr2 INSTEAD OF UPDATE ON "v w" BEGIN
+    SELECT log(new."a b", new."c d");
+  END;
+}
+
+do_test 5.4 {
+  set ::log {}
+  execsql { DELETE FROM "v w" ORDER BY "a b" LIMIT 3 }
+  set ::log
+} {a a b b c c}
+
+do_test 5.5 {
+  set ::log {}
+  execsql { UPDATE "v w" SET "a b" = "a b" || 'x' ORDER BY "a b" LIMIT 5; }
+  set ::log
+} {ax a bx b cx c dx d ex a}
+
+#-----------------------------------------------------------------------
+reset_db
+do_execsql_test 6.0 {
+  CREATE TABLE t2(x);  
+  INSERT INTO t2(x) VALUES(1),(2),(3),(5),(8),(13);
+} {}
+
+do_execsql_test 6.1 {
+  WITH t2 AS MATERIALIZED (VALUES(5))
+  DELETE FROM t2 ORDER BY rank()OVER() LIMIT 2;
+}
+
+do_execsql_test 6.2 {
+  SELECT * FROM t2;
+} {3 5 8 13}
+
+#-------------------------------------------------------------------------
+
+do_execsql_test 7.0 {
+  CREATE TABLE t1(a INT); INSERT INTO t1(a) VALUES(0);
+} {}
+
+do_execsql_test 7.1 {
+  WITH t1(b) AS (SELECT * FROM (SELECT * FROM (VALUES(2))))
+  UPDATE t1 SET a=3 LIMIT 1;
+}
+
+do_execsql_test 7.2 {
+  SELECT * FROM t1;
+} {3}
+
+finish_test