duckdb.sql() on a piped CSV silently returns wrong results; con.execute() on the same input is correct #622

What happens?

DuckDB 1.5.5 Python module on both of: macOS 26.6.2 arm64 with Python 3.14.7, and Ubuntu 24.04.4 x86-64, kernel 6.18.44, with Python 3.11.15. Identical results.

duckdb.sql() and con.sql() on read_csv('/dev/stdin') do not see the start of the input. con.execute() on the same bytes is correct, and all three are correct when given a filename.

This one defect shows up three ways, one per section below:

1. count(*) returns fewer rows than the file holds, and raises nothing.
2. A query naming a column cannot bind, because the header is among the bytes not seen.
3. Those bytes are the buffers the auto-detection sample spans, which is what identifies them.

python3 - <<'PY'
import uuid, random
random.seed(7)
c = [str(uuid.UUID(int=random.getrandbits(128))) for _ in range(400)]
A = '"Mozilla/5.0 (Linux; Android 16) AppleWebKit/537.36 (KHTML, like Gecko) Mobile Safari/537.36"'
with open('in.csv','w') as f:
    f.write("center_id,camera_id,timestamp,agent,ip_address,resource_type,view_duration,user_id\n")
    for i in range(2000000):
        f.write(f"{c[(i//2000)%400]},{uuid.UUID(int=random.getrandbits(128))},2026-07-31 13:47:16.765,{A},203.0.113.42,HLS,3000,{uuid.UUID(int=random.getrandbits(128))}\n")
PY

cat in.csv | python3 -c "import duckdb; print(duckdb.sql(\"SELECT count(*) FROM read_csv('/dev/stdin')\").fetchall())"
cat in.csv | python3 -c "import duckdb; print(duckdb.connect().execute(\"SELECT count(*) FROM read_csv('/dev/stdin')\").fetchall())"
cat in.csv | python3 -c "import duckdb; print(duckdb.sql(\"SELECT center_id FROM read_csv('/dev/stdin') LIMIT 1\").fetchall())"

The file is 2,000,000 rows of 251 bytes, 478.7 MiB. The first command returns 1,872,510, the second 2,000,000, the third raises a BinderException.

1. sql() returns fewer rows than the file holds; execute() returns all of them

No exception is raised.

2. The header is not seen, so a query naming a column cannot bind

SELECT center_id FROM read_csv('/dev/stdin') LIMIT 1 through a pipe:

The binder offers column0 through column4 as candidates, for a file with eight columns and a header naming all eight.

The resumption is at a byte offset rather than a row boundary, and in this file byte 32,000,000 falls inside the quoted user-agent field, mid-word. From there to the next newline there are five comma-separated fields, which is the column count reported:

...bKit/537.36 (KHTML, like Gecko) Mobile S | afari/537.36",203.0.113.42,HLS,3000,04568adf-fb75-b0f7-0008-dec353b0cd95

3. The bytes lost are the buffers the auto-detection sample spans

duckdb.sql() on a pipe, buffer_size fixed at 1 MiB, varying sample_size. The rows lost come to ceil(sample_size * 251 / buffer_size) * buffer_size bytes, to the byte, at every sample size tried.

At the defaults the loss is 32.0 MB, the 127,490 rows above. So what goes missing is not an arbitrary prefix: it is the input read to auto-detect the dialect and types, rounded up to whole buffers.

Expected: sql() and execute() return the same rows for the same input, as they do for a filename. If a relation over a source that cannot be re-read is not supportable, then raising is the outcome I would expect, rather than a short count with no exception.

To Reproduce

python3 - <<'PY'
import uuid, random
random.seed(7)
c = [str(uuid.UUID(int=random.getrandbits(128))) for _ in range(400)]
A = '"Mozilla/5.0 (Linux; Android 16) AppleWebKit/537.36 (KHTML, like Gecko) Mobile Safari/537.36"'
with open('in.csv','w') as f:
    f.write("center_id,camera_id,timestamp,agent,ip_address,resource_type,view_duration,user_id\n")
    for i in range(2000000):
        f.write(f"{c[(i//2000)%400]},{uuid.UUID(int=random.getrandbits(128))},2026-07-31 13:47:16.765,{A},203.0.113.42,HLS,3000,{uuid.UUID(int=random.getrandbits(128))}\n")
PY

cat in.csv | python3 -c "import duckdb; print(duckdb.sql(\"SELECT count(*) FROM read_csv('/dev/stdin')\").fetchall())"
cat in.csv | python3 -c "import duckdb; print(duckdb.connect().execute(\"SELECT count(*) FROM read_csv('/dev/stdin')\").fetchall())"
cat in.csv | python3 -c "import duckdb; print(duckdb.sql(\"SELECT center_id FROM read_csv('/dev/stdin') LIMIT 1\").fetchall())"

OS:

macOS 26.6.2 arm64; Ubuntu 24.04.4 x86-64, kernel 6.18.44

DuckDB Package Version:

1.5.5

Python Version:

macOS: 3.14.7; Ubuntu: 3.11.15

Full Name:

Vijay Varadan

Affiliation:

Axham Corp

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have