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 |
Timelines: | family | ancestors | descendants | both | stat4-function |
Files: | files | file ages | folders |
SHA1: |
b7f1fc26d24012e1e7c7f6b3cc0b84ad |
User & Date: | dan 2015-03-11 20:06:40.904 |
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: ff868e22ca 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: b7f1fc26d2 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: a2715b049a user: drh tags: trunk) | |
Changes
Changes to src/vdbeapi.c.
︙ | ︙ | |||
633 634 635 636 637 638 639 640 | } /* ** Return the current time for a statement */ sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context *p){ Vdbe *v = p->pVdbe; int rc; | > | | > | < | | 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 | } /* ** Return the current time for a statement */ sqlite3_int64 sqlite3StmtCurrentTime(sqlite3_context *p){ Vdbe *v = p->pVdbe; sqlite3_int64 iTime = 0; int rc; if( v && v->iCurrentTime ) return v->iCurrentTime; rc = sqlite3OsCurrentTimeInt64(p->pOut->db->pVfs, &iTime); if( rc ) return 0; if( v ) v->iCurrentTime = iTime; return iTime; } /* ** The following is the implementation of an SQL function that always ** fails with an error message stating that the function is used in the ** wrong context. The sqlite3_overload_function() API might construct ** SQL function that use this routine so that the functions will exist |
︙ | ︙ | |||
708 709 710 711 712 713 714 715 716 717 718 719 720 721 | ** Return the auxiliary data pointer, if any, for the iArg'th argument to ** the user-function defined by pCtx. */ void *sqlite3_get_auxdata(sqlite3_context *pCtx, int iArg){ AuxData *pAuxData; assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) ); for(pAuxData=pCtx->pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNext){ if( pAuxData->iOp==pCtx->iOp && pAuxData->iArg==iArg ) break; } return (pAuxData ? pAuxData->pAux : 0); } | > | 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 | ** Return the auxiliary data pointer, if any, for the iArg'th argument to ** the user-function defined by pCtx. */ void *sqlite3_get_auxdata(sqlite3_context *pCtx, int iArg){ AuxData *pAuxData; assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) ); if( pCtx->pVdbe==0 ) return 0; for(pAuxData=pCtx->pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNext){ if( pAuxData->iOp==pCtx->iOp && pAuxData->iArg==iArg ) break; } return (pAuxData ? pAuxData->pAux : 0); } |
︙ | ︙ | |||
731 732 733 734 735 736 737 738 739 740 741 742 743 744 | void (*xDelete)(void*) ){ AuxData *pAuxData; Vdbe *pVdbe = pCtx->pVdbe; assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) ); if( iArg<0 ) goto failed; for(pAuxData=pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNext){ if( pAuxData->iOp==pCtx->iOp && pAuxData->iArg==iArg ) break; } if( pAuxData==0 ){ pAuxData = sqlite3DbMallocZero(pVdbe->db, sizeof(AuxData)); if( !pAuxData ) goto failed; | > | 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 | void (*xDelete)(void*) ){ AuxData *pAuxData; Vdbe *pVdbe = pCtx->pVdbe; assert( sqlite3_mutex_held(pCtx->pOut->db->mutex) ); if( iArg<0 ) goto failed; if( pVdbe==0 ) goto failed; for(pAuxData=pVdbe->pAuxData; pAuxData; pAuxData=pAuxData->pNext){ if( pAuxData->iOp==pCtx->iOp && pAuxData->iArg==iArg ) break; } if( pAuxData==0 ){ pAuxData = sqlite3DbMallocZero(pVdbe->db, sizeof(AuxData)); if( !pAuxData ) goto failed; |
︙ | ︙ |
Changes to src/vdbemem.c.
︙ | ︙ | |||
1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 | } #else UNUSED_PARAMETER(p); #endif /* defined(SQLITE_ENABLE_STAT3_OR_STAT4) */ return sqlite3ValueNew(db); } /* ** Extract a value from the supplied expression in the manner described ** above sqlite3ValueFromExpr(). Allocate the sqlite3_value object ** using valueNew(). ** ** If pCtx is NULL and an error occurs after the sqlite3_value object ** has been allocated, it is freed before returning. Or, if pCtx is not | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 | } #else UNUSED_PARAMETER(p); #endif /* defined(SQLITE_ENABLE_STAT3_OR_STAT4) */ return sqlite3ValueNew(db); } /* ** The expression object indicated by the second argument is guaranteed ** to be a scalar SQL function. If ** ** * all function arguments are SQL literals, ** * the SQLITE_FUNC_CONSTANT function flag is set, ** * the SQLITE_FUNC_NEEDCOLL function flag is not set, and ** * this routine is being invoked as part of examining stat4 data, ** not as part of handling a default value on a column created using ** ALTER TABLE ADD COLUMN, ** ** then this routine attempts to invoke the SQL function. Assuming no ** error occurs, output parameter (*ppVal) is set to point to a value ** object containing the result before returning SQLITE_OK. ** ** Affinity aff is applied to the result of the function before returning. ** If the result is a text value, the sqlite3_value object uses encoding ** enc. ** ** If the conditions above are not met, this function returns SQLITE_OK ** and sets (*ppVal) to NULL. Or, if an error occurs, (*ppVal) is set to ** NULL and an SQLite error code returned. */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 static int valueFromFunction( sqlite3 *db, /* The database connection */ Expr *p, /* The expression to evaluate */ u8 enc, /* Encoding to use */ u8 aff, /* Affinity to use */ sqlite3_value **ppVal, /* Write the new value here */ struct ValueNewStat4Ctx *pCtx /* Second argument for valueNew() */ ){ sqlite3_context ctx; /* Context object for function invocation */ sqlite3_value **apVal = 0; /* Function arguments */ int nVal = 0; /* Size of apVal[] array */ FuncDef *pFunc = 0; /* Function definition */ sqlite3_value *pVal = 0; /* New value */ int rc = SQLITE_OK; /* Return code */ int nName; /* Size of function name in bytes */ ExprList *pList; /* Function arguments */ int i; /* Iterator variable */ /* If pCtx==0, then this is probably being called to to obtain an ** sqlite3_value object for the default value of a column. In that case ** function expressions are not supported. Function expressions are ** only supported when extracting values to compare with sqlite_stat4 ** records. ** ** It may also be that this function expression is an argument passed ** to another function expression. As in "f2(...)" within the query: ** ** SELECT * FROM tbl WHERE tbl.c = f1(0, f2(...), 1); ** ** For now, extracting the value of "f1(...)" is not supported either. */ if( pCtx==0 ) return SQLITE_OK; assert( (p->flags & (EP_TokenOnly|EP_Reduced))==0 ); pList = p->x.pList; if( pList ) nVal = pList->nExpr; nName = sqlite3Strlen30(p->u.zToken); pFunc = sqlite3FindFunction(db, p->u.zToken, nName, nVal, enc, 0); assert( pFunc ); if( (pFunc->funcFlags & SQLITE_FUNC_CONSTANT)==0 || (pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL) ){ return SQLITE_OK; } if( pList ){ apVal = (sqlite3_value**)sqlite3DbMallocZero(db, sizeof(apVal[0]) * nVal); if( apVal==0 ){ rc = SQLITE_NOMEM; goto value_from_function_out; } for(i=0; i<nVal; i++){ rc = sqlite3ValueFromExpr(db, pList->a[i].pExpr, enc, aff, &apVal[i]); if( apVal[i]==0 ) goto value_from_function_out; assert( rc==SQLITE_OK ); } } pVal = valueNew(db, pCtx); if( pVal==0 ){ rc = SQLITE_NOMEM; goto value_from_function_out; } memset(&ctx, 0, sizeof(ctx)); ctx.pOut = pVal; ctx.pFunc = pFunc; pFunc->xFunc(&ctx, nVal, apVal); if( ctx.isError ){ rc = ctx.isError; sqlite3ErrorMsg(pCtx->pParse, "%s", sqlite3_value_text(pVal)); }else{ sqlite3ValueApplyAffinity(pVal, aff, SQLITE_UTF8); if( rc==SQLITE_OK ){ rc = sqlite3VdbeChangeEncoding(pVal, enc); } if( rc==SQLITE_OK && sqlite3VdbeMemTooBig(pVal) ){ rc = SQLITE_TOOBIG; } } value_from_function_out: if( rc!=SQLITE_OK ){ if( pCtx==0 ) sqlite3ValueFree(pVal); pVal = 0; } for(i=0; i<nVal; i++){ sqlite3ValueFree(apVal[i]); } sqlite3DbFree(db, apVal); *ppVal = pVal; return rc; } #else # define valueFromFunction(a,b,c,d,e,f) SQLITE_OK #endif /* defined(SQLITE_ENABLE_STAT3_OR_STAT4) */ /* ** Extract a value from the supplied expression in the manner described ** above sqlite3ValueFromExpr(). Allocate the sqlite3_value object ** using valueNew(). ** ** If pCtx is NULL and an error occurs after the sqlite3_value object ** has been allocated, it is freed before returning. Or, if pCtx is not |
︙ | ︙ | |||
1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 | zVal = &pExpr->u.zToken[2]; nVal = sqlite3Strlen30(zVal)-1; assert( zVal[nVal]=='\'' ); sqlite3VdbeMemSetStr(pVal, sqlite3HexToBlob(db, zVal, nVal), nVal/2, 0, SQLITE_DYNAMIC); } #endif *ppVal = pVal; return rc; no_mem: db->mallocFailed = 1; sqlite3DbFree(db, zVal); | > > > > | 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 | zVal = &pExpr->u.zToken[2]; nVal = sqlite3Strlen30(zVal)-1; assert( zVal[nVal]=='\'' ); sqlite3VdbeMemSetStr(pVal, sqlite3HexToBlob(db, zVal, nVal), nVal/2, 0, SQLITE_DYNAMIC); } #endif else if( op==TK_FUNCTION ){ rc = valueFromFunction(db, pExpr, enc, affinity, &pVal, pCtx); } *ppVal = pVal; return rc; no_mem: db->mallocFailed = 1; sqlite3DbFree(db, zVal); |
︙ | ︙ |
Added test/analyzeF.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | # 2015-03-12 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # Test that deterministic scalar functions passed constant arguments # are used with stat4 data. # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix analyzeF ifcapable {!stat4} { finish_test return } proc isqrt {i} { expr { int(sqrt($i)) } } db func isqrt isqrt do_execsql_test 1.0 { CREATE TABLE t1(x INTEGER, y INTEGER); WITH data(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM data ) INSERT INTO t1 SELECT isqrt(i), isqrt(i) FROM data LIMIT 400; CREATE INDEX t1x ON t1(x); CREATE INDEX t1y ON t1(y); ANALYZE; } proc str {a} { return $a } db func str str # Note: tests 7 to 12 might be unstable - as they assume SQLite will # prefer the expression to the right of the AND clause. Which of # course could change. # # Note 2: tests 9 and 10 depend on the tcl interface creating functions # without the SQLITE_DETERMINISTIC flag set. # foreach {tn where idx} { 1 "x = 4 AND y = 19" {t1x (x=?)} 2 "x = 19 AND y = 4" {t1y (y=?)} 3 "x = '4' AND y = '19'" {t1x (x=?)} 4 "x = '19' AND y = '4'" {t1y (y=?)} 5 "x = substr('5195', 2, 2) AND y = substr('145', 2, 1)" {t1y (y=?)} 6 "x = substr('145', 2, 1) AND y = substr('5195', 2, 2)" {t1x (x=?)} 7 "x = substr('5195', 2, 2+0) AND y = substr('145', 2, 1+0)" {t1y (y=?)} 8 "x = substr('145', 2, 1+0) AND y = substr('5195', 2, 2+0)" {t1y (y=?)} 9 "x = str('19') AND y = str('4')" {t1y (y=?)} 10 "x = str('4') AND y = str('19')" {t1y (y=?)} 11 "x = nullif('19', 0) AND y = nullif('4', 0)" {t1y (y=?)} 12 "x = nullif('4', 0) AND y = nullif('19', 0)" {t1y (y=?)} } { set res "0 0 0 {SEARCH TABLE t1 USING INDEX $idx}" do_eqp_test 1.$tn "SELECT * FROM t1 WHERE $where" $res } do_catchsql_test 2.1 { SELECT * FROM t1 WHERE x = substr('145', 2, 1) AND y = func(1, 2, 3) } {1 {no such function: func}} do_catchsql_test 2.2 { UPDATE t1 SET y=y+1 WHERE x = substr('145', 2, 1) AND y = func(1, 2, 3) } {1 {no such function: func}} finish_test |