Lowest supported sqlite version?

[rujialiu]

First of all, thanks for the awesome project! I can't find any info about the lowest supported sqlite version. We're trying to integrate sqlite-vec into a rather old codebase that uses sqlite 3.35, and we don't want to risk upgrading sqlite3 version. Currently the code in 0.1.0 release doesn't compile. After some minimal changes it does compile/link successfully, but I'm a bit worried.

compile errors:

• SQLITE_INDEX_CONSTRAINT_LIMIT doesn't exist in 3.35
• a few "not all control paths return a value" since we switched on warning-as-error

link errors:

• error LNK2019: unresolved external symbol sqlite3_vtab_in referenced in function vec0CursorClear
• error LNK2019: unresolved external symbol sqlite3_vtab_in_first referenced in function vec0Filter_knn
• error LNK2019: unresolved external symbol sqlite3_vtab_in_next referenced in function vec0Filter_knn

Actually there is code to check this:

if (sqlite3_libversion_number() >= 3038000) {
      vtabIn = sqlite3_vtab_in(pIdxInfo, i, -1);
}

But unfortunately, the function sqlite3_vtab_in is still required for linking with any sqlite3 version.

BTW: I believe there are more projects that use older version of sqlite3 (several years ago). For example Unreal Engine 5's current shipped sqlite3 is still 3.31.

[1yefuwang1]

@rujialiu I think the problem here is that you are linking with an old version of SQLite that doesn't provide sqlite3_vtab_in symbols. The symbols are required because they are not guarded by any macros at build time and are used in some execution paths. So, the compiler still needs these symbols to be present.

I write a similar vector search SQLite extension https://github.com/1yefuwang1/vectorlite and ran into similar problems before. You can link against newer SQLite and still load it to an old SQLite at runtime. But sqlite3_vtab_in*() calls need to be guarded in sqlite3 version checks in the SQLite extension. Otherwise, it would crash your program.

[asg017]

@rujialiu I'll add proper support for 3.35 in the next release.

I only tested older SQLite versions using dynamic extensions, not statically compiling. I'll update to add some #define's to get around the missing SQLITE_INDEX_CONSTRAINT_LIMIT values and avoid `sqlite3_vtab_in(). The only exception would be the "control path" warnings, that may take be a bit longer to fix (I mostly develop on MacOS which doesn't emit these warnings)

Targeting 3.31 (Jan 2020) seems reasonable enough, I'll add some CI/CD tests for it as well

[rujialiu]

Thank you both! Glad to know that SQLITE_INDEX_CONSTRAINT_LIMIT and sqlite3_vtab_in() is are essential. I'll comment out relavent codes and start experimenting with it soon and update the code after the proper support is implemented.

BTW: Your vectorlite extension looks great too, though my current codebase doesn't allow me to add more dependencies like absl @1yefuwang1

[asg017]

@rujialiu the v0.1.1 release now supports statically linking sqlite-vec in SQLite versions as low as 3.31.1: https://github.com/asg017/sqlite-vec/releases/tag/v0.1.1

Try it out and let me know if it works! There should be no more errors surrounding SQLITE_INDEX_CONSTRAINT_LIMIT or sqlite3_vtab_in(). I also added a test to Github Actions so it shouldn't regress in the future. 3.31.1 is the minimum version I test for.

Although the "control path" warnings will still appear, I'll track that in #64 . If you can provide the compiler you're using and an example of what the warnings look like, that would be great!

[rujialiu]

Thanks! I've provided a patch which (hopefully) contains all such places. The patch also contains some workaround about the static linking issue.

[rujialiu]

I've tested v0.1.1, following examples/simple-c/demo.c, it got runtime check failed

        rc = sqlite3_prepare_v2(db, "CREATE VIRTUAL TABLE vec_items USING vec0(embedding float[4])", -1, &stmt, NULL);
        assert(rc == SQLITE_OK);
        rc = sqlite3_step(stmt); <--- failed here

(BTW: the vec_version() printed earlier is correct, so I assume sqlite_vec is initialized correctly?)

The exact place is line 4082:

pNew->pkIsText = pkColumnType == SQLITE_TEXT;

The error is:

Run-Time Check Failure #3 - The variable 'pkColumnType' is being used without being initialized.

There is only one place that assigns a value to pkColumnType (line 3989) but the program didn't get there because parse_vector_column returns SQLITE_OK so it continues at line 3970

However, if I added a "pkColumnType = -1" at line 3941, later this one will return SQLITE_ERROR

  rc = sqlite3_prepare_v2(db,
    "SELECT "
    "  rowid, "
    "  distance "
    "FROM vec_items "
    "WHERE embedding MATCH ?1 "
    "ORDER BY distance "
    "LIMIT 3 "
  , -1, &stmt, NULL);
  assert(rc == SQLITE_OK);

I'm still very unfamiliar with the codebase so this is pretty much everything I could do now.

If you cannot reproduce this, some brief explanation of the intended the control flow, and important variables to pay attention to, will greatly help me investigate further.

[rujialiu]

@asg017 Could you please take a look at my issue above when you have some time? BTW: I saw your iOS tracking issue. Actually we're planning to use it in iOS, but first we need to make it work with windows 10.

[asg017]

@rujialiu just published 0.1.2-alpha.1, can you give it a shot? https://github.com/asg017/sqlite-vec/releases/tag/v0.1.2-alpha.1

The control path warnings should be fixed now, and use -DSQLITE_CORE and -DSQLITE_VEC_STATIC to avoid those dllexport/sqlite3_api errors.

And for that SQL, try:

  rc = sqlite3_prepare_v2(db,
    "SELECT "
    "  rowid, "
    "  distance "
    "FROM vec_items "
    "WHERE embedding MATCH ?1 "
    "AND k = 3 "
    "ORDER BY distance "
  , -1, &stmt, NULL);
  assert(rc == SQLITE_OK);

Instead of LIMIT 3, the LIMIT 3 syntax only works on SQLite versions greater that 3.41, while k = 3 works on older versions.

[rujialiu]

@asg017 I tested 0.1.2alpha2, everything works! Thanks!

[asg017]

@rujialiu thank you for taking the time to test everything!