Skip to content

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

Description

@Developer1010x

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.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions