Is there a setting to treat empty strings as null when reading from CSVs?

[66OJ66]

Hi,

I have a basic CSV file like this:

id,name
1,
2,bob

And I'm running this code where I'm attempting to filter out null names:

#[tokio::main]
async fn main() -> Result<(), DataFusionError> {
    let ctx = SessionContext::new();

    let results = ctx
        .read_csv("input.csv", CsvReadOptions::new())
        .await?
        .filter(IsNotNull(Box::new(col("name"))))?
        .collect()
        .await?;

    let pretty_results = arrow::util::pretty::pretty_format_batches(&results)?.to_string();

    println!("{}", pretty_results);

    Ok(())
}

I was expecting the name value in row 1 to be treated as null (as it's an empty string), but the output from the code shows the opposite:

+----+------+
| id | name |
+----+------+
| 1  |      |
| 2  | bob  |
+----+------+

So I was wondering if this is expected behaviour/there's a setting that needs to be toggled for empty strings to be treated as null?

[alamb]

I also would have expected this to produce null

There appears to be a setting in the underlying arrow reader to set this:

https://docs.rs/arrow-csv/47.0.0/arrow_csv/reader/struct.ReaderBuilder.html#method.with_null_regex

But I don't understand why the value is not treated as NULL. Maybe we should file a bug 🤔

BTW here is a reproducer with datafusion-cli

$ cat /tmp/test.csv
id,name
1,
2,bob

$ datafusion-cli
DataFusion CLI v31.0.0

❯ select id, name, name IS NULL from '/tmp/test.csv';
+----+------+----------------------------+
| id | name | /tmp/test.csv.name IS NULL |
+----+------+----------------------------+
| 1  |      | false                      |
| 2  | bob  | false                      |
+----+------+----------------------------+
2 rows in set. Query took 0.006 seconds.

[66OJ66]

Thank you @alamb

I did some digging and it looks like that method was added in arrow-csv v0.47, but the current release of datafusion uses v.0.46.

I then tried the main branch of datafusion (which does point towards arrow-csv v0.47), but I was still getting the same result with no records being treated as null.

I'll raise a bug for this :)