You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2025/01/11 17:26:33 UTC

[I] Add a hint about normalization in error message [datafusion]

alamb opened a new issue, #14089:
URL: https://github.com/apache/datafusion/issues/14089

   ### Is your feature request related to a problem or challenge?
   
   There has been significant and long standing confusion about how to refer to columns with capitalization, most recently:
   - https://github.com/apache/datafusion/issues/13649
   
   I think the root cause is that SQL is largely case insensitive but many DataFrame like systems are case sensitive. 
   
   There is a larger question if we could perhaps have less confusing defaults, but I think we could make the error messages even better
   
   Today if you have a schema with a field named `userId` (note the capital `I`), if you run a query like 
   
   ```sql
   SELECT userId FROM foo
   ``` 
   
   you will get a seemingly nonsensical 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.
   
   
   
   ### Describe the solution you'd like
   
   I would like to improve the error to add a hint if there is a column name that matches the field exept for case about what to do to fix it. 
   
   
   In the example above something like:
   
   > note use double quotes to refer to the "userId"  column  or set the `datafusion.sql_parser.enable_ident_normalization` configuration option
   
   So the whole error would be something like
   
   > > Schema error: No field named userid. You can use double quotes to refer to the "userId"  column  or set the `datafusion.sql_parser.enable_ident_normalization` configuration option .  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.
   
   
   ### Describe alternatives you've considered
   
   _No response_
   
   ### Additional context
   
   I think this would be super helpful and a nice first issue


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Add a hint about normalization in error message [datafusion]

Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead closed issue #14089: Add a hint about normalization in error message
URL: https://github.com/apache/datafusion/issues/14089


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Add a hint about normalization in error message [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #14089:
URL: https://github.com/apache/datafusion/issues/14089#issuecomment-2585355521

   I am not opposed to breaking changes per se :) (see the past history of DataFusion)
   
   However, in this case I think the challege is that either default will be good for some people and bad for others. It is unclear to me how we would decide on the balance if more people are helped or hurt by changing the default (I would guess it is about 50/50 -- for example, it would certainly cause trouble I think for us at InfluxData...)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Add a hint about normalization in error message [datafusion]

Posted by "TheBuilderJR (via GitHub)" <gi...@apache.org>.
TheBuilderJR commented on issue #14089:
URL: https://github.com/apache/datafusion/issues/14089#issuecomment-2585351704

   +1 this would be great! That being said I do believe in the long run BC breaking changes are a good thing. Perhaps it makes sense to aggregate changes like these into a central issue and next time there's a big release that requires BC changes, we can include these small ones in as well.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Add a hint about normalization in error message [datafusion]

Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #14089:
URL: https://github.com/apache/datafusion/issues/14089#issuecomment-2585752736

   I agree with @findepi  -- I think of the automatic lowercasing in DataFusion as the implementation detail of how the case-normalization is implemented. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Add a hint about normalization in error message [datafusion]

Posted by "cj-zhukov (via GitHub)" <gi...@apache.org>.
cj-zhukov commented on issue #14089:
URL: https://github.com/apache/datafusion/issues/14089#issuecomment-2585580277

   take


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Add a hint about normalization in error message [datafusion]

Posted by "findepi (via GitHub)" <gi...@apache.org>.
findepi commented on issue #14089:
URL: https://github.com/apache/datafusion/issues/14089#issuecomment-2585678389

   > I think the root cause is that SQL is largely case insensitive 
   
   SQL spec seems to say that unquoted (not delimited) identifiers are equivalent to their upper-case delimited variants (ie `abc` is equivalent to `"abc"`). The equivalence relation is what's used for column resolution.
   
   Quoting SQL'16 here
   
   ```
   24) For every <identifier body> IB there is exactly one corresponding case-normal form CNF. CNF is an
   <identifier body> derived from IB as follows:
   Let n be the number of characters in IB. For i ranging from 1 (one) to n, the i-th character Mi of IB is
   transliterated into the corresponding character or characters of CNF as follows:
   Case:
   a) If Mi is a lower case character or a title case character for which an equivalent upper case sequence
   U is defined by Unicode, then let j be the number of characters in U; the next j characters of CNF are
   U.
   b) Otherwise, the next character of CNF is Mi.
   ```
   
   ```
   28) A <regular identifier> and a <delimited identifier> are equivalent if the case-normal form of the <identifier
   body> of the <regular identifier> and the <delimited identifier body> of the <delimited identifier> (with
   all occurrences of <quote> replaced by <quote symbol> and all occurrences of <doublequote symbol>
   replaced by <double quote>), considered as the repetition of a <character string literal> that specifies a
   <character set specification> of SQL_IDENTIFIER and IDC, compare equally according to the comparison
   rules in Subclause 8.2, “<comparison predicate>”
   ```
   
   However, DataFusion follows PostgreSQL variant of SQL, so my interpretation is that our intended behavior is "like in SQL spec but lower-case".
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org