SQLite扩展vector_distance参数类型异常及行数关联问题咨询

开发的自定义函数 #

1. vector_from_json(input_json_text) #

解析输入的JSON文本，返回BLOB类型的向量数据，C++实现代码如下：

void VectorFromJson(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  if (argc != 1) {
    std::string err = absl::StrFormat(
        "vector_from_json expects 1 argument but %d provided", argc);
    sqlite3_result_error(ctx, err.c_str(), -1);
    return;
  }

  if (sqlite3_value_type(argv[0]) != SQLITE_TEXT) {
    sqlite3_result_error(ctx, "vector_from_json expects a JSON string", -1);
    return;
  }

  std::string_view json_str(
      reinterpret_cast<const char *>(sqlite3_value_text(argv[0])),
      sqlite3_value_bytes(argv[0]));

  auto vector = vectorlite::Vector::FromJSON(json_str);
  if (!vector.ok()) {
    std::string err = absl::StrFormat("Failed to parse vector due to: %s",
                                      vector.status().message());
    sqlite3_result_error(ctx, err.c_str(), -1);
    return;
  }

  sqlite3_result_blob(ctx, vector->ToBlob().data(), vector->ToBlob().size(),
                      SQLITE_TRANSIENT);
  return;
}

2. vector_distance(vector_blob1, vector_blob2, distance_type_text) #

计算两个BLOB类型向量的距离，返回double类型结果，C++实现代码如下：

void VectorDistance(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  if (argc != 3) {
    std::string err = absl::StrFormat(
        "vector_distance expects 3 arguments but %d provided", argc);
    sqlite3_result_error(ctx, err.c_str(), -1);
    return;
  }

  if (sqlite3_value_type(argv[0]) != SQLITE_BLOB ||
      sqlite3_value_type(argv[1]) != SQLITE_BLOB) {
    sqlite3_result_error(ctx, "vectors_distance expects vectors of type blob",
                         -1);
    return;
  }

  if (sqlite3_value_type(argv[2]) != SQLITE_TEXT) {
    sqlite3_result_error(
        ctx, "vectors_distance expects space type of type text", -1);
    return;
  }

  std::string_view space_type_str(
      reinterpret_cast<const char *>(sqlite3_value_text(argv[2])),
      sqlite3_value_bytes(argv[2]));
  auto distance_type = vectorlite::ParseDistanceType(space_type_str);
  if (!distance_type.has_value()) {
    std::string err =
        absl::StrFormat("Failed to parse space type: %s", space_type_str);
    sqlite3_result_error(ctx, err.c_str(), -1);
    return;
  }

  std::string_view v1_str(
      reinterpret_cast<const char *>(sqlite3_value_text(argv[0])),
      sqlite3_value_bytes(argv[0]));

  auto v1 = vectorlite::Vector::FromBlob(v1_str);
  if (!v1.ok()) {
    std::string err = absl::StrFormat("Failed to parse 1st vector due to: %s",
                                      v1.status().message());
    sqlite3_result_error(ctx, err.c_str(), -1);
    return;
  }

  std::string_view v2_str(
      reinterpret_cast<const char *>(sqlite3_value_text(argv[1])),
      sqlite3_value_bytes(argv[1]));
  auto v2 = vectorlite::Vector::FromBlob(v2_str);
  if (!v2.ok()) {
    std::string err = absl::StrFormat("Failed to parse 2nd vector due to: %s",
                                      v2.status().message());
    sqlite3_result_error(ctx, err.c_str(), -1);
    return;
  }

  auto distance = vectorlite::Distance(*v1, *v2, *distance_type);
  if (!distance.ok()) {
    sqlite3_result_error(ctx, absl::StatusMessageAsCStr(distance.status()), -1);
    return;
  }
  sqlite3_result_double(ctx, static_cast<double>(*distance));
  return;
}

问题复现 #

执行以下SQL时出现类型异常：

-- 加载扩展
.load 'path-to-my-extension.so'

CREATE TABLE vectors (
    id PRIMARY KEY,
    vector BLOB NOT NULL
);

-- 插入两行数据
INSERT INTO vectors (id, vector)
VALUES 
    (0, vector_from_json('[1,2,3]')),
    (1, vector_from_json('[2,3,4]'));

-- 顶层调用时，vector_distance运行正常
select vector_distance(vector_from_json('[1,2,3]'), vector_from_json('[1,2,3]'), 'cosine')

-- 非顶层调用时，vector_distance报错第二个参数为TEXT而非BLOB（字节数符合12字节的3个float32值）
SELECT id,vector_distance(vector, vector_from_json('[2,3,4]'), 'cosine') AS distance
FROM vectors ;

-- 删除一行后，表中仅存一行时，同一查询运行正常
DELETE FROM vectors where id = 0;
SELECT id,vector_distance(vector, vector_from_json('[2,3,4]'), 'cosine') AS distance FROM vectors;

疑问 #

1. 为何vectors表仅一行时查询正常，多行时异常？
2. 为何多行时，vector_from_json返回的BLOB值会以TEXT类型传入vector_distance？

问题原因与解决方案 #

核心原因 #

这是SQLite的常量折叠优化导致的类型丢失：

• 当表中有多行数据时，SQLite会提前计算查询中的常量表达式（如vector_from_json('[2,3,4]')）并缓存结果。如果缓存的BLOB内容恰好可被解析为合法文本（无不可打印字符），SQLite会自动将其标记为TEXT类型，而非保留原始BLOB类型。
• 当表中仅一行数据时，SQLite会跳过常量折叠优化，逐行计算时直接调用vector_from_json，此时返回的BLOB类型会被正确保留，因此查询正常。

解决方案 #

1. 显式强制类型转换

在调用vector_distance时，将vector_from_json的结果强制转为BLOB：

SELECT id,vector_distance(vector, CAST(vector_from_json('[2,3,4]') AS BLOB), 'cosine') AS distance
FROM vectors ;

2. 明确函数返回类型注册

注册vector_from_json时，指定返回类型为SQLITE_BLOB，同时添加SQLITE_DETERMINISTIC标记（表明函数输入相同则输出相同），避免SQLite自动推导类型：

sqlite3_create_function(
    db,
    "vector_from_json",
    1,
    SQLITE_UTF8 | SQLITE_DETERMINISTIC,
    nullptr,
    VectorFromJson,
    nullptr,
    nullptr,
    SQLITE_BLOB  -- 明确指定返回类型
);

3. 放宽类型检查逻辑

在vector_distance中，允许参数为TEXT类型（因为SQLite中TEXT和BLOB底层存储逻辑一致，仅类型标记不同），修改类型检查代码：

// 替换原有的BLOB类型检查
int type0 = sqlite3_value_type(argv[0]);
int type1 = sqlite3_value_type(argv[1]);
if ((type0 != SQLITE_BLOB && type0 != SQLITE_TEXT) ||
    (type1 != SQLITE_BLOB && type1 != SQLITE_TEXT)) {
    sqlite3_result_error(ctx, "vector_distance expects vectors of type BLOB or TEXT", -1);
    return;
}

内容的提问来源于stack exchange，提问作者Allen Wang