Generate VDBE code for the built-in COALESCE() and IFNULL() functions. This
allows unused arguments to never be evaluated, which is a performance win when
the unused argument is a subquery.
FossilOrigin-Name: 30055b257c3c65f8123cad5ac6c62c4c6ca2c900
diff --git a/src/expr.c b/src/expr.c
index 0cc3093..249ab90 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -2394,6 +2394,27 @@
sqlite3ErrorMsg(pParse, "unknown function: %.*s()", nId, zId);
break;
}
+
+ /* Attempt a direct implementation of the built-in COALESCE() and
+ ** IFNULL() functions. This avoids unnecessary evalation of
+ ** arguments past the first non-NULL argument.
+ */
+ if( pDef->flags & SQLITE_FUNC_COALESCE ){
+ int endCoalesce = sqlite3VdbeMakeLabel(v);
+ assert( nFarg>=2 );
+ sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target);
+ for(i=1; i<nFarg; i++){
+ sqlite3VdbeAddOp2(v, OP_NotNull, target, endCoalesce);
+ sqlite3ExprCacheRemove(pParse, target);
+ sqlite3ExprCachePush(pParse);
+ sqlite3ExprCode(pParse, pFarg->a[i].pExpr, target);
+ sqlite3ExprCachePop(pParse, 1);
+ }
+ sqlite3VdbeResolveLabel(v, endCoalesce);
+ break;
+ }
+
+
if( pFarg ){
r1 = sqlite3GetTempRange(pParse, nFarg);
sqlite3ExprCachePush(pParse); /* Ticket 2ea2425d34be */
diff --git a/src/func.c b/src/func.c
index 2766fa3..980d04e 100644
--- a/src/func.c
+++ b/src/func.c
@@ -335,6 +335,14 @@
}
}
+
+#if 0 /* This function is never used. */
+/*
+** The COALESCE() and IFNULL() functions used to be implemented as shown
+** here. But now they are implemented as VDBE code so that unused arguments
+** do not have to be computed. This legacy implementation is retained as
+** comment.
+*/
/*
** Implementation of the IFNULL(), NVL(), and COALESCE() functions.
** All three do the same thing. They return the first non-NULL
@@ -353,6 +361,8 @@
}
}
}
+#endif /* NOT USED */
+#define ifnullFunc versionFunc /* Substitute function - never called */
/*
** Implementation of random(). Return a random integer.
@@ -1437,10 +1447,12 @@
FUNCTION(upper, 1, 0, 0, upperFunc ),
FUNCTION(lower, 1, 0, 0, lowerFunc ),
FUNCTION(coalesce, 1, 0, 0, 0 ),
- FUNCTION(coalesce, -1, 0, 0, ifnullFunc ),
FUNCTION(coalesce, 0, 0, 0, 0 ),
+/* FUNCTION(coalesce, -1, 0, 0, ifnullFunc ), */
+ {-1,SQLITE_UTF8,SQLITE_FUNC_COALESCE,0,0,ifnullFunc,0,0,"coalesce",0},
FUNCTION(hex, 1, 0, 0, hexFunc ),
- FUNCTION(ifnull, 2, 0, 1, ifnullFunc ),
+/* FUNCTION(ifnull, 2, 0, 0, ifnullFunc ), */
+ {2,SQLITE_UTF8,SQLITE_FUNC_COALESCE,0,0,ifnullFunc,0,0,"ifnull",0},
FUNCTION(random, 0, 0, 0, randomFunc ),
FUNCTION(randomblob, 1, 0, 0, randomBlob ),
FUNCTION(nullif, 2, 0, 1, nullifFunc ),
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 24debba..16c1d6b 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -959,6 +959,7 @@
#define SQLITE_FUNC_NEEDCOLL 0x08 /* sqlite3GetFuncCollSeq() might be called */
#define SQLITE_FUNC_PRIVATE 0x10 /* Allowed for internal use only */
#define SQLITE_FUNC_COUNT 0x20 /* Built-in count(*) aggregate */
+#define SQLITE_FUNC_COALESCE 0x40 /* Built-in coalesce() or ifnull() function */
/*
** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are