/ Check-in [f8c4e33f]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add further tests for the code on this branch.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | update-delete-limit-fix
Files: files | file ages | folders
SHA3-256: f8c4e33f4813e0c909064406b5cc17e2d465d8a48a50ede1d356b39479d3d669
User & Date: dan 2017-11-10 17:47:54
Context
2017-11-10
20:13
Omit some extra code from non-SQLITE_ENABLE_UPDATE_DELETE_LIMIT builds. Closed-Leaf check-in: 72be33f9 user: dan tags: update-delete-limit-fix
17:47
Add further tests for the code on this branch. check-in: f8c4e33f user: dan tags: update-delete-limit-fix
16:14
Fix a problem involving "DELETE/UPDATE...LIMIT" statements that use an INDEXED BY clause. check-in: 09f94c2c user: dan tags: update-delete-limit-fix
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added test/wherelfault.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 fault-injection with the 
           13  +# LIMIT ... OFFSET ... clause of UPDATE and DELETE statements.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +source $testdir/malloc_common.tcl
           19  +set testprefix wherelfault
           20  +
           21  +ifcapable !update_delete_limit {
           22  +  finish_test
           23  +  return
           24  +}
           25  +
           26  +do_execsql_test 1.0 {
           27  +  CREATE TABLE t1(a, b);
           28  +  INSERT INTO t1 VALUES(1, 'f');
           29  +  INSERT INTO t1 VALUES(2, 'e');
           30  +  INSERT INTO t1 VALUES(3, 'd');
           31  +  INSERT INTO t1 VALUES(4, 'c');
           32  +  INSERT INTO t1 VALUES(5, 'b');
           33  +  INSERT INTO t1 VALUES(6, 'a');
           34  +
           35  +  CREATE VIEW v1 AS SELECT a,b FROM t1;
           36  +  CREATE TABLE log(op, a);
           37  +
           38  +  CREATE TRIGGER v1del INSTEAD OF DELETE ON v1 BEGIN
           39  +    INSERT INTO log VALUES('delete', old.a);
           40  +  END;
           41  +
           42  +  CREATE TRIGGER v1upd INSTEAD OF UPDATE ON v1 BEGIN
           43  +    INSERT INTO log VALUES('update', old.a);
           44  +  END;
           45  +}
           46  +
           47  +faultsim_save_and_close
           48  +do_faultsim_test 1.1 -prep {
           49  +  faultsim_restore_and_reopen
           50  +  db eval {SELECT * FROM sqlite_master}
           51  +} -body {
           52  +  execsql { DELETE FROM v1 ORDER BY a LIMIT 3; }
           53  +} -test {
           54  +  faultsim_test_result {0 {}} 
           55  +}
           56  +
           57  +do_faultsim_test 1.2 -prep {
           58  +  faultsim_restore_and_reopen
           59  +  db eval {SELECT * FROM sqlite_master}
           60  +} -body {
           61  +  execsql { UPDATE v1 SET b = 555 ORDER BY a LIMIT 3 }
           62  +} -test {
           63  +  faultsim_test_result {0 {}} 
           64  +}
           65  +
           66  +#-------------------------------------------------------------------------
           67  +sqlite3 db test.db
           68  +do_execsql_test 2.1.0 {
           69  +  CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;
           70  +}
           71  +faultsim_save_and_close
           72  +
           73  +do_faultsim_test 2.1 -prep {
           74  +  faultsim_restore_and_reopen
           75  +  db eval {SELECT * FROM sqlite_master}
           76  +} -body {
           77  +  execsql { DELETE FROM t2 WHERE c=? ORDER BY a DESC LIMIT 10 }
           78  +} -test {
           79  +  faultsim_test_result {0 {}} 
           80  +}
           81  +
           82  +finish_test

Changes to test/wherelimit2.test.

   235    235     UPDATE x1 INDEXED BY x1bc SET d=5 WHERE b=2 LIMIT 1;
   236    236     SELECT a, d FROM x1;
   237    237   } {1 1 2 2 3 5 4 3 6 1}
   238    238   
   239    239   #-------------------------------------------------------------------------
   240    240   # Test using object names that require quoting.
   241    241   #
          242  +do_execsql_test 5.0 {
          243  +  CREATE TABLE "x y"("a b" PRIMARY KEY, "c d") WITHOUT ROWID;
          244  +  CREATE INDEX xycd ON "x y"("c d");
          245  +
          246  +  INSERT INTO "x y" VALUES('a', 'a');
          247  +  INSERT INTO "x y" VALUES('b', 'b');
          248  +  INSERT INTO "x y" VALUES('c', 'c');
          249  +  INSERT INTO "x y" VALUES('d', 'd');
          250  +  INSERT INTO "x y" VALUES('e', 'a');
          251  +  INSERT INTO "x y" VALUES('f', 'b');
          252  +  INSERT INTO "x y" VALUES('g', 'c');
          253  +  INSERT INTO "x y" VALUES('h', 'd');
          254  +}
          255  +
          256  +do_execsql_test 5.1 {
          257  +  BEGIN;
          258  +    DELETE FROM "x y" WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
          259  +    SELECT * FROM "x y" ORDER BY 1;
          260  +  ROLLBACK;
          261  +} {
          262  +  a a c c d d e a g c h d
          263  +}
          264  +
          265  +do_execsql_test 5.2 {
          266  +  BEGIN;
          267  +    UPDATE "x y" SET "c d"='e' WHERE "c d"!='e' ORDER BY "c d" LIMIT 2 OFFSET 2;
          268  +    SELECT * FROM "x y" ORDER BY 1;
          269  +  ROLLBACK;
          270  +} {
          271  +  a a b e c c d d e a f e g c h d
          272  +}
          273  +
          274  +proc log {args} { lappend ::log {*}$args }
          275  +db func log log
          276  +do_execsql_test 5.3 {
          277  +  CREATE VIEW "v w" AS SELECT * FROM "x y";
          278  +  CREATE TRIGGER tr1 INSTEAD OF DELETE ON "v w" BEGIN
          279  +    SELECT log(old."a b", old."c d");
          280  +  END;
          281  +  CREATE TRIGGER tr2 INSTEAD OF UPDATE ON "v w" BEGIN
          282  +    SELECT log(new."a b", new."c d");
          283  +  END;
          284  +}
          285  +
          286  +do_test 5.4 {
          287  +  set ::log {}
          288  +  execsql { DELETE FROM "v w" ORDER BY "a b" LIMIT 3 }
          289  +  set ::log
          290  +} {a a b b c c}
   242    291   
          292  +do_test 5.5 {
          293  +  set ::log {}
          294  +  execsql { UPDATE "v w" SET "a b" = "a b" || 'x' ORDER BY "a b" LIMIT 5; }
          295  +  set ::log
          296  +} {ax a bx b cx c dx d ex a}
   243    297   
   244    298   
   245    299   finish_test
   246    300