jadami10 commented on issue #10609:
URL: https://github.com/apache/pinot/issues/10609#issuecomment-1517991114
Asking GPT4; sql server, MySQL, and SQLite all do case-insensitive LIKE.
Postgres and Oracle do case sensitive LIKE. There's not a clear "right" way to
do this, and what's more challenging is that each database handles the opposite
of their default LIKE behavior differently. Most use different keywords:
BINARY, GLOB, ILIKE, etc.
The cleanest approach I see is what oracle is doing. For case sensitive use
`LIKE`. For case insensitive, do `WHERE UPPER(column_name) LIKE
UPPER('%pattern%')`. Postgres's `ILIKE` is probably my favorite and feels the
most intuitive, but that's just personal preference. Either of these would
require changing the Pinot default behavior.
Using the MySQL approach of saying `WHERE BINARY column_name like '%A%'`
also makes sense, but MySQL can also be case insensitive by default even on `=`
queries. So I don't think it maps so cleanly here.
I'm leaning slightly to changing the default behavior to avoid having to add
something like `WHERE BINARY` or `WHERE GLOB`. But the rollout plan will be a
little tedious if we don't want to break user queries. For example: in one
release add ILIKE where it does the same thing as LIKE. Add a cluster config
that can make LIKE case sensitive to unblock this issue. Give users a chance to
switch to ILIKE. Then in a future release make LIKE case sensitive by default,
and remove the cluster config.
--
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]
