Bug: re-executed parameterized query returns the first call's rows (joins, sorts, OPTIONAL MATCH, UNION)

[zahariash]

Ladybug version

0.20.0 and 0.20.1. Clean on 0.18.1, 0.19.0 and 0.19.1.

What operating system are you using?

Linux x86-64, Python 3.10–3.14.

What happened?

Re-executing the same parameterized query string returns the first call's rows.
No error is raised and the rows are well-formed, so callers cannot detect it.
Three nodes with ids 1, 2, 3:

Q = "MATCH (a:N)-[:E]->(b:M) WHERE a.id = $v RETURN b.id"
await conn.execute(Q, {"v": 1})   # 1
await conn.execute(Q, {"v": 2})   # 1 — expected 2
await conn.execute(Q, {"v": 3})   # 1 — expected 3

shape (same three parameters)	0.19.1	0.20.1
single-table scan — any parameter type (INT, STRING, FLOAT[]); also IN, OR, DISTINCT, CASE, UNWIND, WITH projections, aggregates / GROUP BY	[1, 2, 3]	[1, 2, 3]
ORDER BY / SKIP	[1, 2, 3]	[1, 1, 1]
traversal (1-hop, var-length, shortest path), OPTIONAL MATCH	[1, 2, 3]	[1, 1, 1]
LIMIT, UNION ALL — first call right, later calls return no rows	[1, 2, 3]	[1, None, None]

Two observations localise it: GROUP BY alone is correct but GROUP BY plus
ORDER BY is stale, so the sort is the trigger; and a cartesian product of two
scans is correct while a relationship join is not, so it is the join operator,
not touching two tables. EXISTS { } subqueries are stale the same way, and
CALL QUERY_VECTOR_INDEX / QUERY_FTS_INDEX are affected only because they are
top-k.

It is per underlying connection. With AsyncConnection(max_concurrent_queries=N)
under concurrent load exactly N calls are correct, one per pooled connection;
sequential awaits reuse one connection, so only the first is. A sync
Connection behaves the same.

A PreparedStatement is correct, but async callers cannot use one:
AsyncConnection.execute(PreparedStatement, ...) raises UnboundLocalError: cannot access local variable 'conn_index'. With separate prepare + execute
deprecated in 0.20.1, and
the docs telling
Python users to pass parameters straight to execute(), the affected form is
the recommended one.

Looks like the sibling of #841, which fixed per-execution state surviving in
cached plans for the aggregate operators — those now pass; sort, hash join,
union, optional-match and subquery appear to carry the same defect. Not
bisected.

This may already be fixed on main and I could not check. #870 landed after
0.20.1 was cut, and its second fix — ResultCollector::prepareForReuse()
clobbering live results so that "queries returned other queries' rows or empty
tables" — sounds like this. 0.20.1 does not contain it: re-executing
LOAD FROM df RETURN * still returns 3, 0, 0 rows there, which is #870's
first symptom. With no nightly after 0.20.1 I have no build of main to test, so
this is reported against the two released versions. If #870 covers it, please
close — the reproducer will confirm either way.

Flagging it because #876 states that cached physical-plan reuse "remains enabled
for read-only parameterized statements", and every shape above is read-only.

Are there known steps to reproduce?

Self-contained PEP 723 script — uv run stale_rows_reexecuted_query.py
needs nothing but uv. Exits 1 when the bug reproduces.

#!/usr/bin/env -S uv run --script
# /// script
# requires-python = ">=3.10,<3.15"
# dependencies = ["ladybug==0.20.1"]
# ///
"""ladybug 0.20.0/0.20.1: re-executing a parameterized query returns the first
call's rows whenever the plan contains a join, sort, OPTIONAL MATCH, UNION or
subquery.  Clean: 0.18.1, 0.19.0, 0.19.1.

Exits 1 when the bug reproduces.
"""
import asyncio
import os
import sys
import tempfile

import ladybug as kuzu

PARAMS = [1, 2, 3]
CASES = [
    ("scan only",        "MATCH (n:N) WHERE n.id = $v RETURN n.id",                     PARAMS),
    ("ORDER BY",         "MATCH (n:N) WHERE n.id = $v RETURN n.id ORDER BY n.id",       PARAMS),
    ("LIMIT",            "MATCH (n:N) WHERE n.id = $v RETURN n.id LIMIT 1",             PARAMS),
    ("traversal",        "MATCH (a:N)-[:E]->(b:M) WHERE a.id = $v RETURN b.id",         PARAMS),
    ("OPTIONAL MATCH",   "MATCH (n:N) WHERE n.id = $v "
                         "OPTIONAL MATCH (n)-[:E]->(m) RETURN n.id",                    PARAMS),
    ("UNION ALL",        "MATCH (n:N) WHERE n.id = $v RETURN n.id "
                         "UNION ALL MATCH (n:N) WHERE n.id = $v RETURN n.id",           PARAMS),
]


async def main() -> int:
    db = kuzu.Database(os.path.join(tempfile.mkdtemp(), "db"))
    conn = kuzu.AsyncConnection(db, max_concurrent_queries=1)
    await conn.execute("CREATE NODE TABLE N(id INT64, PRIMARY KEY(id));")
    await conn.execute("CREATE NODE TABLE M(id INT64, PRIMARY KEY(id));")
    await conn.execute("CREATE REL TABLE E(FROM N TO M);")
    for i in PARAMS:
        await conn.execute(f"CREATE (:N {{id: {i}}});")
        await conn.execute(f"CREATE (:M {{id: {i}}});")
        await conn.execute(f"MATCH (a:N {{id:{i}}}),(b:M {{id:{i}}}) CREATE (a)-[:E]->(b);")

    print(f"ladybug {kuzu.__version__}")
    stale = []
    for label, query, expected in CASES:
        got = []
        for v in PARAMS:
            result = await conn.execute(query, {"v": v})
            got.append(result.get_next()[0] if result.has_next() else None)
        ok = got == expected
        if not ok:
            stale.append(label)
        print(f"  {'ok   ' if ok else 'STALE'} {label:15} got={got}  expected={expected}")

    if stale:
        print(f"\nBUG REPRODUCED: {', '.join(stale)}")
        return 1
    print("\nclean on this version")
    return 0


sys.exit(asyncio.run(main()))