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;
}

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.

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.

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

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.

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

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?

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.

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.