Issue selecting the second column via SQL

[colbyn]

I hope this isn’t a dumb question but I’m having issuing selecting the second column.

Given the following CSV file,

id, value
0, "Red"
1, "Blue"
2, "Green"
3, "White"
4, "Black"

When I try to select the second column as shown

let data_frame = ctx
    // .sql("SELECT id FROM my_table") // works
    // .sql("SELECT * FROM my_table") // works
    .sql("SELECT value FROM my_table") // error
    .await
    .unwrap();

I get this

thread 'main' panicked at src/main.rs:21:10:
called `Result::unwrap()` on an `Err` value: SchemaError(FieldNotFound { field: Column { relation: None, name: "value" }, valid_fields: [Column { relation: Some(Bare { table: "my_table" }), name: "id" }, Column { relation: Some(Bare { table: "my_table" }), name: " value" }] })
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

There is a value entry in valid_fields, this makes no sense?

Here is the repo

[colbyn]

Notice, presumably it's looking for a 'value' field as shown field: Column { relation: None, name: "value" } but for the second element of valid_fields notice the leading space in name: " value".

Why isn't it cleaned up?

[alamb]

The answer to your question is that the arrow-csv reader reads the field names as id, and value (with space)

The longer answer is probably related to the lack of a spec for csv files. It is concievable that we could add an option like "trim fields" or something to the csv reader that would trip leading/trailing whitespace, but I am not sure how common such files are and thus how commonly used such an option would be

[alamb]

Thank you for the question and report @colbyn

Fascinating. When I changed the data to the following (remove the space in the CSV header) then everything works fine:

--- a/sample.csv
+++ b/sample.csv
@@ -1,4 +1,4 @@
-id, value
+id,value
 0, "Red"
 1, "Blue"
 2, "Green"

Then the reproducer runs great

+----------+
| value    |
+----------+
|  "Red"   |
|  "Blue"  |
|  "Green" |
|  "White" |
|  "Black" |
+----------+

As you have surmized the csv reader is not trimming whitespace on the ,

I looked a little into the CSV reader in arrow and it seems to only permit a single character delimiter (rather than potentially multiple chars , )

[colbyn]

Oh I feel stupid! lol This makes sense.

With regard to your suggestion to just trim the headers, I noticed updating the following methods in datafusion/common/src/dfschema.rs works:

/// Find all fields match the given name
pub fn fields_with_unqualified_name(&self, name: &str) -> Vec<&DFField> {
    println!("self.fields: {:#?}", self.fields);
    self.fields
        .iter()
        .filter(|field| field.name().trim() == name) // Updated
        .collect()
}

/// Returns an immutable reference to the `Field`'s name.
#[inline]
pub const fn name(&self) -> &str {
    self.name.trim() // Updated
}

Then SELECT value FROM simple this works

+----------+
|  value   |
+----------+
|  "Red"   |
|  "Blue"  |
|  "Green" |
|  "White" |
|  "Black" |
+----------+

Although this feels hacky. Personally I'm fine with just cleaning up my CSV headers as opposed to making the DataFusion implementation more complex.

Thanks again for your reply!

[colbyn]

Although with regarding to trimming values,

According to RFC 4180 "Spaces are considered part of a field and should not be ignored."

Perhaps it's better to stick to the standard so that such files are more interoperable.

[alamb]

Perhaps it's better to stick to the standard so that such files are more interoperable.

Yeah -- though to be honest I don't think RFC 4180 is the normative standard -- I think it is "whatever Microsoft Excel does" 😆

[colbyn]

It’s kinda surprising there’s no standard.

Personally if it was up to me I’d prefer that leading and trailing spaces be ignored so tables can be neatly formatted in your text editor…