| # 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} |
| |
| 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} |
| |
| 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} |
| |
| |
| finish_test |