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 | branch-3.8.9 |
| Files: | files | file ages | folders |
| SHA3-256: |
30aa941fc16c1affe3821ff2d9a4955e |
| User & Date: | drh 2017-11-09 04:13:54 |
Context
|
2017-11-14
| ||
| 18:26 | Fix the SQLITE_ENABLE_UPDATE_DELETE_LIMIT functionality so that it works with views and WITHOUT ROWID tables. This is a modified cherrypick of [dae4a97a]. check-in: b2679d3b user: dan tags: branch-3.8.9 | |
|
2017-11-09
| ||
| 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-07-27
| ||
| 22:16 | Enhance the like optimization so that it works with an ESCAPE clause. check-in: 2495acf7 user: drh tags: branch-3.8.9 | |
Changes
Changes to src/resolve.c.
631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 |
** 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;
}
|
| > > > > |
631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 |
** 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 AS
SELECT rowid AS r, 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
|