More sensible handling of selecting camelcase columns

[TheBuilderJR]

Describe the bug

If I run the following query

SELECT userId FROM telemetry_user_events LIMIT 10;

against a table with the following schema

New Query
{
    "fields": [
        {
            "name": "event",
            "data_type": "Utf8",
            "nullable": true,
            "dict_id": 0,
            "dict_is_ordered": false,
            "metadata": {}
        },
        {
            "name": "prompt",
            "data_type": "Utf8",
            "nullable": true,
            "dict_id": 0,
            "dict_is_ordered": false,
            "metadata": {}
        },
        {
            "name": "timestamp",
            "data_type": "Utf8",
            "nullable": true,
            "dict_id": 0,
            "dict_is_ordered": false,
            "metadata": {}
        },
        {
            "name": "timestamp_utc",
            "data_type": {
                "Timestamp": [
                    "Millisecond",
                    "UTC"
                ]
            },
            "nullable": true,
            "dict_id": 0,
            "dict_is_ordered": false,
            "metadata": {}
        },
        {
            "name": "teamId",
            "data_type": "Utf8",
            "nullable": true,
            "dict_id": 0,
            "dict_is_ordered": false,
            "metadata": {}
        },
        {
            "name": "userId",
            "data_type": "Utf8",
            "nullable": true,
            "dict_id": 0,
            "dict_is_ordered": false,
            "metadata": {}
        },
        {
            "name": "query",
            "data_type": "Utf8",
            "nullable": true,
            "dict_id": 0,
            "dict_is_ordered": false,
            "metadata": {}
        }
    ],
    "metadata": {}
}

I get the following error

Schema error: No field named userid. Valid fields are telemetry_user_events.event, telemetry_user_events.prompt, telemetry_user_events.timestamp, telemetry_user_events.timestamp_utc, telemetry_user_events.\"teamId\", telemetry_user_events.\"userId\", telemetry_user_events.query.\n 1: No field named userid. Valid fields are telemetry_user_events.event, telemetry_user_events.prompt, telemetry_user_events.timestamp, telemetry_user_events.timestamp_utc, telemetry_user_events.\"teamId\", telemetry_user_events.\"userId\", telemetry_user_events.query.

However if I wrap userId in quotes like so

SELECT "userId" FROM telemetry_user_events LIMIT 10;

there are no errors. but this seems unideal! all other sql query engines I know handle camelcase gracefully

To Reproduce

Included in the above

Expected behavior

no errors if i select a camelcase column without wrapping in quotes

Additional context

No response

[jonahgao]

Do you enclose the column name in quotes when creating the table? similar to the table t2 in the example below.

DataFusion CLI v43.0.0
> create table t1(userId text);
0 row(s) fetched.
Elapsed 0.012 seconds.

> select userid from t1;
+--------+
| userid |
+--------+
+--------+
0 row(s) fetched.
Elapsed 0.006 seconds.

> create table t2("userId" text);
0 row(s) fetched.
Elapsed 0.012 seconds.

> select userid from t2;
Schema error: No field named userid. Valid fields are t2."userId".

[TheBuilderJR]

@jonahgao I actually use infer_json_schema_from_iterator, but this is kind of orthogonal since as you can tell from the arrow schema, clearly the field is named correctly in the parquet file so it's really datafusion doing something wrong like lowercasing the query or other processing that is messing up the matching. This is further evidenced by the fact that wrapping the column in quotes fixes it (but is a horrible UX and something I don't want to impart on my users).

[findepi]

Lowercasing is not unexpected when within SQL parser frontend (and eg PostgreSQL does the same).
Are you constructing queries using ctx.sql(str) method of creating plan nodes programmatically?

[TheBuilderJR]

@findepi yes that's correct, you can see the code here

let result: Result<_> = ctx
            .sql(
                &format!(
                    "SELECT * FROM ({}) AS query LIMIT 10000",
                    regex::Regex
                        ::new(r"--[^\n]*")
                        .unwrap()
                        .replace_all(&query, " ")
                        .replace('\n', " ")
                        .trim()
                        .trim_end_matches(';')
                )
            ).await

As an aside, all the pre-processing I need to do from the datafusion user side is also unideal (removing new lines, trailing semicolon, etc). Ideally the sql API would do this processing by default.

[alamb]

You can also set the datafusion.sql_parser.enable_ident_normalization config parameter to disable identifier normalization

https://datafusion.apache.org/user-guide/configs.html

[Dietr1ch]

Lowercasing is not unexpected when within SQL parser frontend (and eg PostgreSQL does the same).

Not unexpected? If I meant to query columns/tables with lowercased names I'd have typed them with lowercase. This is awfully unexpected.

I was trying to get my dad to use bdt to query some csv files (and hopefully get him to stop using CSV files at work), but things like this seem to come out of nowhere for users.

If there's need for Postgres compatibility, then I feel that should be opt-in, and not a default. Would you consider defaulting datafusion.sql_parser.enable_ident_normalization to false?

[alamb]

If there's need for Postgres compatibility, then I feel that should be opt-in, and not a default. Would you consider defaulting datafusion.sql_parser.enable_ident_normalization to false?

I agree it is confusing, but if we switched the default value to fale then a other users would be confused.

One idea is to make the error message better, in the style of the rust compiler. I filed an idea here:

• Add a hint about normalization in error message #14089

[Dietr1ch]

I agree it is confusing, but if we switched the default value to false then other users would be confused.

I doubt it'll be as confusing since the error they would get even today wouldn't seem wrong.

Consider the two scenarios,

• Today, with identifier normalisation, I query for say select UserId, and get an error saying that the user_id column wasn't found, but I didn't type that, which is perplexing. I then proceed to look at my data and find that I did type UserId correctly.
  Now I know I'm typing the right thing and it doesn't work. Maybe I notice the snake_casing, but I have no idea how to avoid it as whatever I type gets modified. Using quotes to avoid normalisation is not the first thing that may come to mind.

  • Debugging isn't easy
  • Figuring out what to do is hard, or at least annoying
    • Quote if you know SQL pitfalls.
    • Rename your columns.
    • Cry

• If the identifier normalisation was off by default, the errors would be alright.
  I'd keep my typing UserId to query the user_id column like I used to do, but after an update this new error appears. "Couldn't find column UserId", so I check my data and see that in fact the column was called user_id. I fix that in my query and it works.

  • Straightforward debugging
  • My input isn't being changed arbitrarily, so the obvious solution of fixing the typo works.

I think this change is worth the breakage it'd cause, and that it'd be great to have nicer errors before the default changes, but even if we didn't, things would be fine.