/ Check-in [3f221f59]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Disable the multi-row one-pass DELETE optimization when the WHERE clause contains a subquery. Fix for ticket [dc6ebeda9396087].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3f221f592a9a19009076e568566c59801cd3fc32
User & Date: drh 2016-05-02 12:18:56
References
2016-05-04
14:45
Only disable the one-pass DELETE optimization if the WHERE clause contains a correlated subquery. Uncorrelated subqueries are allowed. This is a refinement of check-in [3f221f592a9a1] that is the fix for ticket [dc6ebeda9396087]. check-in: aae38969 user: drh tags: trunk
Context
2016-05-02
13:57
Fix harmless LLVM compiler warnings in the srcck1.c utility program used during the build process. check-in: e4af9675 user: drh tags: trunk
12:18
Disable the multi-row one-pass DELETE optimization when the WHERE clause contains a subquery. Fix for ticket [dc6ebeda9396087]. check-in: 3f221f59 user: drh tags: trunk
10:25
Improvements to the way the LIKE optimization is implemented, resulting in slightly smaller and faster code that is easier to test. check-in: 54c63b32 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/delete.c.

   235    235     int iKey;              /* Memory cell holding key of row to be deleted */
   236    236     i16 nKey;              /* Number of memory cells in the row key */
   237    237     int iEphCur = 0;       /* Ephemeral table holding all primary key values */
   238    238     int iRowSet = 0;       /* Register for rowset of rows to delete */
   239    239     int addrBypass = 0;    /* Address of jump over the delete logic */
   240    240     int addrLoop = 0;      /* Top of the delete loop */
   241    241     int addrEphOpen = 0;   /* Instruction to open the Ephemeral table */
          242  +  int bComplex;          /* True if there are triggers or FKs or or
          243  +                         ** subqueries in the WHERE clause */
   242    244    
   243    245   #ifndef SQLITE_OMIT_TRIGGER
   244    246     int isView;                  /* True if attempting to delete from a view */
   245    247     Trigger *pTrigger;           /* List of table triggers, if required */
   246         -  int bComplex;                /* True if there are either triggers or FKs */
   247    248   #endif
   248    249   
   249    250     memset(&sContext, 0, sizeof(sContext));
   250    251     db = pParse->db;
   251    252     if( pParse->nErr || db->mallocFailed ){
   252    253       goto delete_from_cleanup;
   253    254     }
................................................................................
   267    268   #ifndef SQLITE_OMIT_TRIGGER
   268    269     pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
   269    270     isView = pTab->pSelect!=0;
   270    271     bComplex = pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0);
   271    272   #else
   272    273   # define pTrigger 0
   273    274   # define isView 0
   274         -# define bComplex 0
   275    275   #endif
   276    276   #ifdef SQLITE_OMIT_VIEW
   277    277   # undef isView
   278    278   # define isView 0
   279    279   #endif
   280    280   
   281    281     /* If pTab is really a view, make sure it has been initialized.
................................................................................
   370    370         assert( pIdx->pSchema==pTab->pSchema );
   371    371         sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
   372    372       }
   373    373     }else
   374    374   #endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
   375    375     {
   376    376       u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK;
          377  +    if( pWhere && ExprHasProperty(pWhere, EP_Subquery) ) bComplex = 1;
   377    378       wcf |= (bComplex ? 0 : WHERE_ONEPASS_MULTIROW);
   378    379       if( HasRowid(pTab) ){
   379    380         /* For a rowid table, initialize the RowSet to an empty set */
   380    381         pPk = 0;
   381    382         nPk = 1;
   382    383         iRowSet = ++pParse->nMem;
   383    384         sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);

Changes to test/delete4.test.

   156    156     INSERT INTO t1(a,b,c) SELECT a+2, 1, c FROM t1;
   157    157     INSERT INTO t1(a,b,c) SELECT a+10, 2, c FROM t1 WHERE b=1;
   158    158     INSERT INTO t1(a,b,c) SELECT a+20, 3, c FROM t1 WHERE b=1;
   159    159     PRAGMA reverse_unordered_selects = ON;
   160    160     DELETE FROM t1 WHERE b=2;
   161    161     SELECT a FROM t1 WHERE b=2;
   162    162   } {}
          163  +
          164  +# 2016-05-02
          165  +# Ticket https://www.sqlite.org/src/tktview/dc6ebeda93960877
          166  +# A subquery in the WHERE clause of a one-pass DELETE can cause an
          167  +# incorrect answer.
          168  +#
          169  +db close
          170  +forcedelete test.db
          171  +sqlite3 db test.db
          172  +do_execsql_test 6.0 {
          173  +  CREATE TABLE t2(x INT);
          174  +  INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);
          175  +  DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x-1);
          176  +  SELECT x FROM t2;
          177  +} {1}
          178  +do_execsql_test 6.1 {
          179  +  DROP TABLE IF EXISTS t2;
          180  +  CREATE TABLE t2(x INT);
          181  +  INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);
          182  +  DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x+1);
          183  +  SELECT x FROM t2;
          184  +} {5}
          185  +
   163    186   
   164    187   finish_test