/ Check-in [b7f1fc26]
Login

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

Overview
Comment:Allow the query planner to evaluate deterministic scalar SQL functions used in WHERE constraints if all arguments are SQL literals in order to compare the results with sqlite_stat4 sample data.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | stat4-function
Files: files | file ages | folders
SHA1: b7f1fc26d24012e1e7c7f6b3cc0b84ad2b02b8ad
User & Date: dan 2015-03-11 20:06:40
Context
2015-03-11
20:59
Allow the default value for columns added using ALTER TABLE ADD COLUMN to be a function in existing schemas loaded from disk. But prevent this version of SQLite from being used to create such a column. check-in: ff868e22 user: dan tags: stat4-function
20:06
Allow the query planner to evaluate deterministic scalar SQL functions used in WHERE constraints if all arguments are SQL literals in order to compare the results with sqlite_stat4 sample data. check-in: b7f1fc26 user: dan tags: stat4-function
14:34
Expand the multi-process test cases to repeat each case 20 times and to repeat tests using different journal modes. check-in: a2715b04 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vdbeapi.c.

   633    633   }
   634    634   
   635    635   /*
   636    636   ** Return the current time for a statement
   637    637   */
   638    638   sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context *p){
   639    639     Vdbe *v = p->pVdbe;
          640  +  sqlite3_int64 iTime = 0;
   640    641     int rc;
   641         -  if( v->iCurrentTime==0 ){
   642         -    rc = sqlite3OsCurrentTimeInt64(p->pOut->db->pVfs, &v->iCurrentTime);
   643         -    if( rc ) v->iCurrentTime = 0;
   644         -  }
   645         -  return v->iCurrentTime;
          642  +  if( v && v->iCurrentTime ) return v->iCurrentTime;
          643  +  rc = sqlite3OsCurrentTimeInt64(p->pOut->db->pVfs, &iTime);
          644  +  if( rc ) return 0;
          645  +  if( v ) v->iCurrentTime = iTime;
          646  +  return iTime;
   646    647   }
   647    648   
   648    649   /*
   649    650   ** The following is the implementation of an SQL function that always
   650    651   ** fails with an error message stating that the function is used in the
   651    652   ** wrong context.  The sqlite3_overload_function() API might construct
   652    653   ** SQL function that use this routine so that the functions will exist
................................................................................
   708    709   ** Return the auxiliary data pointer, if any, for the iArg'th argument to
   709    710   ** the user-function defined by pCtx.
   710    711   */
   711    712   void *sqlite3_get_auxdata(sqlite3_context *pCtx, int iArg){
   712    713     AuxData *pAuxData;
   713    714   
   714    715     assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) );
          716  +  if( pCtx->pVdbe==0 ) return 0;
   715    717     for(pAuxData=pCtx->pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNext){
   716    718       if( pAuxData->iOp==pCtx->iOp && pAuxData->iArg==iArg ) break;
   717    719     }
   718    720   
   719    721     return (pAuxData ? pAuxData->pAux : 0);
   720    722   }
   721    723   
................................................................................
   731    733     void (*xDelete)(void*)
   732    734   ){
   733    735     AuxData *pAuxData;
   734    736     Vdbe *pVdbe = pCtx->pVdbe;
   735    737   
   736    738     assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) );
   737    739     if( iArg<0 ) goto failed;
          740  +  if( pVdbe==0 ) goto failed;
   738    741   
   739    742     for(pAuxData=pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNext){
   740    743       if( pAuxData->iOp==pCtx->iOp && pAuxData->iArg==iArg ) break;
   741    744     }
   742    745     if( pAuxData==0 ){
   743    746       pAuxData = sqlite3DbMallocZero(pVdbe->db, sizeof(AuxData));
   744    747       if( !pAuxData ) goto failed;

Changes to src/vdbemem.c.

  1130   1130     }
  1131   1131   #else
  1132   1132     UNUSED_PARAMETER(p);
  1133   1133   #endif /* defined(SQLITE_ENABLE_STAT3_OR_STAT4) */
  1134   1134     return sqlite3ValueNew(db);
  1135   1135   }
  1136   1136   
         1137  +/*
         1138  +** The expression object indicated by the second argument is guaranteed
         1139  +** to be a scalar SQL function. If
         1140  +**
         1141  +**   * all function arguments are SQL literals,
         1142  +**   * the SQLITE_FUNC_CONSTANT function flag is set,
         1143  +**   * the SQLITE_FUNC_NEEDCOLL function flag is not set, and
         1144  +**   * this routine is being invoked as part of examining stat4 data,
         1145  +**     not as part of handling a default value on a column created using
         1146  +**     ALTER TABLE ADD COLUMN, 
         1147  +**
         1148  +** then this routine attempts to invoke the SQL function. Assuming no
         1149  +** error occurs, output parameter (*ppVal) is set to point to a value 
         1150  +** object containing the result before returning SQLITE_OK.
         1151  +**
         1152  +** Affinity aff is applied to the result of the function before returning.
         1153  +** If the result is a text value, the sqlite3_value object uses encoding 
         1154  +** enc.
         1155  +**
         1156  +** If the conditions above are not met, this function returns SQLITE_OK
         1157  +** and sets (*ppVal) to NULL. Or, if an error occurs, (*ppVal) is set to
         1158  +** NULL and an SQLite error code returned.
         1159  +*/
         1160  +#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
         1161  +static int valueFromFunction(
         1162  +  sqlite3 *db,                    /* The database connection */
         1163  +  Expr *p,                        /* The expression to evaluate */
         1164  +  u8 enc,                         /* Encoding to use */
         1165  +  u8 aff,                         /* Affinity to use */
         1166  +  sqlite3_value **ppVal,          /* Write the new value here */
         1167  +  struct ValueNewStat4Ctx *pCtx   /* Second argument for valueNew() */
         1168  +){
         1169  +  sqlite3_context ctx;            /* Context object for function invocation */
         1170  +  sqlite3_value **apVal = 0;      /* Function arguments */
         1171  +  int nVal = 0;                   /* Size of apVal[] array */
         1172  +  FuncDef *pFunc = 0;             /* Function definition */
         1173  +  sqlite3_value *pVal = 0;        /* New value */
         1174  +  int rc = SQLITE_OK;             /* Return code */
         1175  +  int nName;                      /* Size of function name in bytes */
         1176  +  ExprList *pList;                /* Function arguments */
         1177  +  int i;                          /* Iterator variable */
         1178  +
         1179  +  /* If pCtx==0, then this is probably being called to to obtain an 
         1180  +  ** sqlite3_value object for the default value of a column. In that case
         1181  +  ** function expressions are not supported. Function expressions are
         1182  +  ** only supported when extracting values to compare with sqlite_stat4 
         1183  +  ** records.
         1184  +  **
         1185  +  ** It may also be that this function expression is an argument passed
         1186  +  ** to another function expression. As in "f2(...)" within the query:
         1187  +  **
         1188  +  **   SELECT * FROM tbl WHERE tbl.c = f1(0, f2(...), 1);
         1189  +  **
         1190  +  ** For now, extracting the value of "f1(...)" is not supported either.
         1191  +  */
         1192  +  if( pCtx==0 ) return SQLITE_OK;
         1193  +  
         1194  +  assert( (p->flags & (EP_TokenOnly|EP_Reduced))==0 );
         1195  +  pList = p->x.pList;
         1196  +  if( pList ) nVal = pList->nExpr;
         1197  +  nName = sqlite3Strlen30(p->u.zToken);
         1198  +  pFunc = sqlite3FindFunction(db, p->u.zToken, nName, nVal, enc, 0);
         1199  +  assert( pFunc );
         1200  +  if( (pFunc->funcFlags & SQLITE_FUNC_CONSTANT)==0 
         1201  +   || (pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL)
         1202  +  ){
         1203  +    return SQLITE_OK;
         1204  +  }
         1205  +
         1206  +  if( pList ){
         1207  +    apVal = (sqlite3_value**)sqlite3DbMallocZero(db, sizeof(apVal[0]) * nVal);
         1208  +    if( apVal==0 ){
         1209  +      rc = SQLITE_NOMEM;
         1210  +      goto value_from_function_out;
         1211  +    }
         1212  +    for(i=0; i<nVal; i++){
         1213  +      rc = sqlite3ValueFromExpr(db, pList->a[i].pExpr, enc, aff, &apVal[i]);
         1214  +      if( apVal[i]==0 ) goto value_from_function_out;
         1215  +      assert( rc==SQLITE_OK );
         1216  +    }
         1217  +  }
         1218  +
         1219  +  pVal = valueNew(db, pCtx);
         1220  +  if( pVal==0 ){
         1221  +    rc = SQLITE_NOMEM;
         1222  +    goto value_from_function_out;
         1223  +  }
         1224  +
         1225  +  memset(&ctx, 0, sizeof(ctx));
         1226  +  ctx.pOut = pVal;
         1227  +  ctx.pFunc = pFunc;
         1228  +  pFunc->xFunc(&ctx, nVal, apVal);
         1229  +  if( ctx.isError ){
         1230  +    rc = ctx.isError;
         1231  +    sqlite3ErrorMsg(pCtx->pParse, "%s", sqlite3_value_text(pVal));
         1232  +  }else{
         1233  +    sqlite3ValueApplyAffinity(pVal, aff, SQLITE_UTF8);
         1234  +    if( rc==SQLITE_OK ){
         1235  +      rc = sqlite3VdbeChangeEncoding(pVal, enc);
         1236  +    }
         1237  +    if( rc==SQLITE_OK && sqlite3VdbeMemTooBig(pVal) ){
         1238  +      rc = SQLITE_TOOBIG;
         1239  +    }
         1240  +  }
         1241  +
         1242  + value_from_function_out:
         1243  +  if( rc!=SQLITE_OK ){
         1244  +    if( pCtx==0 ) sqlite3ValueFree(pVal);
         1245  +    pVal = 0;
         1246  +  }
         1247  +  for(i=0; i<nVal; i++){
         1248  +    sqlite3ValueFree(apVal[i]);
         1249  +  }
         1250  +  sqlite3DbFree(db, apVal);
         1251  +
         1252  +  *ppVal = pVal;
         1253  +  return rc;
         1254  +}
         1255  +#else
         1256  +# define valueFromFunction(a,b,c,d,e,f) SQLITE_OK
         1257  +#endif /* defined(SQLITE_ENABLE_STAT3_OR_STAT4) */
         1258  +
  1137   1259   /*
  1138   1260   ** Extract a value from the supplied expression in the manner described
  1139   1261   ** above sqlite3ValueFromExpr(). Allocate the sqlite3_value object
  1140   1262   ** using valueNew().
  1141   1263   **
  1142   1264   ** If pCtx is NULL and an error occurs after the sqlite3_value object
  1143   1265   ** has been allocated, it is freed before returning. Or, if pCtx is not
................................................................................
  1237   1359       zVal = &pExpr->u.zToken[2];
  1238   1360       nVal = sqlite3Strlen30(zVal)-1;
  1239   1361       assert( zVal[nVal]=='\'' );
  1240   1362       sqlite3VdbeMemSetStr(pVal, sqlite3HexToBlob(db, zVal, nVal), nVal/2,
  1241   1363                            0, SQLITE_DYNAMIC);
  1242   1364     }
  1243   1365   #endif
         1366  +
         1367  +  else if( op==TK_FUNCTION ){
         1368  +    rc = valueFromFunction(db, pExpr, enc, affinity, &pVal, pCtx);
         1369  +  }
  1244   1370   
  1245   1371     *ppVal = pVal;
  1246   1372     return rc;
  1247   1373   
  1248   1374   no_mem:
  1249   1375     db->mallocFailed = 1;
  1250   1376     sqlite3DbFree(db, zVal);

Added test/analyzeF.test.

            1  +# 2015-03-12
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# Test that deterministic scalar functions passed constant arguments
           12  +# are used with stat4 data.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set ::testprefix analyzeF
           18  +
           19  +ifcapable {!stat4} {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +proc isqrt {i} { expr { int(sqrt($i)) } }
           25  +db func isqrt isqrt
           26  +
           27  +do_execsql_test 1.0 {
           28  +  CREATE TABLE t1(x INTEGER, y INTEGER);
           29  +  WITH data(i) AS (
           30  +    SELECT 1 UNION ALL SELECT i+1 FROM data
           31  +  )
           32  +  INSERT INTO t1 SELECT isqrt(i), isqrt(i) FROM data LIMIT 400;
           33  +  CREATE INDEX t1x ON t1(x);
           34  +  CREATE INDEX t1y ON t1(y);
           35  +  ANALYZE;
           36  +}
           37  +
           38  +proc str {a} { return $a }
           39  +db func str str
           40  +
           41  +# Note: tests 7 to 12 might be unstable - as they assume SQLite will
           42  +# prefer the expression to the right of the AND clause. Which of
           43  +# course could change.
           44  +#
           45  +# Note 2: tests 9 and 10 depend on the tcl interface creating functions
           46  +# without the SQLITE_DETERMINISTIC flag set.
           47  +#
           48  +foreach {tn where idx} {
           49  +  1 "x = 4 AND y = 19"     {t1x (x=?)}
           50  +  2 "x = 19 AND y = 4"     {t1y (y=?)}
           51  +  3 "x = '4' AND y = '19'" {t1x (x=?)}
           52  +  4 "x = '19' AND y = '4'" {t1y (y=?)}
           53  +  5 "x = substr('5195', 2, 2) AND y = substr('145', 2, 1)" {t1y (y=?)}
           54  +  6 "x = substr('145', 2, 1) AND y = substr('5195', 2, 2)" {t1x (x=?)}
           55  +
           56  +  7  "x = substr('5195', 2, 2+0) AND y = substr('145', 2, 1+0)" {t1y (y=?)}
           57  +  8  "x = substr('145', 2, 1+0) AND y = substr('5195', 2, 2+0)" {t1y (y=?)}
           58  +
           59  +  9  "x = str('19') AND y = str('4')" {t1y (y=?)}
           60  +  10 "x = str('4') AND y = str('19')" {t1y (y=?)}
           61  +
           62  +  11 "x = nullif('19', 0) AND y = nullif('4', 0)" {t1y (y=?)}
           63  +  12 "x = nullif('4', 0) AND y = nullif('19', 0)" {t1y (y=?)}
           64  +} {
           65  +  set res "0 0 0 {SEARCH TABLE t1 USING INDEX $idx}"
           66  +  do_eqp_test 1.$tn "SELECT * FROM t1 WHERE $where" $res
           67  +}
           68  +
           69  +do_catchsql_test 2.1 {
           70  +  SELECT * FROM t1 WHERE x = substr('145', 2, 1) AND y = func(1, 2, 3)
           71  +} {1 {no such function: func}}
           72  +do_catchsql_test 2.2 {
           73  +  UPDATE t1 SET y=y+1 WHERE x = substr('145', 2, 1) AND y = func(1, 2, 3)
           74  +} {1 {no such function: func}}
           75  +
           76  +
           77  +finish_test
           78  +