Column name case sensitivity when reading from CSV / JSON

[sergiimk]

Describe the bug

SQL is case-insensitive language, but the way case-insensitivity is implemented for column identifiers in DataFusion often leads to non-intuitive behavior:

To Reproduce

Create test.json:

{"A": "a_upper", "B": "b_upper", "b": "b_lower"}

Run datafusion-cli:

❯ create external table test stored as json location 'test.json';

❯ select * from test;
+---------+---------+---------+
| A       | B       | b       |
+---------+---------+---------+
| a_upper | b_upper | b_lower |
+---------+---------+---------+

❯ select A from test;
Schema error: No field named a. Valid fields are test."A", test."B", test.b.

❯ select b from test;
+---------+
| b       |
+---------+
| b_lower |
+---------+

❯ select B from test;
+---------+
| b       |
+---------+
| b_lower |
+---------+

❯ select "B" from test;
+---------+
| B       |
+---------+
| b_upper |
+---------+

Expected behavior

In Spark:

• When I load json like {"A": "a_upper"} both select a from test and select A from test will return the "A" column.
• When I save to Parquet - the schema will preserve the column's original case
• Loading {"A": "a_upper", "B": "b_upper", "b": "b_lower"} however fails with duplicate column error - points to DF!

What I would expect as a user:

• a and A return "A" column
• b and B fail as ambiguous
• "B" and "b" work, returning upper and lower case columns respectively
• The schema always preserves the original case of identifier

In other words:

• Column names always stored in their original form and never .to_lower()'ed
• identifiers are matched in a case-insensitive way
• when there is an ambiguity - it can be resolved with quoted identifiers.

Additional context

Currently after switching our ingest from Spark to Datafusion our preprocessing code is full of "X" as x, "Y" as y just to restore case-insensitivity for downstream queries.

I could add an automatic step that lower-cases all columns, but I'd really like to preserve the original case in schemas for aesthetic reasons.

[parkma99]

https://github.com/apache/arrow-datafusion/blob/58fc80eddaf93d4c6399e4ac4fc1234649b26e43/datafusion/sql/src/expr/identifier.rs#L49-L56
in here the Column names .to_lower() if ident without quote.

https://github.com/apache/arrow-datafusion/blob/58fc80eddaf93d4c6399e4ac4fc1234649b26e43/datafusion/common/src/dfschema.rs#L274-L279
in here finding table field if it's name is given name.

[parkma99]

Hello here is my solution.

function field_with_unqualified_name add one parameter named ignore_case with bool type.
in function body
change:

.filter(|field| field.name() == name) => .filter(|field| if ignore_case{ field.name().to_ascii_lowercase() == name}else{ field.name() == name})

https://github.com/apache/arrow-datafusion/blob/58fc80eddaf93d4c6399e4ac4fc1234649b26e43/datafusion/sql/src/expr/identifier.rs#L49-L56

we can find get igore_case value by using id.quote_style.is_none();

change :

Ok(Expr::Column(Column { relation: None, name: normalize_ident, })) => Ok(Expr::Column(field.qualified_column()))

fixed all compiler errors, I found it not working very well.

1. a and A return "A" column
2. b and B fail as ambiguous
3. "B" and "b" work, returning upper and lower case columns respectively
4. The schema always preserves the original case of identifier

1 3 4 done, but 2 failed.
because function Column::normalize_with_schemas_and_ambiguity_check does not know using_columns's names are ignore_case or not. I set default is false.

One solution is Column adding a member named ignore_case. I think it's a ugly way. Do you have some suggesstion? @sergiimk Thanks.

here is my code:
https://github.com/parkma99/arrow-datafusion/tree/issue7460

[sergiimk]

Thanks a lot for the initial investigation @parkma99!

I think before making any further steps I'd like to wait for project maintainers to weigh in on whether the behavior and rules that I proposed even make sense.

I based them on my experience with Spark and several databases, but I definitely don't have a full picture of how case-sensitivity is designed in DF.

For example I just discovered enable_ident_normalization config option, which will likely interfere with what I proposed.

[alamb]

I think case sensitivity was designed for better or worse to follow postgres.

I think the ideal solution is not to invent new normalization behavior but follow an existing model. Adding an option that makes DataFusion treat identifiers the same way as Spark would make sense to me. However, coming up with entirely new rules would not

I agree that enabling enable_ident_normalization might make datafusion work better for your usecase.

[sergiimk]

Thank you @alamb. Don't know why it did not occur to me to test Postgres behavior, but can confirm it's consistent with DataFusion's.

Will close this issue and see how experience looks like with enable_ident_normalization.