Respect input_format_allow_errors_num/input_format_allow_errors_ratio during schema inference

[mneedham]

I made a deliberately bad CSV file:

$ cat foo.csv
name,favColour
Mark,Blue
David
Giles,Red

I try to process it:

SELECT *
FROM file('foo.csv')

Query id: 42729c66-9642-4c79-8c77-a68228aa64a4


Elapsed: 0.031 sec.

Received exception:
Code: 636. DB::Exception: The table structure cannot be extracted from a CSV format file. Error:
Code: 117. DB::Exception: Rows have different amount of values. (INCORRECT_DATA) (version 24.3.1.469 (official build)).
You can specify the structure manually: (in file/uri /Users/m

Makes sense, the structure is bad. So I set input_format_allow_errors_num which I thought would skip the bad row and I told it to use the CSVWithNames format too. But it still throws the error?

SELECT *
FROM file('foo.csv', CSVWithNames)
SETTINGS input_format_allow_errors_num = 5

Query id: 167b8033-9fa4-496f-b3e5-dd05cd3f8e04


Elapsed: 0.001 sec.

Received exception:
Code: 636. DB::Exception: The table structure cannot be extracted from a CSVWithNames format file. Error:
Code: 117. DB::Exception: Rows have different amount of values. (INCORRECT_DATA) (version 24.3.1.469 (official build)).
You can specify the structure manually: (in file/uri /Users/markhneedham/projects/videos/20240305-WindowFunctions/foo.csv). (CANNOT_EXTRACT_TABLE_STRUCTURE)

We can work around that by setting input_format_max_rows_to_read_for_schema_inference=1 which will have it use only 1 row to infer the schema, but it would be simpler to use if input_format_allow_errors_num and input_format_allow_errors_ratio were used during schema inference

cc @Avogar

[KalyWan]

I noticed that this behavior has changed starting with version 24.
It has indeed become inconvenient, as two error messages complicate the analysis and understanding of the problem.

Error format in major version 23:

Code: 117. DB::Exception: The number of column names 7 differs with the number of types 14: Cannot extract table structure from CSVWithNames format file. You can specify the structure manually. (INCORRECT_DATA) (version 23.3.4.17 (official build))

Error format in major version 24:

Code: 636. DB::Exception: The table structure cannot be extracted from a CSVWithNames format file. Error: 
Code: 117. DB::Exception: The number of column names 7 differs with the number of types 14. (INCORRECT_DATA) (version 24.8.6.70 (official build)). You can specify the structure manually: (in file/uri DesimalFloattochka.csv). (CANNOT_EXTRACT_TABLE_STRUCTURE) (version 24.8.6.70 (official build))

For the same query (the error is intentional):

DESCRIBE s3('<http://ip:9000/dev/DesimalFloattochka.csv>', 
            'login',
            'pass',
            'CSVWithNames') 
SETTINGS
    format_csv_delimiter='.',
    format_csv_allow_single_quotes=1,
    format_csv_allow_double_quotes=1,
    format_custom_row_after_delimiter= '\n',
    date_time_input_format='best_effort',
    input_format_csv_detect_header=0,
    input_format_custom_detect_header=0

I would like to restore the previous behavior with a single, clear error message, as it was in version 23.

[Avogar]

Agree, it makes sentse to support input_format_allow_errors_num/input_format_allow_errors_ratio in schema inference.

But in this particular example where some rows contain invalid number of columns you can just enable setting input_format_csv_allow_variable_number_of_columns, it also works for schema inference.