Re: Feature Request: startWith and endsWith Conditions should have ignoreCase version

[Lukas Eder]

Hi Max,

Thanks for your message. The correct way to handle case insensitivity is to use collations (in most databases). The existing equalIgnoreCase() method uses LOWER(), internally, which doesn't behave correctly in all languages.

Starting with jOOQ 3.11, collations are now supported:

https://github.com/jOOQ/jOOQ/issues/2908

I remember having rejected a similar feature request like yours much earlier, but I can't seem to find the relevant Github issue or user group message. I do believe that this rejection was because of the lack of collation support in jOOQ at the time. Things have changed now, and it should be possible, in the future, to override the case-insensitivity feature of these methods to use collations instead of LOWER():

https://github.com/jOOQ/jOOQ/issues/7881

So, here's your feature request, due for jOOQ 3.12:

https://github.com/jOOQ/jOOQ/issues/7882

Thanks,

Lukas

On Thu, Sep 20, 2018 at 10:05 PM Max Kremer <mkr...@trialfire.com> wrote:

Hello again Lukas!

   I noticed the contains and equals and ignoreCase analogues. It would be great if Field.startWith and Field.endsWith also had versions that ignored case. Pretty simple request this time :)

Thanks,

Max

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Max Kremer]

Hi Lukas,

   I'm not sure how COLLATE will solve this issue on Postgres, at least not without creating a custom collation. Postgres has the CITEXT field type specifically for case insensitive comparisons. Do you mind elaborating how collation solves the issue with respect to string comparison (not sorting)

[Max Kremer]

Support material: https://dba.stackexchange.com/questions/191905/does-postgresql-support-icu-collations-options-and-settings

[Max Kremer]

Sorry one more : https://postgrespro.com/docs/postgresql/10/collation

Relevant text : ''Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values."

[Lukas Eder]

Hi Max,

That's very interesting, thanks for linking. I wasn't aware of this PostgreSQL specific behaviour. That's weird. The main reason for using collations is to override sort behaviour as far as I have learned this from other databases. I suspect there is some historic reason why this isn't currently the case in PostgreSQL.

CITEXT is a useful data type, but sometimes, you want to have case sensitive content but only sort (or filter) things case insensitively on an ad-hoc basis.

In any case, https://github.com/jOOQ/jOOQ/issues/7882 is implemented for jOOQ 3.12. For the time being, you can work around the missing feature by using LOWER() yourself in your own code.

Thanks again for the pointers,

Lukas

On Fri, Sep 21, 2018 at 5:38 PM Max Kremer <mkr...@trialfire.com> wrote:

Sorry one more : https://postgrespro.com/docs/postgresql/10/collation

Relevant text : ''Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values."

--

[Max Kremer]

Thanks Lukas,

  Consider also that you can cast to CITEXT on the fly to get that ad-hoc case-insensitivity.

Further on this subject of case insensitivity (can you tell I'm implementing case-insentive filters in our app ;)  ) consider arrays and the convenient behavior of the Field.contains method (when applied to arrays (when using the postgres dialect)), unfortunately this behaviour does not extend to the Field.containsIgnoreCase method which seems to behave exactly as contains when applied to arrays. I'm going to try to attempt it like this:

select tags
from sometable
where  tags::citext[] @> cast('{"FOO"}' as citext[])

will return rows where tags = {foo, bar, baz} for example

[Lukas Eder]

Hi Max,

Thanks for the suggestion. While I can see the case for containsIgnoreCase() (as we already have this method), at some point these things clearly don't scale anymore. E.g. consider that IN or NOT IN could have their "ignore case" version, just like [ NOT ] BETWEEN [ SYMMETRIC ], IS [ NOT ] DISTINCT FROM, etc. etc.

Thanks,

Lukas

[Max Kremer]

Lukas, completely argee. Only bringing it up because there are already similar methods...

Regards,

Max Kremer

http://www.trialfire.com

email: m...@trialfire.com

twitter: @maxtrialfire

skype: maxkremer

ᐧ

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/MHu2ApfYeC4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.