/ Check-in [35477a3d]
Login

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

Overview
Comment:Fix a problem with (DELETE...LIMIT) statements against WITHOUT ROWID tables with a single column PK.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | update-delete-limit-fix
Files: files | file ages | folders
SHA3-256: 35477a3dcceadf5dade8e036d5a2ce91b9ca83c4b85d309db233bdbcf538b1cc
User & Date: dan 2017-11-10 15:42:21
Context
2017-11-10
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
15:42
Fix a problem with (DELETE...LIMIT) statements against WITHOUT ROWID tables with a single column PK. check-in: 35477a3d user: dan tags: update-delete-limit-fix
2017-11-09
19:53
Add SQLITE_ENABLE_UPDATE_DELETE_LIMIT for views and WITHOUT ROWID tables. check-in: 584b88aa user: dan tags: update-delete-limit-fix
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   177    177       pLhs = sqlite3PExpr(pParse, TK_ROW, 0, 0);
   178    178       pEList = sqlite3ExprListAppend(
   179    179           pParse, 0, sqlite3PExpr(pParse, TK_ROW, 0, 0)
   180    180       );
   181    181     }else{
   182    182       Index *pPk = sqlite3PrimaryKeyIndex(pTab);
   183    183       if( pPk->nKeyCol==1 ){
   184         -      pLhs = sqlite3Expr(db, TK_ID, pTab->aCol[pPk->aiColumn[0]].zName);
          184  +      const char *zName = pTab->aCol[pPk->aiColumn[0]].zName;
          185  +      pLhs = sqlite3Expr(db, TK_ID, zName);
          186  +      pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, zName));
   185    187       }else{
   186    188         int i;
   187    189         for(i=0; i<pPk->nKeyCol; i++){
   188    190           Expr *p = sqlite3Expr(db, TK_ID, pTab->aCol[pPk->aiColumn[i]].zName);
   189    191           pEList = sqlite3ExprListAppend(pParse, pEList, p);
   190    192         }
   191    193         pLhs = sqlite3PExpr(pParse, TK_VECTOR, 0, 0);

Changes to src/parse.y.

   774    774   //
   775    775   %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   776    776   cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   777    777           where_opt(W) orderby_opt(O) limit_opt(L).  {
   778    778     sqlite3WithPush(pParse, C, 1);
   779    779     sqlite3SrcListIndexedBy(pParse, X, &I);
   780    780     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   781         -#if 0
   782         -  W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE");
   783         -#endif
   784    781     sqlite3UpdateLimit(pParse,X,Y,W,R,O,L.pLimit,L.pOffset);
   785    782   }
   786    783   %endif
   787    784   %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   788    785   cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   789    786           where_opt(W).  {
   790    787     sqlite3WithPush(pParse, C, 1);

Changes to test/wherelimit2.test.

    18     18   set testprefix wherelimit2
    19     19   
    20     20   ifcapable !update_delete_limit {
    21     21     finish_test
    22     22     return
    23     23   }
    24     24   
           25  +#-------------------------------------------------------------------------
           26  +# Test with views and INSTEAD OF triggers.
           27  +#
    25     28   do_execsql_test 1.0 {
    26     29     CREATE TABLE t1(a, b);
    27     30     INSERT INTO t1 VALUES(1, 'f');
    28     31     INSERT INTO t1 VALUES(2, 'e');
    29     32     INSERT INTO t1 VALUES(3, 'd');
    30     33     INSERT INTO t1 VALUES(4, 'c');
    31     34     INSERT INTO t1 VALUES(5, 'b');
................................................................................
    64     67   do_execsql_test 1.4 {
    65     68     UPDATE v1 SET b = 555 ORDER BY b LIMIT 3;
    66     69     SELECT * FROM log; DELETE FROM log;
    67     70   } {
    68     71     update 6 update 5 update 4
    69     72   }
    70     73   
           74  +#-------------------------------------------------------------------------
           75  +# Simple test using WITHOUT ROWID table.
           76  +#
    71     77   do_execsql_test 2.0 {
    72     78     CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;
    73     79     INSERT INTO t2 VALUES(1, 1, 'h');
    74     80     INSERT INTO t2 VALUES(1, 2, 'g');
    75     81     INSERT INTO t2 VALUES(2, 1, 'f');
    76     82     INSERT INTO t2 VALUES(2, 2, 'e');
    77     83     INSERT INTO t2 VALUES(3, 1, 'd');
................................................................................
    98    104     2 1 {} 
    99    105     2 2 {} 
   100    106     3 1 d 
   101    107     3 2 c 
   102    108     4 1 b 
   103    109     4 2 a
   104    110   }
          111  +
          112  +#-------------------------------------------------------------------------
          113  +# Test using a virtual table
          114  +#
          115  +ifcapable fts5 {
          116  +  do_execsql_test 3.0 {
          117  +    CREATE VIRTUAL TABLE ft USING fts5(x);
          118  +    INSERT INTO ft(rowid, x) VALUES(-45,   'a a');
          119  +    INSERT INTO ft(rowid, x) VALUES(12,    'a b');
          120  +    INSERT INTO ft(rowid, x) VALUES(444,   'a c');
          121  +    INSERT INTO ft(rowid, x) VALUES(12300, 'a d');
          122  +    INSERT INTO ft(rowid, x) VALUES(25400, 'a c');
          123  +    INSERT INTO ft(rowid, x) VALUES(25401, 'a b');
          124  +    INSERT INTO ft(rowid, x) VALUES(50000, 'a a');
          125  +  }
          126  +
          127  +  do_execsql_test 3.1.1 {
          128  +    BEGIN;
          129  +      DELETE FROM ft ORDER BY rowid LIMIT 3;
          130  +      SELECT x FROM ft;
          131  +    ROLLBACK;
          132  +  } {{a d} {a c} {a b} {a a}}
          133  +
          134  +  do_execsql_test 3.1.2 {
          135  +    BEGIN;
          136  +      DELETE FROM ft WHERE ft MATCH 'a' ORDER BY rowid LIMIT 3;
          137  +      SELECT x FROM ft;
          138  +    ROLLBACK;
          139  +  } {{a d} {a c} {a b} {a a}}
          140  +  
          141  +  do_execsql_test 3.1.3 {
          142  +    BEGIN;
          143  +      DELETE FROM ft WHERE ft MATCH 'b' ORDER BY rowid ASC LIMIT 1 OFFSET 1;
          144  +      SELECT rowid FROM ft;
          145  +    ROLLBACK;
          146  +  } {-45 12 444 12300 25400 50000}
          147  +
          148  +  do_execsql_test 3.2.1 {
          149  +    BEGIN;
          150  +      UPDATE ft SET x='hello' ORDER BY rowid LIMIT 2 OFFSET 2;
          151  +      SELECT x FROM ft;
          152  +    ROLLBACK;
          153  +  } {{a a} {a b} hello hello {a c} {a b} {a a}}
          154  +
          155  +  do_execsql_test 3.2.2 {
          156  +    BEGIN;
          157  +      UPDATE ft SET x='hello' WHERE ft MATCH 'a' 
          158  +          ORDER BY rowid DESC LIMIT 2 OFFSET 2;
          159  +      SELECT x FROM ft;
          160  +    ROLLBACK;
          161  +  } {{a a} {a b} {a c} hello hello {a b} {a a}}
          162  +} ;# fts5
          163  +
          164  +#-------------------------------------------------------------------------
          165  +# Test using INDEXED BY clauses.
          166  +#
          167  +foreach {tn t} {1 "" 2 "WITHOUT ROWID"} {
          168  +  execsql "DROP TABLE IF EXISTS x1"
          169  +  execsql "CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c, d) $t"
          170  +  do_execsql_test 4.$tn.0 {
          171  +    CREATE INDEX x1bc ON x1(b, c);
          172  +    INSERT INTO x1 VALUES(1,1,1,1);
          173  +    INSERT INTO x1 VALUES(2,1,2,2);
          174  +    INSERT INTO x1 VALUES(3,2,1,3);
          175  +    INSERT INTO x1 VALUES(4,2,2,3);
          176  +    INSERT INTO x1 VALUES(5,3,1,2);
          177  +    INSERT INTO x1 VALUES(6,3,2,1);
          178  +  }
          179  +
          180  +  do_execsql_test 4.$tn.1 {
          181  +    BEGIN;
          182  +      DELETE FROM x1 ORDER BY a LIMIT 2;
          183  +      SELECT a FROM x1;
          184  +    ROLLBACK;
          185  +  } {3 4 5 6}
          186  +}
          187  +
          188  +
          189  +
          190  +
          191  +#-------------------------------------------------------------------------
          192  +# Test using object names that require quoting.
          193  +#
   105    194   
   106    195   
   107    196   
   108    197   finish_test
   109    198