Spanner silently dropping rows when making large query

[trevor-pope]

Environment details

• OS type and version:
• Python version: 3.11.0
• pip version: pip 22.3.1
• google-cloud-spanner version: 3.27.0

Code example

import uuid
from google.cloud import spanner

instance_id = 'my-instance'
database_id = 'my-database'
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)

database = instance.database(
    database_id,
    ddl_statements=[
        """CREATE TABLE test (
        id String(32),
        col1 String(8),
        col2 String(8),
        col3 String(8),
        col4 String(8),
        col5 String(8),
        col6 String(8),
        col7 String(8),
        col8 String(8),
        col9 String(8),
        col10 String(8),
    ) PRIMARY KEY (id)"""
    ]
)

database.create().result()

# Insert a bunch of rows
rows = [[uuid.uuid4().hex, *("12345678" for _ in range(10))] for _ in range(30_000)]
for i in range(10):  # Insert in chunks to avoid mutation limit
    chunk = rows[3000 * i:3000 * (i + 1)]
    with database.batch() as batch:
        batch.insert(table="test", columns=["id", *(f"col{i}" for i in range(1, 11))], values=chunk)

# Select them
with database.snapshot() as snapshot:
    results = snapshot.execute_sql("SELECT * FROM test")
    print(len([*results]))

This returns a random amount of rows, usually around 26,000, instead of the 30,000 that we inserted. We can verify that the query actually retrieved 30,000 rows via the query statistics table:

with database.shapshot() as snapshot:
    results = snapshot.execute_sql('select AVG_ROWS, AVG_BYTES from SPANNER_SYS.QUERY_STATS_TOP_MINUTE WHERE TEXT = "SELECT * FROM test"')
    print([*results])

My only theory is Spanner has a 10mb request limit when querying data. Spanner may be silently drop rows when making a query that returns more than 10mb of data. However, the query statistics table reports that only 4112500 bytes (4mb) is being read. The documentation reports that this number doesn't include transmission overhead, but I doubt that there is 6mb in overhead.

[asthamohta]

Thanks for reporting this @trevor-pope, let me try to recreate this issue and get back to you.

[asthamohta]

Hi @trevor-pope, I tried recreating your issue multiple times using your exact code but did not face the issue. Is there any more info you can share that may help debug it?

[asthamohta]

Hi @trevor-pope , closing this as no new response received from you. Feel free to open this if needed.