apache / apache/datafusion

CSV null_regex is applied to schema inference but never to the reader, so matching values are not null

Open Beginner friendly
#25,213 1 comment 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.