Request: Allow virtual tables to make use of partial indexes

I believe I've found another limitation for efficient querying of virtual tables. The xBestIndex call communicates column constraints, but it doesn't specify whether a constraint's value is known at compile time, nor pass such a compile-time value to xBestIndex.

This means that the virtual-table implementation can't make use of partial indexes in its underlying storage, because it doesn't know whether the index's predicate is satisfied.

For example, say my data store is FooCabinet, and it has a FooCabinet index* on the "cost" column where the "type" column is equal to "expense".
Consider these two queries:
	SELECT cost FROM myvtable WHERE cost > ? AND type = 'expense';
	SELECT cost FROM myvtable WHERE cost > ? AND type =?;
The first query can make use of the index; the second can't.
But in either case, my xBestIndex function is called with SQLITE_INDEX_CONSTRAINT_GT on "cost" and SQLITE_INDEX_CONSTRAINT_EQ on "type", and that's all I know. I can't tell whether the index is useable, so I can't use it.

Consider this an enhancement request to extend the sqlite3_index_info struct somehow to convey compile-time column constraint values to the extension, perhaps by adding a field
	const sqlite3_value** const constraintValues;

Thanks,

—Jens

* Obviously we are not talking about SQLite indexes here! My underlying data store has its own partial- index functionality.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users