Bug: query() silently discards results from multi-statement queries and transactions

[mysticaltech]

Bug description

query() always returns response["result"][0]["result"] — only the first statement's result. For multi-statement queries and BEGIN TRANSACTION blocks, all results after the first statement are silently discarded.

This causes silent data loss when using transactions: the server executes all statements (mutations apply), but the caller receives None or an empty result because [0] points to the BEGIN TRANSACTION acknowledgment (which has no result).

Reproduction

import asyncio
from surrealdb import AsyncSurreal

async def main():
    db = AsyncSurreal("ws://127.0.0.1:8000")
    await db.connect()
    await db.use("test", "test")
    await db.signin({"username": "root", "password": "root"})

    # Setup
    await db.query("DELETE test_tbl")
    await db.query("CREATE test_tbl:1 SET name = 'alice', status = 'pending'")

    # Multi-statement query — only first result returned
    result = await db.query("SELECT * FROM test_tbl; SELECT count() FROM test_tbl GROUP ALL")
    print("Multi-statement:", result)
    # Expected: both results
    # Actual: only the SELECT * result (first statement)

    # Transaction — returns None (BEGIN has no result)
    result = await db.query(
        'BEGIN TRANSACTION;'
        ' LET $rec = (SELECT * FROM test_tbl:1);'
        ' UPDATE test_tbl:1 SET status = "running";'
        ' COMMIT TRANSACTION;'
    )
    print("Transaction:", result)
    # Expected: the UPDATE result
    # Actual: None (response["result"][0] is the BEGIN acknowledgment)

    # Verify the UPDATE did execute server-side
    check = await db.query("SELECT * FROM test_tbl:1")
    print("Server state:", check)
    # Shows status = "running" — mutation applied, but caller never saw the result

    await db.close()

asyncio.run(main())

Root cause

In async_ws.py line 217:

return response["result"][0]["result"]  # hard-coded [0]

The server returns N result objects (one per statement), but the SDK only extracts the first. The same pattern exists in blocking_ws.py.

Impact

This is a silent failure — no error is raised, no warning logged. The server correctly executes all statements and returns all results, but the SDK discards everything after index 0. Users who rely on query() return values for transactions or multi-statement queries will get phantom writes: mutations apply server-side but the caller sees None.

In our case, this caused a job queue to leak thousands of "running" jobs that were never processed — the claim_next_job transaction successfully set status = "running" in the DB, but returned None to the worker, which assumed no job was available.

Suggested fix

query() should return all results for multi-statement queries. Options:

1. Return a list when the query contains multiple statements (breaking change to return type)
2. Return the last non-null result (least surprise, matches SQL clients)
3. At minimum, document this limitation clearly and recommend query_raw() for multi-statement use

Environment

• Python SDK: main branch @ 1ff4470e (also verified on current main)
• SurrealDB server: v3.0.0-beta.4
• Python: 3.13+/3.14

[martinschaer]

Hi @mysticaltech ! Thanks for the report. This is expected behaviour when using query. query_raw is what you should use when executing multiple statements and want to get all results, as documented here: https://surrealdb.com/docs/sdk/python/methods/query#query_raw

It's a common issue developers experience, so we need to find a better solution.