Repository navigation
CSVReader behavior with dataset that has duplicate column headers is confusing #12852
Description
Describe the bug
When working with a CSV file that has duplicate column headers (e.g., https://github.com/openssl/openssl/blob/master/test/recipes/80-test_cmp_http_data/test_connection.csv) the behavior is confusing.
Specifically, any query against a table backed by that file produces the error:
Arrow error: Csv error: incorrect number of fields for line 1, expected 14 got more than 14
However, all rows in that CSV have 20 fields.
Based on looking at the results of a limit 0 query, I can see that the schema is effectively dropping all duplicate columns from the expected schema, and therefore the following rows do not have the expected number of cells.
To Reproduce
Run queries against the linked CSV file.
Expected behavior
I believe expected behavior would be to either a) automatically rename those columns (adding _{n} perhaps), or b) provide a clear error that schemas with duplicate column names are not supported.
Additional context
No response
Activity
jpsrobertson commentedon Nov 12, 2024
bumping as i'm also experiencing this problem!
Rafferty97 commentedon Nov 12, 2024
This looks to be caused by the call to
Schema::try_mergeatdatasource/file_format/csv.rs:328. That function merges schemas together by joining on field name, so a side effect is the merging of fields within a single schema that happen to have the same name.I think an important question is whether it makes sense for a schema to have multiple fields with the same name in the first place, or whether it should be disallowed. While maybe it's defensible given fields can be accessed by index, it does make it pretty hard to sensibly merge schemas based on field name as
Schema::try_mergedoes.alex commentedon Nov 12, 2024
From the perspective of a user, I think the ideal default behavior would be register a clean error in that case.
But I'd also like the ability to somehow map the duplicate field names to something (e.g.,
{field}_{i}). If it just did that by default, that'd also be fine.But sadly, CSVs with duplicate columns names are kind of common.
Rafferty97 commentedon Nov 12, 2024
Having thought about it some more, I think the use of
Schema::try_mergeis actually incorrect for CSV files, because the CSV reading process assumes that the fields in theSchemaare in the same order as they appear in the file.So, if two CSVs are read in with the same columns but out of order, this will cause data to appear in the wrong columns. This might error out if there is a mismatch in types, but could also just silently return bogus data.
My intuition is that the code needs to be changed to merge CSV schemas based on field index not field name.
fix: reject duplicate field names during CSV and Parquet schema infer…