apache / apache/datafusion-python
CsvReadOptions.null_regex has no effect: matching values are read as literal strings, and fail the read in a numeric column
- Langage dominant
- Python
- Étoiles
- 604
- Forks
- 174
- Merge moyen
- 1 j 7 h
- PR mergées (30 j)
- 4
Description
**Describe the bug**
`CsvReadOptions.null_regex` is accepted everywhere it is offered, but the CSV
reader never applies it. Values matching the regex are read as literal strings,
and when a matching value sits in a column typed as an integer the read fails
outright instead of producing NULL.
This is not a bug in this crate — `python/datafusion/options.py` stores the
value and `crates/core/src/options.rs` copies it into DataFusion's
`CsvReadOptions` correctly. The root cause is in DataFusion itself; see below.
**To Reproduce**
```python
from pathlib import Path
from datafusion import CsvReadOptions, SessionContext
p = Path("probe.csv")
p.write_text("id,name\n1,alice\n2,N/A\n3,carol\n")
ctx = SessionContext()
options = CsvReadOptions().with_has_header(True).with_null_regex(r"^(null|NULL|N/A)$")
ctx.read_csv(p, options=options).show()
```
```
+----+-------+
| id | name |
+----+-------+
| 1 | alice |
| 2 | N/A | <- expected NULL
| 3 | carol |
+----+-------+
```
Every entry point behaves the same way — the `CsvReadOptions(null_regex=...)`
constructor, `with_null_regex()`, `read_csv()`, `register_csv()`, and SQL:
```python
ctx.sql("""
CREATE EXTERNAL TABLE t (id INT, name VARCHAR)
STORED AS CSV LOCATION 'probe.csv'
OPTIONS ('format.has_header' 'true', 'format.null_regex' '^(null|NULL|N/A)$')
""")
ctx.sql("select * from t").show() # same output, N/A not nulled
```
The SQL form does not reject the option, and giving an explicit schema does not
change anything, so this is not schema inference choosing `Utf8`.
When the matching value is in a numeric column, the read fails rather than
returning the wrong value:
```python
p.write_text("id,value\n1,10\n2,N/A\n3,30\n")
ctx.sql("""CREATE EXTERNAL TABLE t2 (id INT, value BIGINT) STORED AS CSV
LOCATION 'probe.csv'
OPTIONS ('format.has_header' 'true', 'format.null_regex' '^(null|NULL|N/A)$')""")
ctx.sql("select * from t2").show()
```
```
DataFusion error: Arrow error: Parser error: Error while parsing value 'N/A' as
type 'Int64' for column 1 at line 2. Row data: '[2,N/A]'
```
This is the case that matters in practice: `N/A`, `NULL` and `-` placeholders
in otherwise numeric columns are the reason to reach for `null_regex` at all.
**Expected behavior**
A field matching `null_regex` is read as NULL, whatever the column's type.
**Additional context**
Why the existing coverage does not catch it: `test_read_csv_with_options` in
`python/tests/test_context.py` does set `null_regex="[pP]+aris"`, but the only
`Paris` in its fixture is on the `#Charlie;35;Paris` line, which `comment="#"`
removes before the reader sees it. The `None` in that test's expected output
comes from `truncated_rows=True` on the `Bob;25` row, not from `null_regex`. The
test pins that the option parses, which is what its comment says it is for — it
does not pin the behavior.
`docs/source/user-guide/io/csv.md` documents `with_null_regex` as "Treat these
as NULL", so the documented behavior and the actual behavior disagree.
**Root cause (upstream).** In `datafusion-datasource-csv` 55.0.0, the version
this repository pins, `null_regex` is applied during schema inference but never
to the reader that actually parses the rows:
- `src/file_format.rs:547` sets it on the `arrow::csv::reader::Format` used for
`infer_schema`.
- `src/source.rs:187` builds the `csv::ReaderBuilder` that reads the data, and
calls `with_delimiter`, `with_header`, `with_quote`, `with_truncated_rows`,
`with_terminator`, `with_escape` and `with_comment` — but not
`with_null_regex`. `arrow_csv::reader::ReaderBuilder::with_null_regex` exists
in arrow 59.2.0 and is simply never called.
`CsvSource` already holds the full `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 just below it.
I could not find this reported in either this repository or apache/datafusion.
If you would rather track it upstream, I am happy to open it against
apache/datafusion and link it back here.
Found while working on #1728 / #1732, which is why the advanced-options example
there keeps `with_null_regex` set but puts its `N/A` in a string column, so the
example runs. Happy to take the fix if it turns out to be on this side.
Guide de contribution
Aucun guide de contribution indexé pour ce dépôt
Piste de recherche
Start in crates/core/src/options.rs and datafusion-datasource-csv's src/source.rs:187, then compare the reader builder with the null_regex handling in src/file_format.rs:547. Add coverage to python/tests/test_context.py using a matching value in a numeric column, and run the CSV option tests. Done means matching fields become NULL for both string and numeric columns, while the documented behavior in docs/source/user-guide/io/csv.md remains accurate.
Rédigé par le modèle d'indexation à partir du texte de l'issue.
Évaluation
- Stack technique
- python, rust
- Domaine
- backend, data-engineering
- Type d'issue
- Bug
- Difficulté
- 3/5
- Temps estimé
- 1-2 jours
- Activité
- Active
- Clarté
- Clairement spécifiée
- Accessibilité débutants
- 76/100