/ Check-in [62fe56b5]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

   592    592       ** column in the FROM clause.  This is used by the LIMIT and ORDER BY
   593    593       ** clause processing on UPDATE and DELETE statements.
   594    594       */
   595    595       case TK_ROW: {
   596    596         SrcList *pSrcList = pNC->pSrcList;
   597    597         struct SrcList_item *pItem;
   598    598         assert( pSrcList && pSrcList->nSrc==1 );
   599         -      pItem = pSrcList->a; 
          599  +      pItem = pSrcList->a;
          600  +      if( !HasRowid(pItem->pTab) || pItem->pTab->pSelect!=0 ){
          601  +         sqlite3ErrorMsg(pParse, "ORDER BY and LIMIT not support for table %s",
          602  +                                 pItem->pTab->zName);
          603  +      }
   600    604         pExpr->op = TK_COLUMN;
   601    605         pExpr->pTab = pItem->pTab;
   602    606         pExpr->iTable = pItem->iCursor;
   603    607         pExpr->iColumn = -1;
   604    608         pExpr->affinity = SQLITE_AFF_INTEGER;
   605    609         break;
   606    610       }

Changes to test/wherelimit.test.

   274    274       execsql {UPDATE t1 SET y=1 WHERE x=2 ORDER BY x LIMIT 30, 50}
   275    275       execsql {SELECT count(*) FROM t1 WHERE y=1}
   276    276     } {6}
   277    277     do_test wherelimit-3.13 {
   278    278       execsql {UPDATE t1 SET y=1 WHERE x=3 ORDER BY x LIMIT 50 OFFSET 50}
   279    279       execsql {SELECT count(*) FROM t1 WHERE y=1}
   280    280     } {6}
          281  +
          282  +  # Cannot use a LIMIT for UPDATE or DELETE against a WITHOUT ROWID table
          283  +  # or a VIEW.  (We should fix this someday).
          284  +  #
          285  +  db close
          286  +  sqlite3 db :memory:
          287  +  do_execsql_test wherelimit-4.1 {
          288  +    CREATE TABLE t1(a int);
          289  +    INSERT INTO t1 VALUES(1);
          290  +    INSERT INTO t1 VALUES(2);
          291  +    INSERT INTO t1 VALUES(3);
          292  +    CREATE TABLE t2(a int);
          293  +    INSERT INTO t2 SELECT a+100 FROM t1;
          294  +    CREATE VIEW tv(r,a) AS
          295  +       SELECT rowid, a FROM t2 UNION ALL SELECT rowid, a FROM t1;
          296  +    CREATE TRIGGER tv_del INSTEAD OF DELETE ON tv
          297  +    BEGIN
          298  +      DELETE FROM t1 WHERE rowid=old.r;
          299  +      DELETE FROM t2 WHERE rowid=old.r;
          300  +    END;
          301  +  } {}
          302  +  do_catchsql_test wherelimit-4.2 {
          303  +    DELETE FROM tv WHERE 1 LIMIT 2;
          304  +  } {1 {ORDER BY and LIMIT not support for table tv}}
          305  +  do_catchsql_test wherelimit-4.3 {
          306  +    DELETE FROM tv WHERE 1 ORDER BY a LIMIT 2;
          307  +  } {1 {ORDER BY and LIMIT not support for table tv}}
          308  +  do_execsql_test wherelimit-4.10 {
          309  +    CREATE TABLE t3(a,b,c,d TEXT, PRIMARY KEY(a,b)) WITHOUT ROWID;
          310  +    INSERT INTO t3(a,b,c,d) VALUES(1,2,3,4),(5,6,7,8),(9,10,11,12);
          311  +  } {}
          312  +  do_catchsql_test wherelimit-4.11 {
          313  +    DELETE FROM t3 WHERE a=5 LIMIT 2;
          314  +  } {1 {ORDER BY and LIMIT not support for table t3}}
          315  +  do_execsql_test wherelimit-4.12 {
          316  +    SELECT a,b,c,d FROM t3 ORDER BY 1;
          317  +  } {1 2 3 4 5 6 7 8 9 10 11 12}
   281    318   
   282    319   }
   283    320   
   284    321   finish_test