atris commented on issue #10609: URL: https://github.com/apache/pinot/issues/10609#issuecomment-1520626208
> @atris > > Asked ChatGPT on this > > > The SQL Standard defines the LIKE operator to be case insensitive > > and it says: > > ``` > According to the SQL-92 standard, column values are not required to be case-sensitive, so the > implementation of case-sensitivity for column values is left up to the database system. > > That being said, most database systems do treat column values as case-sensitive by default. > This means that "John" and "john" would be considered two different values in a column. > However, some database systems may provide options or settings to allow for case-insensitive > comparison of column values. > ``` > > It seems - **identifiers, such as table and column names, are not case-sensitive** as per SQL-92 standard. > > Also it looks like that behaviour of `REGEXP_LIKE` is case-insensitive as well in Pinot. According to ChatGPT: > > ``` > According to the SQL standard, the REGEXP_LIKE function should be case-sensitive by default. > ``` > > So I think the behaviour of `REGEXP_LIKE` should be fixed for sure but that is also backward-incompatible. Ok, I stand corrected. Nevertheless, databases choose one path. I am +1 to adding the capacity of specifying case sensitive searches -- as an extension to LIKE, while leaving the default behaviour the same. We need to think about REGEXP_LIKE's rollout plan, but should fix it for sure. Thinking about it, most databases make this choice a lot more obvious by using UPPER and LOWER. Should we consider that option? -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
