SQL logic error when using rusqlite with bundled-sqlcipher feature in Rust version of sqlite-vec

[AngelLiang]

Hello,

Firstly, I would like to express my admiration for your work on the sqlite-vec library. It is an excellent tool that simplifies many complex tasks.

However, I've recently encountered an issue with the Rust version of sqlite-vec. When using the rusqlite database tool with the bundled-sqlcipher feature, I'm experiencing an SQL logic error. This error occurs when I try to execute the following SQL query:

SELECT rowid, distance
FROM vec_items
WHERE embedding MATCH ?1
ORDER BY distance
LIMIT 10;

Despite various attempts, I am unable to resolve this issue. Here are the dependencies I am using in the project:

[dependencies]
sqlite-vec= {version="0.0.1-alpha.10"}
rusqlite = {version="0.31.0", features= ["bundled-sqlcipher"] }

This issue has been a bit of a stumbling block and I'm hopeful that with your expertise and experience, you might be able to provide a solution.

Thank you once again for your excellent work on sqlite-vec. I look forward to your response.

Best regards,

AngelLiang

[asg017]

Hey thanks for the report!

Try this SQL instead:

SELECT rowid, distance
FROM vec_items
WHERE embedding MATCH ?1
  AND k = 10
ORDER BY distance;

Explanation: The embedding MATCH? and LIMIT 10 syntax only works with SQLite version >= 3.42, because of a bug in older SQLite versions. I show that syntax in the docs bc it's prettier, but for older SQLite version, you'll need to add a k = 10 constraint instead of LIMIT 10. I might change this in the docs, still trying to figure out whats best

I'm guess the sqlcipher version is a bit older, and k = 10 should solve your issue. Let me know if it doesnt!

[AngelLiang]

Thank you so much, it worked!

I've checked the versions of the dependencies below:

rusqlite = {version="0.31.0", features= ["bundled"] }

The version of SQLite is 3.45.0.

However, when using the bundled-sqlcipher feature of rusqlite

rusqlite = {version="0.31.0", features= ["bundled-sqlcipher"] }

The version of SQLite is 3.39.4.