SQLite: Check-in [3f221f59]

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

Changes to src/delete.c.

  int iKey;              /* Memory cell holding key of row to be deleted */
  i16 nKey;              /* Number of memory cells in the row key */
  int iEphCur = 0;       /* Ephemeral table holding all primary key values */
  int iRowSet = 0;       /* Register for rowset of rows to delete */
  int addrBypass = 0;    /* Address of jump over the delete logic */
  int addrLoop = 0;      /* Top of the delete loop */
  int addrEphOpen = 0;   /* Instruction to open the Ephemeral table */


 
#ifndef SQLITE_OMIT_TRIGGER
  int isView;                  /* True if attempting to delete from a view */
  Trigger *pTrigger;           /* List of table triggers, if required */
  int bComplex;                /* True if there are either triggers or FKs */
#endif

  memset(&sContext, 0, sizeof(sContext));
  db = pParse->db;
  if( pParse->nErr || db->mallocFailed ){
    goto delete_from_cleanup;
  }
................................................................................
#ifndef SQLITE_OMIT_TRIGGER
  pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
  isView = pTab->pSelect!=0;
  bComplex = pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0);
#else
# define pTrigger 0
# define isView 0
# define bComplex 0
#endif
#ifdef SQLITE_OMIT_VIEW
# undef isView
# define isView 0
#endif

  /* If pTab is really a view, make sure it has been initialized.
................................................................................
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
    }
  }else
#endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
  {
    u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK;

    wcf |= (bComplex ? 0 : WHERE_ONEPASS_MULTIROW);
    if( HasRowid(pTab) ){
      /* For a rowid table, initialize the RowSet to an empty set */
      pPk = 0;
      nPk = 1;
      iRowSet = ++pParse->nMem;
      sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);

  int iKey;              /* Memory cell holding key of row to be deleted */
  i16 nKey;              /* Number of memory cells in the row key */
  int iEphCur = 0;       /* Ephemeral table holding all primary key values */
  int iRowSet = 0;       /* Register for rowset of rows to delete */
  int addrBypass = 0;    /* Address of jump over the delete logic */
  int addrLoop = 0;      /* Top of the delete loop */
  int addrEphOpen = 0;   /* Instruction to open the Ephemeral table */
  int bComplex;          /* True if there are triggers or FKs or or
                         ** subqueries in the WHERE clause */
 
#ifndef SQLITE_OMIT_TRIGGER
  int isView;                  /* True if attempting to delete from a view */
  Trigger *pTrigger;           /* List of table triggers, if required */

#endif

  memset(&sContext, 0, sizeof(sContext));
  db = pParse->db;
  if( pParse->nErr || db->mallocFailed ){
    goto delete_from_cleanup;
  }
................................................................................
#ifndef SQLITE_OMIT_TRIGGER
  pTrigger = sqlite3TriggersExist(pParse, pTab, TK_DELETE, 0, 0);
  isView = pTab->pSelect!=0;
  bComplex = pTrigger || sqlite3FkRequired(pParse, pTab, 0, 0);
#else
# define pTrigger 0
# define isView 0

#endif
#ifdef SQLITE_OMIT_VIEW
# undef isView
# define isView 0
#endif

  /* If pTab is really a view, make sure it has been initialized.
................................................................................
      assert( pIdx->pSchema==pTab->pSchema );
      sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
    }
  }else
#endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
  {
    u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK;
    if( pWhere && ExprHasProperty(pWhere, EP_Subquery) ) bComplex = 1;
    wcf |= (bComplex ? 0 : WHERE_ONEPASS_MULTIROW);
    if( HasRowid(pTab) ){
      /* For a rowid table, initialize the RowSet to an empty set */
      pPk = 0;
      nPk = 1;
      iRowSet = ++pParse->nMem;
      sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);

Changes to test/delete4.test.

  INSERT INTO t1(a,b,c) SELECT a+2, 1, c FROM t1;
  INSERT INTO t1(a,b,c) SELECT a+10, 2, c FROM t1 WHERE b=1;
  INSERT INTO t1(a,b,c) SELECT a+20, 3, c FROM t1 WHERE b=1;
  PRAGMA reverse_unordered_selects = ON;
  DELETE FROM t1 WHERE b=2;
  SELECT a FROM t1 WHERE b=2;
} {}
























finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

  INSERT INTO t1(a,b,c) SELECT a+2, 1, c FROM t1;
  INSERT INTO t1(a,b,c) SELECT a+10, 2, c FROM t1 WHERE b=1;
  INSERT INTO t1(a,b,c) SELECT a+20, 3, c FROM t1 WHERE b=1;
  PRAGMA reverse_unordered_selects = ON;
  DELETE FROM t1 WHERE b=2;
  SELECT a FROM t1 WHERE b=2;
} {}

# 2016-05-02
# Ticket https://www.sqlite.org/src/tktview/dc6ebeda93960877
# A subquery in the WHERE clause of a one-pass DELETE can cause an
# incorrect answer.
#
db close
forcedelete test.db
sqlite3 db test.db
do_execsql_test 6.0 {
  CREATE TABLE t2(x INT);
  INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);
  DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x-1);
  SELECT x FROM t2;
} {1}
do_execsql_test 6.1 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(x INT);
  INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);
  DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x+1);
  SELECT x FROM t2;
} {5}


finish_test