'no query solution' error
(1.1) By curmudgeon on 2020-04-05 15:23:31 edited from 1.0 [source]
Can someone explain the above error which I get when I use 'indexed by' along with any valid index. Error seems to depend on the column list but it isn't mentioned on the indexed by documentation. sqlite> create table t(a,b); sqlite> create index ndx on t(b); sqlite> select a from t indexed by ndx; Error: no query solution sqlite> select b from t indexed by ndx; sqlite> select * from t indexed by ndx; Error: no query solution sqlite> I'm using 3.31.0
(2) By Igor Tandetnik (itandetnik) on 2020-04-05 15:52:27 in reply to 1.1 [link] [source]
The documentation does state: "If the query optimizer is unable to use the index specified by the INDEX BY clause, then the query will fail with an error."
Obviously, it is not possible to select column
a from an index on
(3.1) By curmudgeon on 2020-04-05 16:12:54 edited from 3.0 in reply to 2 [link] [source]
I see what you're saying Igor. For 'select *' index ndx has no information on a.
This is documented. You will have to look in the documentation to find that documentation but it is documented, and it is also straightforward pure English.
You are being told that the query as you have phrased DOES NOT have a solution where the index you have requested to be used is used. (Note that this is an entirely different thing from saying that "no solution can be generated using your artificial constraint:).
Take for example the first query:
select a from t indexed by ndx;
The query is select a from t;. When all the myriad of possible ways to solve this query is computed there is no solution in which ndx is used. Hence you get the error message to that effect. Now then if the query were select a from t order by b; then there would indeed be a solution that used ndx to avoid a sort and your query would use that solution even if it were not the optimal solution. Similarly select a from t not indexed order by b would force the choice of the table scan and sort rather than the use of the index. However, in order to prefer a particular solution that solution must be one of the available solutions to the question stated.
To be more clear: indexed by does NOT mean to go out and generate a solution to the query using the specified index, it means that where there is a choice between solutions to a query, one or more of which use the requested index, then prefer those solutions over solution that do no use that index.