SQLite

Changes On Branch factor-out-scalar-subselect
Login

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

Changes In Branch factor-out-scalar-subselect Excluding Merge-Ins

This is equivalent to a diff from 0f1b9ff9 to 44e1b55a

2018-12-31
18:30
Merge enhancements and bug fixes from trunk. (check-in: 9fb646f2 user: drh tags: reuse-subqueries)
2018-12-28
21:40
Merge performance enhancements and other patches. (Leaf check-in: 44e1b55a user: drh tags: factor-out-scalar-subselect)
21:32
Merge performance enhancements from trunk. (check-in: 0f1b9ff9 user: drh tags: reuse-subqueries)
20:48
Faster allocation of new sqlite3_stmt objects. (check-in: 891f1f72 user: drh tags: trunk)
19:23
Merge patches and enhancements from trunk. (check-in: da53f3d3 user: drh tags: reuse-subqueries)
2018-12-26
15:04
Here is the beginnings of an effort to factor out the implementation of scalar subqueries to be implemented as subroutines at the end of the main body of bytecode, after the jump back to the OP_Init opcode. This is an incremental check-in that contains only preliminary changes. (check-in: 50e5f390 user: drh tags: factor-out-scalar-subselect)

Changes to src/build.c.

164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
    /* The cookie mask contains one bit for each database file open.
    ** (Bit 0 is for main, bit 1 is for temp, and so forth.)  Bits are
    ** set for each database that is used.  Generate code to start a
    ** transaction on each used database and to verify the schema cookie
    ** on each used database.
    */
    if( db->mallocFailed==0 
     && (DbMaskNonZero(pParse->cookieMask) || pParse->pConstExpr)
    ){
      int iDb, i;
      assert( sqlite3VdbeGetOp(v, 0)->opcode==OP_Init );
      sqlite3VdbeJumpHere(v, 0);
      for(iDb=0; iDb<db->nDb; iDb++){
        Schema *pSchema;
        if( DbMaskTest(pParse->cookieMask, iDb)==0 ) continue;







|







164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
    /* The cookie mask contains one bit for each database file open.
    ** (Bit 0 is for main, bit 1 is for temp, and so forth.)  Bits are
    ** set for each database that is used.  Generate code to start a
    ** transaction on each used database and to verify the schema cookie
    ** on each used database.
    */
    if( db->mallocFailed==0 
     && (DbMaskNonZero(pParse->cookieMask) || pParse->pAuxExpr)
    ){
      int iDb, i;
      assert( sqlite3VdbeGetOp(v, 0)->opcode==OP_Init );
      sqlite3VdbeJumpHere(v, 0);
      for(iDb=0; iDb<db->nDb; iDb++){
        Schema *pSchema;
        if( DbMaskTest(pParse->cookieMask, iDb)==0 ) continue;
203
204
205
206
207
208
209
210
211
212

213

214

215
216





217
218
219
220

221
222
223
224
225
226
227
      */
      codeTableLocks(pParse);

      /* Initialize any AUTOINCREMENT data structures required.
      */
      sqlite3AutoincrementBegin(pParse);

      /* Code constant expressions that where factored out of inner loops */
      if( pParse->pConstExpr ){
        ExprList *pEL = pParse->pConstExpr;

        pParse->okConstFactor = 0;

        for(i=0; i<pEL->nExpr; i++){

          sqlite3ExprCode(pParse, pEL->a[i].pExpr, pEL->a[i].u.iConstExprReg);
        }





      }

      /* Finally, jump back to the beginning of the executable code. */
      sqlite3VdbeGoto(v, 1);

    }
  }


  /* Get the VDBE program ready for execution
  */
  if( v && pParse->nErr==0 && !db->mallocFailed ){







<
|
|
>

>
|
>
|

>
>
>
>
>
|
|
|
|
>







203
204
205
206
207
208
209

210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
      */
      codeTableLocks(pParse);

      /* Initialize any AUTOINCREMENT data structures required.
      */
      sqlite3AutoincrementBegin(pParse);


      if( pParse->pAuxExpr ){
        ExprList *pEL = pParse->pAuxExpr;
        struct ExprList_item *pX;
        pParse->okConstFactor = 0;
        /* Code constant expressions that where factored out of inner loops */
        for(pX=pEL->a, i=pEL->nExpr; i>0; i--, pX++){
          if( pX->bAuxSubrtn ) continue;
          sqlite3ExprCode(pParse, pX->pExpr, pX->u.iConstExprReg);
        }
        sqlite3VdbeGoto(v, 1);  /* Jump back to the main code */
        /* Code subroutines factored out from main */
        for(pX=pEL->a, i=pEL->nExpr; i>0; i--, pX++){
          if( !pX->bAuxSubrtn ) continue;
          /* sqlite3ExprCode(pParse, pX->pExpr, pX->u.iConstExprReg); */
        }
      }else{
        /* Jump back to the beginning of main */
        sqlite3VdbeGoto(v, 1);
      }
    }
  }


  /* Get the VDBE program ready for execution
  */
  if( v && pParse->nErr==0 && !db->mallocFailed ){

Changes to src/expr.c.

1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
**   (1) the expression is constant, and
**   (2) the expression does originate in the ON or USING clause
**       of a LEFT JOIN, and
**   (3) the expression does not contain any EP_FixedCol TK_COLUMN
**       operands created by the constant propagation optimization.
**
** When this routine returns true, it indicates that the expression
** can be added to the pParse->pConstExpr list and evaluated once when
** the prepared statement starts up.  See sqlite3ExprCodeAtInit().
*/
int sqlite3ExprIsConstantNotJoin(Expr *p){
  return exprIsConst(p, 2, 0);
}

/*







|







1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
**   (1) the expression is constant, and
**   (2) the expression does originate in the ON or USING clause
**       of a LEFT JOIN, and
**   (3) the expression does not contain any EP_FixedCol TK_COLUMN
**       operands created by the constant propagation optimization.
**
** When this routine returns true, it indicates that the expression
** can be added to the pParse->pAuxExpr list and evaluated once when
** the prepared statement starts up.  See sqlite3ExprCodeAtInit().
*/
int sqlite3ExprIsConstantNotJoin(Expr *p){
  return exprIsConst(p, 2, 0);
}

/*
4134
4135
4136
4137
4138
4139
4140
4141
4142
4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
4153
4154
4155
4156
4157
4158
4159
4160
4161
4162
4163
4164
4165
4166
int sqlite3ExprCodeAtInit(
  Parse *pParse,    /* Parsing context */
  Expr *pExpr,      /* The expression to code when the VDBE initializes */
  int regDest       /* Store the value in this register */
){
  ExprList *p;
  assert( ConstFactorOk(pParse) );
  p = pParse->pConstExpr;
  if( regDest<0 && p ){
    struct ExprList_item *pItem;
    int i;
    for(pItem=p->a, i=p->nExpr; i>0; pItem++, i--){
      if( pItem->reusable && sqlite3ExprCompare(0,pItem->pExpr,pExpr,-1)==0 ){
        return pItem->u.iConstExprReg;
      }
    }
  }
  pExpr = sqlite3ExprDup(pParse->db, pExpr, 0);
  p = sqlite3ExprListAppend(pParse, p, pExpr);
  if( p ){
     struct ExprList_item *pItem = &p->a[p->nExpr-1];
     pItem->reusable = regDest<0;
     if( regDest<0 ) regDest = ++pParse->nMem;
     pItem->u.iConstExprReg = regDest;
  }
  pParse->pConstExpr = p;
  return regDest;
}

/*
** Generate code to evaluate an expression and store the results
** into a register.  Return the register number where the results
** are stored.







|

















|







4134
4135
4136
4137
4138
4139
4140
4141
4142
4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
4153
4154
4155
4156
4157
4158
4159
4160
4161
4162
4163
4164
4165
4166
int sqlite3ExprCodeAtInit(
  Parse *pParse,    /* Parsing context */
  Expr *pExpr,      /* The expression to code when the VDBE initializes */
  int regDest       /* Store the value in this register */
){
  ExprList *p;
  assert( ConstFactorOk(pParse) );
  p = pParse->pAuxExpr;
  if( regDest<0 && p ){
    struct ExprList_item *pItem;
    int i;
    for(pItem=p->a, i=p->nExpr; i>0; pItem++, i--){
      if( pItem->reusable && sqlite3ExprCompare(0,pItem->pExpr,pExpr,-1)==0 ){
        return pItem->u.iConstExprReg;
      }
    }
  }
  pExpr = sqlite3ExprDup(pParse->db, pExpr, 0);
  p = sqlite3ExprListAppend(pParse, p, pExpr);
  if( p ){
     struct ExprList_item *pItem = &p->a[p->nExpr-1];
     pItem->reusable = regDest<0;
     if( regDest<0 ) regDest = ++pParse->nMem;
     pItem->u.iConstExprReg = regDest;
  }
  pParse->pAuxExpr = p;
  return regDest;
}

/*
** Generate code to evaluate an expression and store the results
** into a register.  Return the register number where the results
** are stored.
4390
4391
4392
4393
4394
4395
4396
4397
4398
4399
4400
4401
4402
4403
4404
  compRight.pRight = pExpr->x.pList->a[1].pExpr;
  exprToRegister(&exprX, exprCodeVector(pParse, &exprX, &regFree1));
  if( xJump ){
    xJump(pParse, &exprAnd, dest, jumpIfNull);
  }else{
    /* Mark the expression is being from the ON or USING clause of a join
    ** so that the sqlite3ExprCodeTarget() routine will not attempt to move
    ** it into the Parse.pConstExpr list.  We should use a new bit for this,
    ** for clarity, but we are out of bits in the Expr.flags field so we
    ** have to reuse the EP_FromJoin bit.  Bummer. */
    exprX.flags |= EP_FromJoin;
    sqlite3ExprCodeTarget(pParse, &exprAnd, dest);
  }
  sqlite3ReleaseTempReg(pParse, regFree1);








|







4390
4391
4392
4393
4394
4395
4396
4397
4398
4399
4400
4401
4402
4403
4404
  compRight.pRight = pExpr->x.pList->a[1].pExpr;
  exprToRegister(&exprX, exprCodeVector(pParse, &exprX, &regFree1));
  if( xJump ){
    xJump(pParse, &exprAnd, dest, jumpIfNull);
  }else{
    /* Mark the expression is being from the ON or USING clause of a join
    ** so that the sqlite3ExprCodeTarget() routine will not attempt to move
    ** it into the Parse.pAuxExpr list.  We should use a new bit for this,
    ** for clarity, but we are out of bits in the Expr.flags field so we
    ** have to reuse the EP_FromJoin bit.  Bummer. */
    exprX.flags |= EP_FromJoin;
    sqlite3ExprCodeTarget(pParse, &exprAnd, dest);
  }
  sqlite3ReleaseTempReg(pParse, regFree1);

Changes to src/prepare.c.

502
503
504
505
506
507
508
509
510
511
512
513
514
515
516

/*
** Free all memory allocations in the pParse object
*/
void sqlite3ParserReset(Parse *pParse){
  sqlite3 *db = pParse->db;
  sqlite3DbFree(db, pParse->aLabel);
  sqlite3ExprListDelete(db, pParse->pConstExpr);
  if( db ){
    assert( db->lookaside.bDisable >= pParse->disableLookaside );
    db->lookaside.bDisable -= pParse->disableLookaside;
  }
  pParse->disableLookaside = 0;
}








|







502
503
504
505
506
507
508
509
510
511
512
513
514
515
516

/*
** Free all memory allocations in the pParse object
*/
void sqlite3ParserReset(Parse *pParse){
  sqlite3 *db = pParse->db;
  sqlite3DbFree(db, pParse->aLabel);
  sqlite3ExprListDelete(db, pParse->pAuxExpr);
  if( db ){
    assert( db->lookaside.bDisable >= pParse->disableLookaside );
    db->lookaside.bDisable -= pParse->disableLookaside;
  }
  pParse->disableLookaside = 0;
}

Changes to src/sqliteInt.h.

2583
2584
2585
2586
2587
2588
2589
2590
2591

2592
2593
2594
2595
2596
2597
2598
  struct ExprList_item { /* For each expression in the list */
    Expr *pExpr;            /* The parse tree for this expression */
    char *zName;            /* Token associated with this expression */
    char *zSpan;            /* Original text of the expression */
    u8 sortOrder;           /* 1 for DESC or 0 for ASC */
    unsigned done :1;       /* A flag to indicate when processing is finished */
    unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */
    unsigned reusable :1;   /* Constant expression is reusable */
    unsigned bSorterRef :1; /* Defer evaluation until after sorting */

    union {
      struct {
        u16 iOrderByCol;      /* For ORDER BY, column number in result set */
        u16 iAlias;           /* Index into Parse.aAlias[] for zName */
      } x;
      int iConstExprReg;      /* Register in which Expr value is cached */
    } u;







|

>







2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
  struct ExprList_item { /* For each expression in the list */
    Expr *pExpr;            /* The parse tree for this expression */
    char *zName;            /* Token associated with this expression */
    char *zSpan;            /* Original text of the expression */
    u8 sortOrder;           /* 1 for DESC or 0 for ASC */
    unsigned done :1;       /* A flag to indicate when processing is finished */
    unsigned bSpanIsTab :1; /* zSpan holds DB.TABLE.COLUMN */
    unsigned reusable   :1; /* Constant expression is reusable */
    unsigned bSorterRef :1; /* Defer evaluation until after sorting */
    unsigned bAuxSubrtn :1; /* Member of pAuxExpr to be coded as a subroutine */
    union {
      struct {
        u16 iOrderByCol;      /* For ORDER BY, column number in result set */
        u16 iAlias;           /* Index into Parse.aAlias[] for zName */
      } x;
      int iConstExprReg;      /* Register in which Expr value is cached */
    } u;
3071
3072
3073
3074
3075
3076
3077
3078
3079
3080
3081
3082
3083
3084
3085
  int nTab;            /* Number of previously allocated VDBE cursors */
  int nMem;            /* Number of memory cells used so far */
  int szOpAlloc;       /* Bytes of memory space allocated for Vdbe.aOp[] */
  int iSelfTab;        /* Table associated with an index on expr, or negative
                       ** of the base register during check-constraint eval */
  int nLabel;          /* Number of labels used */
  int *aLabel;         /* Space to hold the labels */
  ExprList *pConstExpr;/* Constant expressions */
  Token constraintName;/* Name of the constraint currently being parsed */
  yDbMask writeMask;   /* Start a write transaction on these databases */
  yDbMask cookieMask;  /* Bitmask of schema verified databases */
  int regRowid;        /* Register holding rowid of CREATE TABLE entry */
  int regRoot;         /* Register holding root page number for new objects */
  int nMaxArg;         /* Max args passed to user function by sub-program */
  int nSelect;         /* Number of SELECT stmts. Counter for Select.selId */







|







3072
3073
3074
3075
3076
3077
3078
3079
3080
3081
3082
3083
3084
3085
3086
  int nTab;            /* Number of previously allocated VDBE cursors */
  int nMem;            /* Number of memory cells used so far */
  int szOpAlloc;       /* Bytes of memory space allocated for Vdbe.aOp[] */
  int iSelfTab;        /* Table associated with an index on expr, or negative
                       ** of the base register during check-constraint eval */
  int nLabel;          /* Number of labels used */
  int *aLabel;         /* Space to hold the labels */
  ExprList *pAuxExpr;  /* Exprs coded at initialization or as subroutines */
  Token constraintName;/* Name of the constraint currently being parsed */
  yDbMask writeMask;   /* Start a write transaction on these databases */
  yDbMask cookieMask;  /* Bitmask of schema verified databases */
  int regRowid;        /* Register holding rowid of CREATE TABLE entry */
  int regRoot;         /* Register holding root page number for new objects */
  int nMaxArg;         /* Max args passed to user function by sub-program */
  int nSelect;         /* Number of SELECT stmts. Counter for Select.selId */