order of search for Sqlite's "IN" operator guaranteed?

Question

I'm performing an Sqlite3 query similar to

SELECT * FROM nodes WHERE name IN ('name1', 'name2', 'name3', ...) LIMIT 1

Am I guaranteed that it will search for name1 first, name2 second, etc? Such that by limiting my output to 1 I know that I found the first hit according to my ordering of items in the IN clause?

Update: with some testing it seems to always return the first hit in the index regardless of the IN order. It's using the order of the index on name. Is there some way to enforce the search order?

Answer 1

The order of the returned rows is not guaranteed to match the order of the items inside the parenthesis after IN.
What you can do is use ORDER BY in your statement with the use of the function INSTR():

SELECT * FROM nodes 
WHERE name IN ('name1', 'name2', 'name3') 
ORDER BY INSTR(',name1,name2,name3,', ',' || name || ',')
LIMIT 1

This code uses the same list from the IN clause as a string, where the items are in the same order, concatenated and separated by commas, assuming that the items do not contain commas.
This way the results are ordered by their position in the list and then LIMIT 1 will return the 1st of them which is closer to the start of the list.

Another way to achieve the same results is by using a CTE which returns the list along with an Id which serves as the desired ordering of the results, which will be joined to the table:

WITH list(id, item) AS (
  SELECT 1, 'name1' UNION ALL 
  SELECT 2, 'name2' UNION ALL 
  SELECT 3, 'name3' 
)
SELECT n.* 
FROM nodes n INNER JOIN list l
ON l.item = n.name 
ORDER BY l.id
LIMIT 1 

Or:

WITH list(id, item) AS (
  SELECT * FROM (VALUES 
    (1, 'name1'), (2, 'name2'), (3, 'name3')
  )
)
SELECT n.* 
FROM nodes n INNER JOIN list l
ON l.item = n.name 
ORDER BY l.id
LIMIT 1 

This way you don't have to repeat the list twice.