Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -2094,11 +2094,11 @@ ** (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(). +** the prepared statement starts up. See sqlite3ExprCodeRunJustOnce(). */ int sqlite3ExprIsConstantNotJoin(Expr *p){ return exprIsConst(p, 2, 0); } @@ -4125,13 +4125,13 @@ 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); + /* 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; @@ -4505,19 +4505,27 @@ sqlite3ReleaseTempReg(pParse, regFree2); return inReg; } /* -** Factor out the code of the given expression to initialization time. +** 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 will -** code to the same register. +** 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 sqlite3ExprCodeAtInit( +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; @@ -4531,18 +4539,33 @@ 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; + 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 @@ -4563,11 +4586,11 @@ if( ConstFactorOk(pParse) && pExpr->op!=TK_REGISTER && sqlite3ExprIsConstantNotJoin(pExpr) ){ *pReg = 0; - r2 = sqlite3ExprCodeAtInit(pParse, pExpr, -1); + r2 = sqlite3ExprCodeRunJustOnce(pParse, pExpr, -1); }else{ int r1 = sqlite3GetTempReg(pParse); r2 = sqlite3ExprCodeTarget(pParse, pExpr, r1); if( r2==r1 ){ *pReg = r1; @@ -4620,11 +4643,11 @@ ** 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); + sqlite3ExprCodeRunJustOnce(pParse, pExpr, target); }else{ sqlite3ExprCodeCopy(pParse, pExpr, target); } } @@ -4680,11 +4703,11 @@ sqlite3VdbeAddOp2(v, copyOp, j+srcReg-1, target+i); } }else if( (flags & SQLITE_ECEL_FACTOR)!=0 && sqlite3ExprIsConstantNotJoin(pExpr) ){ - sqlite3ExprCodeAtInit(pParse, pExpr, target+i); + sqlite3ExprCodeRunJustOnce(pParse, pExpr, target+i); }else{ int inReg = sqlite3ExprCodeTarget(pParse, pExpr, target+i); if( inReg!=target+i ){ VdbeOp *pOp; if( copyOp==OP_Copy @@ -5240,25 +5263,11 @@ 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( aop2!=pB->op2 && pA->op==TK_TRUTH ) return 2; if( pA->op!=TK_IN && pA->iTable!=pB->iTable && pA->iTable!=iTab ){ return 2; } } } Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -4237,11 +4237,11 @@ #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 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 */ Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -1542,11 +1542,10 @@ 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 ); Index: test/func.test ================================================================== --- test/func.test +++ test/func.test @@ -1474,7 +1474,27 @@ ) ); 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 Index: test/func5.test ================================================================== --- test/func5.test +++ test/func5.test @@ -51,13 +51,14 @@ 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')=counter2('hello')+x + WHERE x+counter2('hello')=$cvalue+x ORDER BY +x; } {1 2 3 4 5 6 7 8} finish_test