[GitHub] [pinot] atris commented on issue #10609: Support for case-sensitive like in Pinot

["atris (via GitHub)" <gi...@apache.org>]

atris commented on issue #10609:
URL: https://github.com/apache/pinot/issues/10609#issuecomment-1519868779

   > @Jackie-Jiang @walterddr : just fyi, the behavior of like is not compliant with Postgres. LIKE is case sensitive there but in Pinot it is case insensitive.
   
   Postgres's ILIKE is not SQL Standard compliant, as is documented in their documentation.
   
   The SQL Standard defines the LIKE operator to be case insensitive.
   
   I think it will be super messy to introduce ILIKE to the dialect. Most databases choose a style and stick to it.
   
   I would strongly argue that this is not a "bug". SQL Server has the exact same behaviour, and so does MariaDB. We should not try to do all styles by introducing different keywords.
   
   I see three clean ways of doing it:
   1. Introduce collations and let the set collation type define whether the match is case insensitive or not.
   2. Introduce a configuration which toggles between the two behaviours and let users decide what they want. The code path remains the same and just has an additional rule stacked over it.
   3. Introduce a new keyword to LIKE to define if case sensitivity is required (MySQL's WHERE BLOB).


-- 
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: commits-unsubscribe@pinot.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org