Developer1010x opened a new issue, #25213:
URL: https://github.com/apache/datafusion/issues/25213
### 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:
```rust
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:
```sql
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:
```sql
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 https://github.com/apache/datafusion-python/issues/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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
