Support for historical data validation causes error in CSV files without headers

[jfpascoal]

The recent changes introduced in #1006 use INTERSECT to get the names of the columns that exist in both the data contract and the data files (parquet or CSV). However, when CSV files have no headers and the option names is not used, DuckDB assigns default names. Except in very rare cases, an intersection between those default names and the data contract names will return an empty array, which causes the INSERT in the next step to throw an error (due to empty SELECT statement).

Apart from this error, these changes also introduced an inconsistency - the SQL object created by the create_view_with_schema_union function will either be a table (if converted_types) or a view (fallback). I suspect the use of a table instead of a view may have further implications in terms of performance, as data will automatically be loaded into memory.

Finally, I would raise the question of whether it makes sense to treat non-required fields as optional rather than nullable. According to ODCS, the required key "indicates if the element may contain Null values", not if it can be entirely absent from the data. Perhaps it would be useful to have historical data support as an option but I am not sure that it makes sense to have it as the default behavior.

[dwestheide]

Hey, author of #1006 here. Thanks for reporting this.

On the ODCS spec consistency:

You're right that the ODCS spec defines required in terms of nullability. However, the spec is written with a database-centric view where field presence isn't a concept - database columns always exist in the schema, they just may contain NULL values.
For file-based formats (CSV, Parquet), there's a meaningful distinction between:

A field being present with a NULL value
A field being completely absent from the file

When a CSV file doesn't include a column, it's not that the field is "NULL for all rows" - the field simply doesn't exist in that file. For schema evolution to work with immutable files, we need to validate field presence, not just nullability constraints.

I believe required: false naturally extends to mean "field can be absent" in file format contexts, even though the spec only explicitly discusses nullability. This interpretation makes schema evolution practical for files, where you can't retroactively add columns to existing files the way you can with ALTER TABLE.

On headerless CSV files:

You're right that my implementation doesn't handle headerless CSV files correctly - it assumes column names are available for comparison, and I should have thought of headerless CSV as well.

The fundamental challenge is that headerless CSV files don't provide enough information for meaningful schema evolution validation - we can't match columns by name, and matching by position is unreliable if fields can be reordered.
I think the pragmatic solution is to fall back to the original behavior for headerless CSV files. If we do this, this limitation should be documented.

I'm happy to implement a fix that handles headerless CSV appropriately.

On making schema evolution support opt-in:

I'd push back on making this opt-in. For database targets, schema evolution validation is already built into the database system itself - you can't add a NOT NULL column without handling existing data, constraint violations are caught automatically, etc.
Making schema evolution validation opt-in would primarily affect file-based formats - exactly the use case where such validation is most valuable and least naturally enforced. File systems don't validate schema compatibility; existing files don't automatically update when schemas change. This is precisely where automated validation provides the most benefit.
If the concern is about breaking existing workflows or edge cases like headerless CSV, I think the better approach is to handle those cases gracefully (skip validation where it can't be meaningful) rather than making users explicitly opt into a safety feature that catches real problems.