Virtual Table xBestIndex Bug: Cannot use SQLITE_INDEX_CONSTRAINT_FUNCTION with SQLITE_INDEX_CONSTRAINT_LIMIT
(1) By Alex Garcia (alexgarciaxyz) on 2023-01-03 21:48:49 [source]
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
(2) By Dan Kennedy (dan) on 2023-01-04 17:48:55 in reply to 1 [link] [source]
Thanks for reporting this. Hopefully fixed here:
https://www.sqlite.org/src/info/f38caab23bcef1df
Dan.