CODEWITHSUNDEEP
sqlsqliteselectindexing
SparkyNZ
SparkyNZ

Reputation: 6676

SQLite "INDEXED BY" not working in SELECT

I have a Tune table which consists of an _id field and an MD5 field.. and other fields which are irrelevant to this question. When I do the following statement I get my MD5 value back:

SELECT MD5 FROM Tune WHERE _id='5'

I have an index, ITUNEID which I try to use to speed up the locating of a tune record:

CREATE INDEX ITUNEID ON Tune (_id)

When I use the following statement I get a "No query solution" error.

SELECT MD5 FROM Tune INDEXED BY ITUNEID WHERE _id='5'

What is wrong with the above statement?

I forgot to mention that _id is the primary key. Is that what the problem is? Is it illegal to specify the usage of an index where the primary key is concerned?

Upvotes: 3

Views: 1116

Answers (1)

M. Stefański
M. Stefański

Reputation: 359

I was having the same problem - but with additional context. And sadly, still relevant in year 2021.

Error sqlite3.OperationalError: no query solution was only raised in Python (in my case 3.8.6 so SQLite 3.32.3) but not in DB Browser for SQLite 3.12.1 (SQLite 3.33.0).

So I checked with newer Python version (3.9.2 - SQLite 3.34.0) and the problem was gone.

I seems that this problem exists only on SQLite below 3.33.0.

Upvotes: 1

Related Questions