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: |
35477a3dcceadf5dade8e036d5a2ce91 |
| 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
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