SQLite Forum

Custom error messages for required table-valued function parameter
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".


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.