SQLite

Check-in [10ca7357b2]
Login

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

Overview
Comment:Add simple tests and bugfixes for DELETE statements that qualify for the OR-optimization.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | onepass-delete-or
Files: files | file ages | folders
SHA1: 10ca7357b27d0657127d553738be3ae6d715b74a
User & Date: dan 2015-12-09 10:06:24.300
Context
2015-12-09
16:08
Fix a problem with the DROP TABLE command on this branch. (check-in: c80bbf14b3 user: dan tags: onepass-delete-or)
10:06
Add simple tests and bugfixes for DELETE statements that qualify for the OR-optimization. (check-in: 10ca7357b2 user: dan tags: onepass-delete-or)
08:13
Merge latest trunk with this branch. (check-in: dc236f1118 user: dan tags: onepass-delete-or)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/delete.c.
︙︙
210
211
212
213
214
215
216

217

218
219
220
221
222
223
224
225
226
227




/*
** sqlite3WalkExpr() callback used by deleteSetColUsed().
*/
static int deleteSetColUsedExpr(Walker *pWalker, Expr *pExpr){

  if( pExpr->op==TK_COLUMN ){

    int i = pExpr->iColumn;
    if( i>=0 ){
      pWalker->u.pSrcList->a[0].colUsed |= ((Bitmask)1)<<(i>=BMS ? BMS-1 : i);
    }
  }
  return WRC_Continue;
}

/*
** No-op sqlite3WalkExpr() callback used by deleteSetColUsed().








>
|
>
|
<
|








210
211
212
213
214
215
216
217
218
219
220

221
222
223
224
225
226
227
228




/*
** sqlite3WalkExpr() callback used by deleteSetColUsed().
*/
static int deleteSetColUsedExpr(Walker *pWalker, Expr *pExpr){
  int iCol;
  if( pExpr->op==TK_COLUMN && (iCol = pExpr->iColumn)>=0 ){
    struct SrcList_item *pItem = &pWalker->u.pSrcList->a[0];
    if( pItem->iCursor==pExpr->iTable ){

      pItem->colUsed |= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
    }
  }
  return WRC_Continue;
}

/*
** No-op sqlite3WalkExpr() callback used by deleteSetColUsed().
︙︙
Changes to src/where.c.
︙︙
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829

**
** If an OOM error occurs, NULL is returned. In this case the mallocFailed
** field of the database handle (pWInfo->pParse->db->mallocFailed) is set 
** to record the error.
*/
Expr *sqlite3WhereSplitExpr(WhereInfo *pWInfo, int iExpr){
  sqlite3 *db = pWInfo->pParse->db;
  WhereLoop *pLoop = pWInfo->pLoops;
  WhereTerm *pTerm = pLoop->aLTerm[0];
  WhereClause *pOrWC = &pTerm->u.pOrInfo->wc;
  Expr *pExpr = 0;

  assert( pWInfo->eOnePass==ONEPASS_SPLIT_DELETE );
  assert( pLoop->pNextLoop==0 );
  assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );

  if( iExpr<pOrWC->nTerm ){
    pExpr = sqlite3ExprDup(db, pOrWC->a[iExpr].pExpr, 0);
    if( pExpr ){
      WhereClause *pWC = &pWInfo->sWC;
      int ii;








|





<








1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821

1822
1823
1824
1825
1826
1827
1828

**
** If an OOM error occurs, NULL is returned. In this case the mallocFailed
** field of the database handle (pWInfo->pParse->db->mallocFailed) is set 
** to record the error.
*/
Expr *sqlite3WhereSplitExpr(WhereInfo *pWInfo, int iExpr){
  sqlite3 *db = pWInfo->pParse->db;
  WhereLoop *pLoop = pWInfo->a[0].pWLoop;
  WhereTerm *pTerm = pLoop->aLTerm[0];
  WhereClause *pOrWC = &pTerm->u.pOrInfo->wc;
  Expr *pExpr = 0;

  assert( pWInfo->eOnePass==ONEPASS_SPLIT_DELETE );

  assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );

  if( iExpr<pOrWC->nTerm ){
    pExpr = sqlite3ExprDup(db, pOrWC->a[iExpr].pExpr, 0);
    if( pExpr ){
      WhereClause *pWC = &pWInfo->sWC;
      int ii;
︙︙
Changes to test/fordelete.test.
︙︙
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77































78
79
80
81
82
83
84

  2 { DELETE FROM t1 WHERE a=? AND b=? } { sqlite_autoindex_t1_1  t1 }
  3 { DELETE FROM t1 WHERE a>? }         { sqlite_autoindex_t1_1  t1* }
  4 { DELETE FROM t1 WHERE rowid=? }     { sqlite_autoindex_t1_1*  t1 }
} {
  do_adp_test 1.$tn $sql $res
}

do_execsql_test 2.0 {
  CREATE TABLE t2(a, b, c);
  CREATE INDEX t2a ON t2(a);
  CREATE INDEX t2b ON t2(b);
  CREATE INDEX t2c ON t2(c);
}
foreach {tn sql res} {
  1 { DELETE FROM t2 WHERE a=?}          { t2* t2a t2b* t2c* }
  2 { DELETE FROM t2 WHERE a=? AND +b=?} { t2 t2a t2b* t2c* }
  3 { DELETE FROM t2 WHERE a=? OR b=?}   { t2* t2* t2a t2a* t2b t2b* t2c* t2c* }
  4 { DELETE FROM t2 WHERE +a=? }        { t2 t2a* t2b* t2c* }
  5 { DELETE FROM t2 WHERE rowid=? }     { t2 t2a* t2b* t2c* }
} {
  do_adp_test 2.$tn $sql $res































}

#-------------------------------------------------------------------------
# Test that a record that consists of the bytes:
#
#   0x01 0x00
#








|












|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

  2 { DELETE FROM t1 WHERE a=? AND b=? } { sqlite_autoindex_t1_1  t1 }
  3 { DELETE FROM t1 WHERE a>? }         { sqlite_autoindex_t1_1  t1* }
  4 { DELETE FROM t1 WHERE rowid=? }     { sqlite_autoindex_t1_1*  t1 }
} {
  do_adp_test 1.$tn $sql $res
}

do_execsql_test 2.1.0 {
  CREATE TABLE t2(a, b, c);
  CREATE INDEX t2a ON t2(a);
  CREATE INDEX t2b ON t2(b);
  CREATE INDEX t2c ON t2(c);
}
foreach {tn sql res} {
  1 { DELETE FROM t2 WHERE a=?}          { t2* t2a t2b* t2c* }
  2 { DELETE FROM t2 WHERE a=? AND +b=?} { t2 t2a t2b* t2c* }
  3 { DELETE FROM t2 WHERE a=? OR b=?}   { t2* t2* t2a t2a* t2b t2b* t2c* t2c* }
  4 { DELETE FROM t2 WHERE +a=? }        { t2 t2a* t2b* t2c* }
  5 { DELETE FROM t2 WHERE rowid=? }     { t2 t2a* t2b* t2c* }
} {
  do_adp_test 2.1.$tn $sql $res
}

foreach {tn where res} {
  1 { a=? OR b=? }             { t2* t2* t2a t2a* t2b t2b* t2c* t2c* }
  2 { a=? OR (b=? AND +c=?) }  { t2 t2* t2a t2a* t2b t2b* t2c* t2c* }
  3 { (a=? OR b=?) AND +c=? }  { t2 t2 t2a t2a* t2b t2b* t2c* t2c* }
  4 { (c=? OR b=?) }           { t2* t2* t2a* t2a* t2b t2b* t2c t2c* }
  5 { (c=? OR b=?) AND EXISTS (SELECT 1 FROM t1 WHERE t1.a=?) }
                               { t2* t2* t2a* t2a* t2b t2b* t2c t2c* }
} {
  do_adp_test 2.2.$tn "DELETE FROM t2 WHERE $where" $res
}

do_execsql_test 2.3.0 {
  CREATE TABLE t3(x, y, z);
  CREATE INDEX t3xy ON t3(x, y);
  CREATE INDEX t3zy ON t3(z, y);
  CREATE INDEX t3zx ON t3(z, x);
  CREATE INDEX t3x ON t3(x);
}

foreach {tn where res} {
  1 { x=? OR z=? }          
    { t3* t3* t3x t3x* t3xy* t3xy* t3zx t3zx* t3zy* t3zy* }

  2 { (x=? OR z=?) AND y=? }   
    { t3* t3* t3x* t3x* t3xy t3xy* t3zx* t3zx* t3zy t3zy* }

  3 { (y=? OR z=?) AND x=? }   { t3 t3x t3xy* t3zx* t3zy* }
} {
  do_adp_test 2.3.$tn "DELETE FROM t3 WHERE $where" $res
}

#-------------------------------------------------------------------------
# Test that a record that consists of the bytes:
#
#   0x01 0x00
#
︙︙