CSV null_regex is applied to schema inference but never to the reader, so matching values are not null
- Dominant language
- Rust
- Stars
- 9.3k
- Forks
- 2.4k
- Avg merge
- 3d 7h
- Merged PRs (30d)
- 344
Description
### 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.
Contributor guide
Research direction
Start in datafusion-datasource-csv/src/source.rs at CsvSource::builder() around line 187, then compare it with the null_regex handling in src/file_format.rs around line 547. Run the SQL reproductions with string and numeric columns to confirm the current failure. Done means values matching null_regex are read as NULL for both data types.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust
- Domain
- data-engineering
- Issue type
- Bug
- Difficulty
- 2/5
- Estimated time
- 1-3 hours
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 84/100