Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Disallow ORDER BY and LIMIT on UPDATE and DELETE of views and WITHOUT ROWID tables. This is a temporary fix for ticket [d4beea1633f1b88f] until a better solution can be found. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
62fe56b59270d9d7372b1bb8a53788a4 |
| User & Date: | drh 2017-11-09 03:55:09 |
Context
|
2017-11-10
| ||
| 12:41 | Fix harmless compiler warning seen with MSVC. check-in: 3711ef23 user: mistachkin tags: trunk | |
|
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 | |
| 04:13 | Disallow ORDER BY and LIMIT on UPDATE and DELETE of views and WITHOUT ROWID tables. This is a temporary fix for ticket [d4beea1633f1b88f] until a better solution can be found. check-in: 30aa941f user: drh tags: branch-3.8.9 | |
| 03:55 | Disallow ORDER BY and LIMIT on UPDATE and DELETE of views and WITHOUT ROWID tables. This is a temporary fix for ticket [d4beea1633f1b88f] until a better solution can be found. check-in: 62fe56b5 user: drh tags: trunk | |
|
2017-11-08
| ||
| 11:14 | Fix a problem causing LSM to add unnecessary padding to empty segments in compressed databases. check-in: 1bc2d046 user: dan tags: trunk | |
Changes
Changes to src/resolve.c.
592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 |
** column in the FROM clause. This is used by the LIMIT and ORDER BY
** clause processing on UPDATE and DELETE statements.
*/
case TK_ROW: {
SrcList *pSrcList = pNC->pSrcList;
struct SrcList_item *pItem;
assert( pSrcList && pSrcList->nSrc==1 );
pItem = pSrcList->a;
pExpr->op = TK_COLUMN;
pExpr->pTab = pItem->pTab;
pExpr->iTable = pItem->iCursor;
pExpr->iColumn = -1;
pExpr->affinity = SQLITE_AFF_INTEGER;
break;
}
|
| > > > > |
592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 |
** column in the FROM clause. This is used by the LIMIT and ORDER BY
** clause processing on UPDATE and DELETE statements.
*/
case TK_ROW: {
SrcList *pSrcList = pNC->pSrcList;
struct SrcList_item *pItem;
assert( pSrcList && pSrcList->nSrc==1 );
pItem = pSrcList->a;
if( !HasRowid(pItem->pTab) || pItem->pTab->pSelect!=0 ){
sqlite3ErrorMsg(pParse, "ORDER BY and LIMIT not support for table %s",
pItem->pTab->zName);
}
pExpr->op = TK_COLUMN;
pExpr->pTab = pItem->pTab;
pExpr->iTable = pItem->iCursor;
pExpr->iColumn = -1;
pExpr->affinity = SQLITE_AFF_INTEGER;
break;
}
|
Changes to test/wherelimit.test.
274 275 276 277 278 279 280 281 282 283 284 |
execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50}
execsql {SELECT count(*) FROM t1 WHERE y=1}
} {6}
do_test wherelimit-3.13 {
execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
execsql {SELECT count(*) FROM t1 WHERE y=1}
} {6}
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | > |
274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 |
execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50}
execsql {SELECT count(*) FROM t1 WHERE y=1}
} {6}
do_test wherelimit-3.13 {
execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
execsql {SELECT count(*) FROM t1 WHERE y=1}
} {6}
# Cannot use a LIMIT for UPDATE or DELETE against a WITHOUT ROWID table
# or a VIEW. (We should fix this someday).
#
db close
sqlite3 db :memory:
do_execsql_test wherelimit-4.1 {
CREATE TABLE t1(a int);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
INSERT INTO t1 VALUES(3);
CREATE TABLE t2(a int);
INSERT INTO t2 SELECT a+100 FROM t1;
CREATE VIEW tv(r,a) AS
SELECT rowid, a FROM t2 UNION ALL SELECT rowid, a FROM t1;
CREATE TRIGGER tv_del INSTEAD OF DELETE ON tv
BEGIN
DELETE FROM t1 WHERE rowid=old.r;
DELETE FROM t2 WHERE rowid=old.r;
END;
} {}
do_catchsql_test wherelimit-4.2 {
DELETE FROM tv WHERE 1 LIMIT 2;
} {1 {ORDER BY and LIMIT not support for table tv}}
do_catchsql_test wherelimit-4.3 {
DELETE FROM tv WHERE 1 ORDER BY a LIMIT 2;
} {1 {ORDER BY and LIMIT not support for table tv}}
do_execsql_test wherelimit-4.10 {
CREATE TABLE t3(a,b,c,d TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4),(5,6,7,8),(9,10,11,12);
} {}
do_catchsql_test wherelimit-4.11 {
DELETE FROM t3 WHERE a=5 LIMIT 2;
} {1 {ORDER BY and LIMIT not support for table t3}}
do_execsql_test wherelimit-4.12 {
SELECT a,b,c,d FROM t3 ORDER BY 1;
} {1 2 3 4 5 6 7 8 9 10 11 12}
}
finish_test
|