duckdb sniff_csv fails with > 10240 lines

[ddm-michel]

What happens?

When I use sniff_csv implicitly using read_csv with the arguments ", header=True, delim=';', sample_size=10241", it triggers an "unable to detect csv format"-error.

• I can place multiple different lines on line 20241 and they all trigger it. The lines seem fine and not different than any others.
• When I reduce the sample_size to 10240, the import works again.
• When I then remove lines 10236-10240 from the original file and keep the sample_size on 10240, it also works.

This indicates to me that 10240 is an upper limit for the sampling before it goes wrong, but the default is of 20xxx.

I unfortunately can't provide you the sample.

The duckdb-version is "v1.2.0"

To Reproduce

self._db.execute(f"CREATE OR REPLACE TABLE {all_table_name} AS SELECT * FROM read_csv('{self.event.tmp_file_path}', header=True, delim=';', sample_size=10241)")

OS:

Linux

DuckDB Package Version:

1.2.0

Python Version:

3.13.7

Full Name:

Michel

Affiliation:

Acme

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?

No - I cannot share the data sets because they are confidential

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

[evertlammerts]

@ddm-michel can you test with the latest duckdb version, 1.4.1?

[paultiq]

This indicates to me that 10240 is an upper limit for the sampling

The sample size defines the length of the "jump", not the number of rows analyzed, and this can be a problem when the data is aligned to the sample size, see duckdb/duckdb#6669.

For example, this will fail because it's sampling every 2 elements which all happen to be int's:

import pandas as pd 
import duckdb 
data = [1, "a", 1, "b", 1, "c", 1, "d"]
n = 2
offset = 0

data = data * n
df1 = pd.DataFrame(data={"col1": data})

with duckdb.connect() as con:
    con.execute(f"SET global pandas_analyze_sample=2")
    df = con.execute("select * from df1").df()
    print(df)

InvalidInputException: Invalid Input Error: Failed to cast value: Could not convert string 'a' to INT32

[ddm-michel]

I just checked and the issues still occurs with the latest 1.4-version of duckdb. The same error.

[ddm-michel]

This indicates to me that 10240 is an upper limit for the sampling

The sample size defines the length of the "jump", not the number of rows analyzed, and this can be a problem when the data is aligned to the sample size, see duckdb/duckdb#6669.

For example, this will fail because it's sampling every 2 elements which all happen to be int's:

import pandas as pd
import duckdb
data = [1, "a", 1, "b", 1, "c", 1, "d"]
n = 2
offset = 0

data = data * n
df1 = pd.DataFrame(data={"col1": data})

with duckdb.connect() as con:
con.execute(f"SET global pandas_analyze_sample=2")
df = con.execute("select * from df1").df()
print(df)

InvalidInputException: Invalid Input Error: Failed to cast value: Could not convert string 'a' to INT32

I understand it's the number of samples and they are probably taken randomly or pseudo. I suppose the data is taken per column here, to determine the format of the column. It's not a type error that I'm getting of course. The error is that the csv format is not recognized (the delimiter, comment, columns and their types, ...). so, it's more general.

[paultiq]

* ignore my comment... I was thinking this was a pandas question because I was just working on Pandas samples sizes!

[Tishj]

This sounds like a core issue, can you check whether this reproduces with the CLI?
If that's the case we should move this issue back to core