Describe the bug
CsvOptions::null_regex is applied when the schema is inferred, but it is never
passed to the reader that parses the rows. A field matching the regex therefore
comes back as the literal string, and if the column is typed as a number the
read fails outright with an Arrow parser error instead of producing NULL.
In datafusion-datasource-csv (checked against 55.0.0):
-
src/file_format.rs:547 builds the arrow::csv::reader::Format used for
infer_schema and does set the regex:
if let Some(null_regex) = &self.options.null_regex {
let regex = Regex::new(null_regex.as_str())
.expect("Unable to parse CSV null regex.");
format = format.with_null_regex(regex);
}
-
src/source.rs:187, CsvSource::builder(), constructs the
csv::ReaderBuilder that actually reads the data. It sets with_delimiter,
with_batch_size, with_header, with_quote, with_truncated_rows,
with_terminator, with_projection, with_escape and with_comment — and
never with_null_regex.
arrow_csv::reader::ReaderBuilder::with_null_regex exists (arrow 59.2.0), and
CsvSource already holds the whole CsvOptions, so self.options.null_regex
is in scope at that point. It looks like a few lines in builder(), mirroring
the escape and comment blocks immediately below it.
To Reproduce
Observed through the Python bindings (datafusion 54.0.0), which pass
null_regex straight into CsvReadOptions. The same SQL is what a
datafusion-cli reproduction would run:
CREATE EXTERNAL TABLE t_str (id INT, name VARCHAR)
STORED AS CSV LOCATION 'nr_str.csv'
OPTIONS ('format.has_header' 'true', 'format.null_regex' '^(null|NULL|N/A)$');
SELECT * FROM t_str;
with nr_str.csv:
id,name
1,alice
2,N/A
3,carol
gives
+----+-------+
| id | name |
+----+-------+
| 1 | alice |
| 2 | N/A | <- expected NULL
| 3 | carol |
+----+-------+
The option is not rejected, and an explicit schema is supplied, so this is not
schema inference choosing Utf8.
The same placeholder in a numeric column fails the read rather than returning a
wrong value:
CREATE EXTERNAL TABLE t_num (id INT, value BIGINT)
STORED AS CSV LOCATION 'nr_num.csv'
OPTIONS ('format.has_header' 'true', 'format.null_regex' '^(null|NULL|N/A)$');
SELECT * FROM t_num;
Arrow error: Parser error: Error while parsing value 'N/A' as type 'Int64'
for column 1 at line 2. Row data: '[2,N/A]'
Every entry point behaves the same way, which is consistent with the reader
never seeing the regex at all.
Expected behavior
A field matching null_regex is read as NULL regardless of the column's data
type. That is what the option is for: N/A, NULL and - placeholders are
almost always sitting in columns that are otherwise numeric, which is exactly
the case that currently errors.
Additional context
The inference half working while the read half does not is why this is easy to
miss: the schema comes out as though the regex were honored, and only the data
disagrees.
Reported downstream first, with the equivalent reproduction through the Python
bindings, at apache/datafusion-python#1735. The
bindings pass the option through correctly; the gap is here.
I could not find an existing issue for this. Happy to put up a PR if the
approach above is the one you would want.
Describe the bug
CsvOptions::null_regexis applied when the schema is inferred, but it is neverpassed to the reader that parses the rows. A field matching the regex therefore
comes back as the literal string, and if the column is typed as a number the
read fails outright with an Arrow parser error instead of producing NULL.
In
datafusion-datasource-csv(checked against 55.0.0):src/file_format.rs:547builds thearrow::csv::reader::Formatused forinfer_schemaand does set the regex:src/source.rs:187,CsvSource::builder(), constructs thecsv::ReaderBuilderthat actually reads the data. It setswith_delimiter,with_batch_size,with_header,with_quote,with_truncated_rows,with_terminator,with_projection,with_escapeandwith_comment— andnever
with_null_regex.arrow_csv::reader::ReaderBuilder::with_null_regexexists (arrow 59.2.0), andCsvSourcealready holds the wholeCsvOptions, soself.options.null_regexis in scope at that point. It looks like a few lines in
builder(), mirroringthe
escapeandcommentblocks immediately below it.To Reproduce
Observed through the Python bindings (
datafusion54.0.0), which passnull_regexstraight intoCsvReadOptions. The same SQL is what adatafusion-clireproduction would run:with
nr_str.csv:gives
The option is not rejected, and an explicit schema is supplied, so this is not
schema inference choosing
Utf8.The same placeholder in a numeric column fails the read rather than returning a
wrong value:
Every entry point behaves the same way, which is consistent with the reader
never seeing the regex at all.
Expected behavior
A field matching
null_regexis read as NULL regardless of the column's datatype. That is what the option is for:
N/A,NULLand-placeholders arealmost always sitting in columns that are otherwise numeric, which is exactly
the case that currently errors.
Additional context
The inference half working while the read half does not is why this is easy to
miss: the schema comes out as though the regex were honored, and only the data
disagrees.
Reported downstream first, with the equivalent reproduction through the Python
bindings, at apache/datafusion-python#1735. The
bindings pass the option through correctly; the gap is here.
I could not find an existing issue for this. Happy to put up a PR if the
approach above is the one you would want.