I have a virtual table where I would like to apply both a SQLITE_INDEX_CONSTRAINT_FUNCTION constraint and a SQLITE_INDEX_CONSTRAINT_LIMIT constraint for a specific query. However, it appears that SQLite only applies SQLITE_INDEX_CONSTRAINT_LIMIT under specific circumstances, and I believe it should apply for query plans that use SQLITE_INDEX_CONSTRAINT_FUNCTION.

My virtual table (source code available here), is a lightly edited version of templatevtab, which overloads the foo() function in xFindFunction and returns SQLITE_INDEX_CONSTRAINT_FUNCTION.

This query:

select *
from templatevtab
where foo(a, 20);

Calls the xBestIndex function with a single constraint on the a column with operator of 150 (SQLITE_INDEX_CONSTRAINT_FUNCTION), which is what I expect.

This query:

select *
from templatevtab
limit 20;

Calls the xBestIndex function with a single constraint with operator of 73 (SQLITE_INDEX_CONSTRAINT_LIMIT), which is what I expect.

Now, with this query:

select *
from templatevtab
where foo(a, 20)
limit 20;

I expected that the xBestIndex function would be called with 2 constraints: The first with a SQLITE_INDEX_CONSTRAINT_FUNCTION on the a column, and the second with a SQLITE_INDEX_CONSTRAINT_LIMIT operator.

However, SQLite instead only calls xBestIndex once with 1 constraint: SQLITE_INDEX_CONSTRAINT_FUNCTION on the a column.

To see this yourself, compile the gist, and run this on the SQLite CLI:

sqlite> .load ./templatevtab
sqlite> select *
   ...> from templatevtab
   ...> where foo(a, 20);
        i=0, col=0, op=150

sqlite> select *
   ...> from templatevtab
   ...> limit 20;
        i=0, col=0, op=73

sqlite> select *
   ...> from templatevtab
   ...> where foo(a, 20)
   ...> limit 20;
        i=0, col=0, op=150

The xBestIndex method in that vtab just prints out all the constraints (where i is the constraint index, col is the iColumn index, and op is the constraint operator).

I believe this is happening because of requirement #4 in sqlite3WhereAddLimit - perhaps it doesn't handle WHERE terms that are overloaded with xFindFunction?

CLI version I'm using:

SQLite 3.41.0 2022-12-27 22:46:49 e8afad630b085a9208491e0516a6a30c9cda77a20b1aa2cba49b2f44eb9fa2f8
zlib version 1.2.11
clang-13.1.6