/ Check-in [62fe56b5]
Login

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: 62fe56b59270d9d7372b1bb8a53788a40d20d0f111fe38c61dd6269848592c70
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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