/ Check-in [c5f96a08]
Login

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

Overview
Comment:Do not factor out constant functions into the initialization section of a prepared statement, because even though they are constant, they can still throw exceptions. Instead, put such functions in an OP_Once block. This fixes ticket [3c9eadd2a6ba0aa5] and causes COALESCE() and CASE...END to be short-circuit.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c5f96a085db9688a09793f52ce1ecf033c2e6e2e5873a19fe0fb374b242b317f
User & Date: drh 2020-03-11 19:56:26
Context
2020-03-12
17:28
Fix comments and strengthen assert() statements associated with the OPFLAG_SEEKEQ and BTREE_SEEK_EQ flags. (check-in: 23174921 user: drh tags: trunk)
2020-03-11
20:03
Merge enhancements from trunk. (check-in: 4945a662 user: drh tags: approximate-analyze)
19:56
Do not factor out constant functions into the initialization section of a prepared statement, because even though they are constant, they can still throw exceptions. Instead, put such functions in an OP_Once block. This fixes ticket [3c9eadd2a6ba0aa5] and causes COALESCE() and CASE...END to be short-circuit. (check-in: c5f96a08 user: drh tags: trunk)
19:41
Rename sqlite3ExprCodeAtInit() to sqlite3ExprCodeRunJustOnce(). Other changes to make the new code cleaner. Test cases added. (Closed-Leaf check-in: d7f18489 user: drh tags: do-not-factor-functions)
02:04
This variant to the fix for ticket [e0c2ad1aa8a9c691] uses fewer CPU cycles. (check-in: fb5a8a9e user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
....
4123
4124
4125
4126
4127
4128
4129
4130
4131
4132
4133
4134
4135
4136
4137
4138
4139
....
4503
4504
4505
4506
4507
4508
4509
4510






4511
4512
4513
4514
4515
4516


4517
4518
4519
4520
4521
4522
4523
4524
4525
....
4529
4530
4531
4532
4533
4534
4535














4536
4537
4538
4539
4540
4541
4542
4543

4544
4545
4546
4547
4548
4549
4550
....
4561
4562
4563
4564
4565
4566
4567
4568
4569
4570
4571
4572
4573
4574
4575
....
4618
4619
4620
4621
4622
4623
4624
4625
4626
4627
4628
4629
4630
4631
4632
....
4678
4679
4680
4681
4682
4683
4684
4685
4686
4687
4688
4689
4690
4691
4692
....
5238
5239
5240
5241
5242
5243
5244
5245
5246
5247
5248
5249
5250
5251
5252
5253
5254
5255
5256
5257
5258
5259
5260
5261
5262
5263
5264
5265
5266
**   (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);
}

/*
** Walk an expression tree.  Return non-zero if the expression is constant
................................................................................
#ifndef SQLITE_OMIT_WINDOWFUNC
      if( ExprHasProperty(pExpr, EP_WinFunc) ){
        return pExpr->y.pWin->regResult;
      }
#endif

      if( ConstFactorOk(pParse) && sqlite3ExprIsConstantNotJoin(pExpr) ){
        /* SQL functions can be expensive. So try to move constant functions
        ** out of the inner loop, even if that means an extra OP_Copy. */
        return sqlite3ExprCodeAtInit(pParse, pExpr, -1);
      }
      assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
      assert( !ExprHasProperty(pExpr, EP_TokenOnly) );
      pFarg = pExpr->x.pList;
      nFarg = pFarg ? pFarg->nExpr : 0;
      assert( !ExprHasProperty(pExpr, EP_IntValue) );
      zId = pExpr->u.zToken;
................................................................................
  }
  sqlite3ReleaseTempReg(pParse, regFree1);
  sqlite3ReleaseTempReg(pParse, regFree2);
  return inReg;
}

/*
** Factor out the code of the given expression to initialization time.






**
** If regDest>=0 then the result is always stored in that register and the
** result is not reusable.  If regDest<0 then this routine is free to 
** store the value whereever it wants.  The register where the expression 
** is stored is returned.  When regDest<0, two identical expressions will
** code to the same register.


*/
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;
................................................................................
    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.
................................................................................
  int r2;
  pExpr = sqlite3ExprSkipCollateAndLikely(pExpr);
  if( ConstFactorOk(pParse)
   && pExpr->op!=TK_REGISTER
   && sqlite3ExprIsConstantNotJoin(pExpr)
  ){
    *pReg  = 0;
    r2 = sqlite3ExprCodeAtInit(pParse, pExpr, -1);
  }else{
    int r1 = sqlite3GetTempReg(pParse);
    r2 = sqlite3ExprCodeTarget(pParse, pExpr, r1);
    if( r2==r1 ){
      *pReg = r1;
    }else{
      sqlite3ReleaseTempReg(pParse, r1);
................................................................................
** Generate code that will evaluate expression pExpr and store the
** results in register target.  The results are guaranteed to appear
** in register target.  If the expression is constant, then this routine
** might choose to code the expression at initialization time.
*/
void sqlite3ExprCodeFactorable(Parse *pParse, Expr *pExpr, int target){
  if( pParse->okConstFactor && sqlite3ExprIsConstantNotJoin(pExpr) ){
    sqlite3ExprCodeAtInit(pParse, pExpr, target);
  }else{
    sqlite3ExprCodeCopy(pParse, pExpr, target);
  }
}

/*
** Generate code that pushes the value of every element of the given
................................................................................
        n--;
      }else{
        sqlite3VdbeAddOp2(v, copyOp, j+srcReg-1, target+i);
      }
    }else if( (flags & SQLITE_ECEL_FACTOR)!=0
           && sqlite3ExprIsConstantNotJoin(pExpr)
    ){
      sqlite3ExprCodeAtInit(pParse, pExpr, target+i);
    }else{
      int inReg = sqlite3ExprCodeTarget(pParse, pExpr, target+i);
      if( inReg!=target+i ){
        VdbeOp *pOp;
        if( copyOp==OP_Copy
         && (pOp=sqlite3VdbeGetOp(v, -1))->opcode==OP_Copy
         && pOp->p1+pOp->p3+1==inReg
................................................................................
    if( sqlite3ExprCompare(pParse, pA->pRight, pB->pRight, iTab) ) return 2;
    if( sqlite3ExprListCompare(pA->x.pList, pB->x.pList, iTab) ) return 2;
    if( pA->op!=TK_STRING
     && pA->op!=TK_TRUEFALSE
     && ALWAYS((combinedFlags & EP_Reduced)==0)
    ){
      if( pA->iColumn!=pB->iColumn ) return 2;
      if( pA->op2!=pB->op2 ){
        if( pA->op==TK_TRUTH ) return 2;
        if( pA->op==TK_FUNCTION && iTab<0 ){
          /* Ex: CREATE TABLE t1(a CHECK( a<julianday('now') ));
          **     INSERT INTO t1(a) VALUES(julianday('now')+10);
          ** Without this test, sqlite3ExprCodeAtInit() will run on the
          ** the julianday() of INSERT first, and remember that expression.
          ** Then sqlite3ExprCodeInit() will see the julianday() in the CHECK
          ** constraint as redundant, reusing the one from the INSERT, even
          ** though the julianday() in INSERT lacks the critical NC_IsCheck
          ** flag.  See ticket [830277d9db6c3ba1] (2019-10-30)
          */
          return 2;
        }
      }
      if( pA->op!=TK_IN && pA->iTable!=pB->iTable && pA->iTable!=iTab ){
        return 2;
      }
    }
  }
  return 0;
}







|







 







|
|
|







 







|
>
>
>
>
>
>




|
|
>
>

|







 







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







 







|







 







|







 







|







 







|
<
<
<
<
<
<
<
<
<
<
<
<
<
<







2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
....
4123
4124
4125
4126
4127
4128
4129
4130
4131
4132
4133
4134
4135
4136
4137
4138
4139
....
4503
4504
4505
4506
4507
4508
4509
4510
4511
4512
4513
4514
4515
4516
4517
4518
4519
4520
4521
4522
4523
4524
4525
4526
4527
4528
4529
4530
4531
4532
4533
....
4537
4538
4539
4540
4541
4542
4543
4544
4545
4546
4547
4548
4549
4550
4551
4552
4553
4554
4555
4556
4557
4558
4559
4560
4561
4562
4563
4564
4565
4566
4567
4568
4569
4570
4571
4572
4573
....
4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
4598
....
4641
4642
4643
4644
4645
4646
4647
4648
4649
4650
4651
4652
4653
4654
4655
....
4701
4702
4703
4704
4705
4706
4707
4708
4709
4710
4711
4712
4713
4714
4715
....
5261
5262
5263
5264
5265
5266
5267
5268














5269
5270
5271
5272
5273
5274
5275
**   (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 sqlite3ExprCodeRunJustOnce().
*/
int sqlite3ExprIsConstantNotJoin(Expr *p){
  return exprIsConst(p, 2, 0);
}

/*
** Walk an expression tree.  Return non-zero if the expression is constant
................................................................................
#ifndef SQLITE_OMIT_WINDOWFUNC
      if( ExprHasProperty(pExpr, EP_WinFunc) ){
        return pExpr->y.pWin->regResult;
      }
#endif

      if( ConstFactorOk(pParse) && sqlite3ExprIsConstantNotJoin(pExpr) ){
        /* SQL functions can be expensive. So try to avoid running them
        ** multiple times if we know they always give the same result */
        return sqlite3ExprCodeRunJustOnce(pParse, pExpr, -1);
      }
      assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
      assert( !ExprHasProperty(pExpr, EP_TokenOnly) );
      pFarg = pExpr->x.pList;
      nFarg = pFarg ? pFarg->nExpr : 0;
      assert( !ExprHasProperty(pExpr, EP_IntValue) );
      zId = pExpr->u.zToken;
................................................................................
  }
  sqlite3ReleaseTempReg(pParse, regFree1);
  sqlite3ReleaseTempReg(pParse, regFree2);
  return inReg;
}

/*
** Generate code that will evaluate expression pExpr just one time
** per prepared statement execution.
**
** If the expression uses functions (that might throw an exception) then
** guard them with an OP_Once opcode to ensure that the code is only executed
** once. If no functions are involved, then factor the code out and put it at
** the end of the prepared statement in the initialization section.
**
** If regDest>=0 then the result is always stored in that register and the
** result is not reusable.  If regDest<0 then this routine is free to 
** store the value whereever it wants.  The register where the expression 
** is stored is returned.  When regDest<0, two identical expressions might
** code to the same register, if they do not contain function calls and hence
** are factored out into the initialization section at the end of the
** prepared statement.
*/
int sqlite3ExprCodeRunJustOnce(
  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;
................................................................................
    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);
  if( pExpr!=0 && ExprHasProperty(pExpr, EP_HasFunc) ){
    Vdbe *v = pParse->pVdbe;
    int addr;
    assert( v );
    addr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
    pParse->okConstFactor = 0;
    if( !pParse->db->mallocFailed ){
      if( regDest<0 ) regDest = ++pParse->nMem;
      sqlite3ExprCode(pParse, pExpr, regDest);
    }
    pParse->okConstFactor = 1;
    sqlite3ExprDelete(pParse->db, pExpr);
    sqlite3VdbeJumpHere(v, addr);
  }else{
    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.
................................................................................
  int r2;
  pExpr = sqlite3ExprSkipCollateAndLikely(pExpr);
  if( ConstFactorOk(pParse)
   && pExpr->op!=TK_REGISTER
   && sqlite3ExprIsConstantNotJoin(pExpr)
  ){
    *pReg  = 0;
    r2 = sqlite3ExprCodeRunJustOnce(pParse, pExpr, -1);
  }else{
    int r1 = sqlite3GetTempReg(pParse);
    r2 = sqlite3ExprCodeTarget(pParse, pExpr, r1);
    if( r2==r1 ){
      *pReg = r1;
    }else{
      sqlite3ReleaseTempReg(pParse, r1);
................................................................................
** Generate code that will evaluate expression pExpr and store the
** results in register target.  The results are guaranteed to appear
** in register target.  If the expression is constant, then this routine
** might choose to code the expression at initialization time.
*/
void sqlite3ExprCodeFactorable(Parse *pParse, Expr *pExpr, int target){
  if( pParse->okConstFactor && sqlite3ExprIsConstantNotJoin(pExpr) ){
    sqlite3ExprCodeRunJustOnce(pParse, pExpr, target);
  }else{
    sqlite3ExprCodeCopy(pParse, pExpr, target);
  }
}

/*
** Generate code that pushes the value of every element of the given
................................................................................
        n--;
      }else{
        sqlite3VdbeAddOp2(v, copyOp, j+srcReg-1, target+i);
      }
    }else if( (flags & SQLITE_ECEL_FACTOR)!=0
           && sqlite3ExprIsConstantNotJoin(pExpr)
    ){
      sqlite3ExprCodeRunJustOnce(pParse, pExpr, target+i);
    }else{
      int inReg = sqlite3ExprCodeTarget(pParse, pExpr, target+i);
      if( inReg!=target+i ){
        VdbeOp *pOp;
        if( copyOp==OP_Copy
         && (pOp=sqlite3VdbeGetOp(v, -1))->opcode==OP_Copy
         && pOp->p1+pOp->p3+1==inReg
................................................................................
    if( sqlite3ExprCompare(pParse, pA->pRight, pB->pRight, iTab) ) return 2;
    if( sqlite3ExprListCompare(pA->x.pList, pB->x.pList, iTab) ) return 2;
    if( pA->op!=TK_STRING
     && pA->op!=TK_TRUEFALSE
     && ALWAYS((combinedFlags & EP_Reduced)==0)
    ){
      if( pA->iColumn!=pB->iColumn ) return 2;
      if( pA->op2!=pB->op2 && pA->op==TK_TRUTH ) return 2;














      if( pA->op!=TK_IN && pA->iTable!=pB->iTable && pA->iTable!=iTab ){
        return 2;
      }
    }
  }
  return 0;
}

Changes to src/sqliteInt.h.

4235
4236
4237
4238
4239
4240
4241
4242
4243
4244
4245
4246
4247
4248
4249
void sqlite3ExprCodeMove(Parse*, int, int, int);
void sqlite3ExprCode(Parse*, Expr*, int);
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
void sqlite3ExprCodeGeneratedColumn(Parse*, Column*, int);
#endif
void sqlite3ExprCodeCopy(Parse*, Expr*, int);
void sqlite3ExprCodeFactorable(Parse*, Expr*, int);
int sqlite3ExprCodeAtInit(Parse*, Expr*, int);
int sqlite3ExprCodeTemp(Parse*, Expr*, int*);
int sqlite3ExprCodeTarget(Parse*, Expr*, int);
int sqlite3ExprCodeExprList(Parse*, ExprList*, int, int, u8);
#define SQLITE_ECEL_DUP      0x01  /* Deep, not shallow copies */
#define SQLITE_ECEL_FACTOR   0x02  /* Factor out constant terms */
#define SQLITE_ECEL_REF      0x04  /* Use ExprList.u.x.iOrderByCol */
#define SQLITE_ECEL_OMITREF  0x08  /* Omit if ExprList.u.x.iOrderByCol */







|







4235
4236
4237
4238
4239
4240
4241
4242
4243
4244
4245
4246
4247
4248
4249
void sqlite3ExprCodeMove(Parse*, int, int, int);
void sqlite3ExprCode(Parse*, Expr*, int);
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
void sqlite3ExprCodeGeneratedColumn(Parse*, Column*, int);
#endif
void sqlite3ExprCodeCopy(Parse*, Expr*, int);
void sqlite3ExprCodeFactorable(Parse*, Expr*, int);
int sqlite3ExprCodeRunJustOnce(Parse*, Expr*, int);
int sqlite3ExprCodeTemp(Parse*, Expr*, int*);
int sqlite3ExprCodeTarget(Parse*, Expr*, int);
int sqlite3ExprCodeExprList(Parse*, ExprList*, int, int, u8);
#define SQLITE_ECEL_DUP      0x01  /* Deep, not shallow copies */
#define SQLITE_ECEL_FACTOR   0x02  /* Factor out constant terms */
#define SQLITE_ECEL_REF      0x04  /* Use ExprList.u.x.iOrderByCol */
#define SQLITE_ECEL_OMITREF  0x08  /* Omit if ExprList.u.x.iOrderByCol */

Changes to src/vdbe.c.

1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
  i64 nByte;          /* Total size of the output string or blob */
  u16 flags1;         /* Initial flags for P1 */
  u16 flags2;         /* Initial flags for P2 */

  pIn1 = &aMem[pOp->p1];
  pIn2 = &aMem[pOp->p2];
  pOut = &aMem[pOp->p3];
  testcase( pIn1==pIn2 );
  testcase( pOut==pIn2 );
  assert( pIn1!=pOut );
  flags1 = pIn1->flags;
  testcase( flags1 & MEM_Null );
  testcase( pIn2->flags & MEM_Null );
  if( (flags1 | pIn2->flags) & MEM_Null ){
    sqlite3VdbeMemSetNull(pOut);







<







1540
1541
1542
1543
1544
1545
1546

1547
1548
1549
1550
1551
1552
1553
  i64 nByte;          /* Total size of the output string or blob */
  u16 flags1;         /* Initial flags for P1 */
  u16 flags2;         /* Initial flags for P2 */

  pIn1 = &aMem[pOp->p1];
  pIn2 = &aMem[pOp->p2];
  pOut = &aMem[pOp->p3];

  testcase( pOut==pIn2 );
  assert( pIn1!=pOut );
  flags1 = pIn1->flags;
  testcase( flags1 & MEM_Null );
  testcase( pIn2->flags & MEM_Null );
  if( (flags1 | pIn2->flags) & MEM_Null ){
    sqlite3VdbeMemSetNull(pOut);

Changes to test/func.test.

1472
1473
1474
1475
1476
1477
1478
1479




















1480
              30,31,32,33,34,35,36,37,38,39,
              40,41,42,43,44,45,46,47,48,a)
   )
  );
  INSERT INTO t1(a) VALUES(1),(2);
  SELECT * FROM t1;
} {1 2}





















finish_test








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

1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
              30,31,32,33,34,35,36,37,38,39,
              40,41,42,43,44,45,46,47,48,a)
   )
  );
  INSERT INTO t1(a) VALUES(1),(2);
  SELECT * FROM t1;
} {1 2}

# 2020-03-11 COALESCE() should short-circuit
# See also ticket 3c9eadd2a6ba0aa5
# Both issues stem from the fact that functions that could
# throw exceptions were being factored out into initialization
# code.  The fix was to put those function calls inside of
# OP_Once instead.
#
reset_db
do_execsql_test func-35.100 {
  CREATE TABLE t1(x);
  SELECT coalesce(x, abs(-9223372036854775808)) FROM t1;
} {}
do_execsql_test func-35.110 {
  SELECT coalesce(x, 'xyz' LIKE printf('%.1000000c','y')) FROM t1;
} {}
do_execsql_test func-35.200 {
  CREATE TABLE t0(c0 CHECK(ABS(-9223372036854775808)));
  PRAGMA integrity_check;
} {ok}

finish_test

Changes to test/func5.test.

49
50
51
52
53
54
55

56
57
58
59
60
61
62
63
} {1 2 3 4 5 6 7 8}
sqlite3_create_function db
do_execsql_test func5-2.2 {
  SELECT x, y FROM t2
   WHERE x+counter1('hello')=counter1('hello')+x
   ORDER BY +x;
} {}

do_execsql_test func5-2.3 {
  SELECT x, y FROM t2
   WHERE x+counter2('hello')=counter2('hello')+x
   ORDER BY +x;
} {1 2 3 4 5 6 7 8}


finish_test







>


|





49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
} {1 2 3 4 5 6 7 8}
sqlite3_create_function db
do_execsql_test func5-2.2 {
  SELECT x, y FROM t2
   WHERE x+counter1('hello')=counter1('hello')+x
   ORDER BY +x;
} {}
set cvalue [db one {SELECT counter2('hello')+1}]
do_execsql_test func5-2.3 {
  SELECT x, y FROM t2
   WHERE x+counter2('hello')=$cvalue+x
   ORDER BY +x;
} {1 2 3 4 5 6 7 8}


finish_test