Custom error messages for required table-valued function parameter
(1) By David Matson (dmatson) on 2021-07-08 00:49:13 [link] [source]
I have a number of table-valued functions that require one or more parameters, and I'd like to give a helpful error message if a required parameter is not specified. I've tried setting sqlite3_vtab*'s zErrMsg, but it's ignored when returning SQLITE_CONSTRAINT, which is what the documentation says should be returned for this case. (If I return anything else, it makes the query planner unhappy with at least some queries involving this function.)
Specifically, the docs say:
The SQLITE_CONSTRAINT return is useful for table-valued functions that have required parameters. If the aConstraint[].usable field is false for one of the required parameter, then the xBestIndex method should return SQLITE_CONSTRAINT.
(from section 2.3.3)
Below is sample source that reproduces the problem. It shows a nice error message when the parameter passed is wrong, but only a generic "no query solution" when the required parameter is missing. (Near the end, change "SELECT count() FROM testvtab(NULL);" to "SELECT count() FROM testvtab();" to see the case where a custom error message cannot be provided back to the application.)
Could SQLite add support for setting the error message when all solutions fail with SQLITE_CONSTRAINT? I recognize there are multiple possible invocations to xBestIndex in this case, and it would have to choose one of them (say, the first or the last that failed with SQLITE_CONSTRAINT), but which one wouldn't matter for my purposes.
Thanks,
David
#include <assert.h> #include <stdbool.h> #include <stdio.h> #include <string.h> #include "sqlite3.h" enum test_vtab_column { test_vtab_column_value = 0, test_vtab_column_source }; static int testVTabConnect(sqlite3* connection, void* pAux, int argc, const char* const* argv, sqlite3_vtab** ppVtab, char** pzErr) { (void)pAux; (void)argc; (void)argv; sqlite3_vtab* table = (sqlite3_vtab*)sqlite3_malloc(sizeof(*table)); if (table == NULL) { return SQLITE_NOMEM; } memset(table, 0, sizeof(*table)); int rc = sqlite3_declare_vtab(connection, "CREATE TABLE x(" "Value INTEGER PRIMARY KEY, " "Source HIDDEN INTEGER NOT NULL);"); if (rc != SQLITE_OK) { sqlite3_free(table); const char* connectionError = sqlite3_errmsg(connection); size_t errorLength = strlen(connectionError); char* errorMessageCopy = (char*)sqlite3_malloc64((sqlite3_uint64)errorLength + 1); if (errorMessageCopy == NULL) { return SQLITE_NOMEM; } strncpy_s(errorMessageCopy, errorLength + 1, connectionError, errorLength); *pzErr = errorMessageCopy; return rc; } *ppVtab = table; return SQLITE_OK; } static int testVTabDisconnect(sqlite3_vtab* pVtab) { sqlite3_free(pVtab); return SQLITE_OK; } static bool try_set_error_message(sqlite3_vtab* table, const char* message) { size_t length = strlen(message); char* copy = (char*)sqlite3_malloc64((sqlite3_uint64)length + 1); if (copy == NULL) { return false; } strncpy_s(copy, length + 1, message, length); sqlite3_free(table->zErrMsg); table->zErrMsg = copy; return true; } static int testVTabBestIndex(sqlite3_vtab* tab, sqlite3_index_info* pIdxInfo) { int sourceDataConstraintIndex = -1; for (int index = 0; index < pIdxInfo->nConstraint; ++index) { struct sqlite3_index_constraint* constraint = &pIdxInfo->aConstraint[index]; if (!constraint->usable) { continue; } if (constraint->iColumn == test_vtab_column_source && constraint->op == SQLITE_INDEX_CONSTRAINT_EQ) { sourceDataConstraintIndex = index; } } if (sourceDataConstraintIndex == -1) { if (!try_set_error_message(tab, "testvtab requires a Source value")) { return SQLITE_NOMEM; } return SQLITE_CONSTRAINT; } if (pIdxInfo->nOrderBy == 1 && pIdxInfo->aOrderBy[0].iColumn == test_vtab_column_value && pIdxInfo->aOrderBy[0].desc == 0) { pIdxInfo->orderByConsumed = 1; } pIdxInfo->idxNum = 0; pIdxInfo->idxStr = NULL; pIdxInfo->aConstraintUsage[sourceDataConstraintIndex].argvIndex = 1; pIdxInfo->aConstraintUsage[sourceDataConstraintIndex].omit = 1; pIdxInfo->estimatedCost = 1; return SQLITE_OK; } struct test_vtab_cursor { sqlite3_vtab_cursor base; // This base struct must come first. sqlite_int64 rowid; sqlite_int64 source; }; typedef struct test_vtab_cursor test_vtab_cursor; static int testVTabOpen(sqlite3_vtab* p, sqlite3_vtab_cursor** ppCursor) { (void)p; test_vtab_cursor* cursor = (test_vtab_cursor*)sqlite3_malloc(sizeof(*cursor)); if (cursor == NULL) { return SQLITE_NOMEM; } memset(cursor, 0, sizeof(*cursor)); *ppCursor = &cursor->base; return SQLITE_OK; } static int testVTabClose(sqlite3_vtab_cursor* cur) { test_vtab_cursor* cursor = (test_vtab_cursor*)cur; sqlite3_free(cursor); return SQLITE_OK; } static int testVTabFilter(sqlite3_vtab_cursor* pVtabCursor, int idxNum, const char* idxStr, int argc, sqlite3_value** argv) { assert(idxNum == 0); (void)idxNum; assert(idxStr == NULL); (void)idxStr; assert(argc == 1); (void)argc; test_vtab_cursor* cursor = (test_vtab_cursor*)pVtabCursor; sqlite3_vtab* table = cursor->base.pVtab; sqlite3_value* arg = argv[0]; if (sqlite3_value_type(arg) != SQLITE_INTEGER) { return try_set_error_message(table, "testvtab requires a Source value that is an INTEGER") ? SQLITE_ERROR : SQLITE_NOMEM; } cursor->rowid = 0; cursor->source = sqlite3_value_int64(arg); return SQLITE_OK; } static int testVTabNext(sqlite3_vtab_cursor* cur) { test_vtab_cursor* cursor = (test_vtab_cursor*)cur; ++(cursor->rowid); return SQLITE_OK; } static int testVTabEof(sqlite3_vtab_cursor* cur) { test_vtab_cursor* cursor = (test_vtab_cursor*)cur; return cursor->rowid < cursor->source ? 0 : 1; } static int testVTabColumn(sqlite3_vtab_cursor* cur, sqlite3_context* ctx, int i) { test_vtab_cursor* cursor = (test_vtab_cursor*)cur; assert(i <= test_vtab_column_source); switch (i) { case test_vtab_column_value: sqlite3_result_int64(ctx, cursor->rowid); return SQLITE_OK; default: assert(i == test_vtab_column_source); sqlite3_result_int64(ctx, cursor->source); return SQLITE_OK; } } static int testVTabRowid(sqlite3_vtab_cursor* cur, sqlite_int64* pRowid) { test_vtab_cursor* cursor = (test_vtab_cursor*)cur; *pRowid = cursor->rowid; return SQLITE_OK; } static sqlite3_module testVTabModule = { 3, // iVersion NULL, // xCreate testVTabConnect, // xConnect testVTabBestIndex, // xBestIndex testVTabDisconnect, //xDisconnect NULL, // xDestroy testVTabOpen, //int xOpen testVTabClose, //int xClose testVTabFilter, //int xFilter testVTabNext, // int xNext testVTabEof, // int xEof testVTabColumn, // xColumn testVTabRowid, // xRowid NULL, // xUpdate NULL, // xBegin NULL, // xSync NULL, // xCommit NULL, // xRollback NULL, // xFindFunction NULL, // xRename NULL, // xSavepoint NULL, // xRelease NULL, // xRollbackTo NULL, // xShadowName }; int main() { int rc = 0; int rcCleanup = 0; int rcCurrentCleanup = 0; sqlite3* db = NULL; sqlite3_stmt* stmt = NULL; rc = sqlite3_open("D:\\test.db", &db); if (rc) { goto cleanup; } rc = sqlite3_create_module_v2(db, "testvtab", &testVTabModule, NULL, NULL); if (rc) { goto cleanup; } rc = sqlite3_prepare_v2(db, "SELECT count(*) FROM testvtab(NULL);", -1, &stmt, NULL); if (rc) { goto cleanup; } rc = sqlite3_step(stmt); if (rc != SQLITE_ROW) { goto cleanup; } printf("%i\n", sqlite3_column_int(stmt, 0)); rc = sqlite3_step(stmt); if (rc != SQLITE_DONE) { goto cleanup; } rc = SQLITE_OK; cleanup: if (rc && db) { fprintf(stderr, "%s\n", sqlite3_errmsg(db)); } if (stmt) { rcCurrentCleanup = sqlite3_finalize(stmt); if (rcCurrentCleanup && !rcCleanup) { rcCleanup = rcCurrentCleanup; } } if (db) { rcCurrentCleanup = sqlite3_close_v2(db); if (rcCurrentCleanup && !rcCleanup) { rcCleanup = rcCurrentCleanup; } } if (rcCleanup) { return rcCleanup; } return rc; }
(2) By Max (Maxulite) on 2021-07-08 12:35:26 in reply to 1 [link] [source]
I don't know whether the developers took your suggestion to the future features, but currently you may switch to a different method in order to see your messages.
the SQLITE_CONSTRAINT result as a possible outcome of xBestIndex appeared in Sqlite 3.26.00. But before that one only could provide a very expensive cost for any non-desirable constrained suggestion and report SQLITE_ERROR in the xFilter if no required constraints were provided. Actually I welcomed SQLITE_CONSTRAINT, but I already had virtual tables working only when the required constrained were provided so my current code checks for the version (3.26.00) and works either old or new way. And if you switch to this older method, you should see your error messages set with zErrMsg in the xFilter with SQLITE_ERROR result.
(3.1) By Dan Kennedy (dan) on 2021-07-08 18:25:39 edited from 3.0 in reply to 1 [link] [source]
The SQLITE_CONSTRAINT return is useful for table-valued functions that have required parameters. If the aConstraint[].usable field is false for one of the required parameter, then the xBestIndex method should return SQLITE_CONSTRAINT.
You should return SQLITE_CONSTRAINT if the required parameter is present in the aConstraint[] array but with usable=0. No need for an error message in this case - some other invocation of xBestIndex will have usable=1 for the required parameter and the vtable will be able to proceed. If the required parameter is not present in aConstraint[] at all, return SQLITE_ERROR and an error message.
Dan.
(4) By David Matson (dmatson) on 2021-07-08 22:48:19 in reply to 3.1 [link] [source]
I double-checked back in my repo's history and confirmed I used to have exactly that behavior (SQLITE_CONSTRAINT when present but unusable; SQLITE_ERROR otherwise), but I had to change it for some complex query cases - returning SQLITE_ERROR caused those queries to fail. I think it had to do with left joins when this function might have no input available or something like that.
If returning SQLITE_ERROR for a missing required parameter is expected to work in all cases, let me know, and I can repro the problem I was having with that approach.
Thanks,
David
(8) By Dan Kennedy (dan) on 2021-07-09 11:34:31 in reply to 4 [link] [source]
If returning SQLITE_ERROR for a missing required parameter is expected to work in all cases, let me know, and I can repro the problem I was having with that approach.
I think that's the right approach. Code would be great, but we probably only need the virtual table schema (whatever is passed to sqlite3_declare_vtab()), the SQL query, and the details of which parameters are required to look into the problem.
I think it had to do with left joins when this function might have no input available or something like that.
That the tricky cases have to do with LEFT JOIN does sound quite plausible..
Dan.
(9) By David Matson (dmatson) on 2021-07-15 04:01:22 in reply to 8 [link] [source]
I changed the code back to use SQLITE_ERROR normally and SQLITE_CONSTRAINT only for the "present but unusable" case. None of the saved queries I had triggered the original problem, and none of the complex queries I came up with did either. Perhaps I was mistaken, or can't remember the right query to trigger this case, or any bug in the query planner has since been fixed.
I'll leave the code in the new state and see if we encounter this problem again, but unless that happens, please consider this report resolved.
Thanks,
David
(5.1) By Max (Maxulite) on 2021-07-09 07:42:18 edited from 5.0 in reply to 3.1 [source]
I always thought that the xBestIndex invocations are made in no particular order and sqlite even free to repeat them if it wishes so even if some constraint case was not sufficient for me, I still can wait for a better one following. That's why the answer in my tables always either SQLITE_OK or SQLITE_CONSTRAINT (after version 3.26.00) and no SQLITE_ERROR since the latter will interrupt the variants enumerations. But you explanations implicitly assumes some possibly documented ordering logic allowing one to detect in a xBestIndex invocation that there won't be better one for particular column set and one is safe to fire SQLITE_ERROR and break the loop. Can you confirm this?
(6) By Gunter Hick (gunter_hick) on 2021-07-09 08:48:20 in reply to 5.1 [link] [source]
AFAICT SQLite builds an array of all possible constraints based on the query and will then set the "usable" field to reflect those available for the specific query plan.
There is no specific order documented, so even if a heuristic can be applied, it may well become unusable due to a changes in the implementation. the xBestIndex function therefore cannot "wait for a better opportunity".
Consider:
SELECT .. FROM a JOIN b ON (a.x == b.x) WHERE a.x == 5;
The constraint array for table a will contain 2 identical constraints for equality on field x, one from the JOIN condition and one from the WHERE clause.
For table a on the LHS, one of them will not be usable (which one is determined by implemenation details); for table a on the RHS, BOTH will be usable and the BestIndex function cannot tell which is which.
For table b, there is only one entry, which is usable only for the RHS.
Assuming both virtual tables implement indices starting with field x, this will strongly favor table a on the LHS. (search X search vs. scan X search)
Consider the logically equivalent:
SELECT .. FROM a JOIN b ON (a.x == b.x) WHERE b.x == 5;
For the reasons stated above, this will strongly favor table b on the LHS.
SQLite currently does not recognize that a.x == b.x AND a.x == 5 implies b.x == 5, so the optimal comparison (search a X search b vs. search b X search a) is never made. It is up to the programmer to guess which plan is actually superior and formulate the constraints accordingly
In the case of multiple identical usable constraints, I am not sure if it is actually irrelevant which one of them is marked as suitable; nor if marking both/all of them can possibly lead to conflicting constraints in a three or more way join.
(7) By Max (Maxulite) on 2021-07-09 09:22:42 in reply to 6 [link] [source]
AFAICT SQLite builds an array of all possible constraints based on the query and will then set the "usable" field to reflect those available for the specific query plan.
Sounds reasonable.I just read the Dan's reply and one phrase in it
If the required parameter is not present in aConstraint[] at all, return SQLITE_ERROR and an error message.
indeed assumes that the array is fixed and only "usable" fields are changing. If it's the case then one really can compare the array set (not looking at the usable field) with the required set and return the error immediately if they don't match. But the docs also contains the phrase
While compiling a single SQL query, the SQLite core might call xBestIndex multiple times with different settings in sqlite3_index_infoand this implies (at least for me) that the column set may change during a single xBestIndex session (multiply calls), so one can not rely on this assumption
(10) By Richard Hipp (drh) on 2021-07-15 11:03:52 in reply to 1 [link] [source]
To enforce one or more required parameters for a table-valued function in the
current code, I think you should have xBestIndex return SQLITE_OK with a very
large
estimatedCost if the parameters are missing, together with an
idxNum
and/or
idxStr that indicates that the parameters are missing. Then, raise
an error in xFilter if it ever sees an
idxNum or
idxStr that indicates
missing parameters.
This is suboptimal in that it delays the error until run-time. The error might not even occur if a WHERE clause constraint prevents the table-valued function from ever being referenced. In a UNION ALL query, you might get several rows of output before the error is raised. Better solutions will be forthcoming in a future release.
(11) By Richard Hipp (drh) on 2021-07-16 17:27:12 in reply to 1 [link] [source]
The preferred technique for enforcing required arguments for table-valued functions is now documented in the version 3.37.0 draft documentation. There is nothing new in SQLite to provide this capability - only the documentation has been changed. So the technique described should work just as well in historical versions of SQLite as it does in (currently unreleased) 3.37.0.
See the current documentation draft for details.
Note that the loadable-extension that implements the generate_series() table-valued function has been updated to make its first parameter required, in order to illustrate the techniques described above. The changes to generate_series() are not in the current release and will appear with version 3.37.0.