Allow configuration of transformation for case-insensitive queries

[ViktarRVR]

We use Spring Data JPA + Querydsl JPA. If we perform queries with ignoreCase keyword in repository methods Spring data JPA use UPPER function on parameters in SQL(as described in documentation, Table 3. Supported keywords inside method names), but Querydsl JPA uses LOWER function. We should use two types of indexes or don't use ignoreCase keyword in repository methods.

It would be great, if it was possible to configured to use UPPER function or LOWER function in Spring Data JPA.

See also #3540

[mp911de]

FTR: https://github.com/querydsl/querydsl/blob/99e3ca936f46471bc61d9af6e1a3dbb16f3f12b3/querydsl-jpa/src/main/java/com/querydsl/jpa/JPQLTemplates.java

[steveratay]

I ran into the same problem in the query by example code. It also appears to be hard-coded to use a lower conversion. As stated above, this is challenging because I only want to maintain a single case-insensitive index on either the upper or lowercase version of certain columns.

See https://github.com/spring-projects/spring-data-jpa/blob/main/src/main/java/org/springframework/data/jpa/convert/QueryByExamplePredicateBuilder.java#L182.

It would be great if this were able to be customized in the ExampleMatcher methods.

[gregturn]

Whereas another knob would work, I wonder if it isn't simpler to just update things to use lower across the board.

[schauder]

Going consistently with lower sounds like a good way to go. Please make sure to have this change included in release notes since it might disable existing indexes and therefore cause a performance degradation when not addressed.

[gregturn]

This has been merged to main and to 3.0.x.

[gregturn]

Release notes https://github.com/spring-projects/spring-data-commons/wiki/Release-Train-2021.2-(Raj)-Release-Notes-(Preview) have been updated.

[OrangeDog]

Did this get reverted, or did I miss something? According to the logged HQL, 3.2.2 is using upper instead of lower.

Edit: 15e8e3bfe8396e7e151e7e540ed0b040593f1cfa doesn't look right at all. All it did was change a variable name from lower to upper.

[danwatt]

I am with @OrangeDog - I just wrapped up dropping an UPPER index thinking this had been resolved in 2.7.x, only to discover that a ~~QueryDsl~~ Repository IgnoreCase method is still generating where upper(...)=upper(?)

I am on 2.7.18 for this project, upgraded to 2.6.x, and was following the release notes assuming this had been fixed in 2.7: https://github.com/spring-projects/spring-data-commons/wiki/Spring-Data-2021.2-(Raj)-Release-Notes#ignorecase-operators-now-use-lower-functions-across-the-board

[OrangeDog]

@danwatt QueryDSL is using lower, repositories are using upper. Same as described in the original issue.

[OrangeDog]

@gregturn @mp911de can this be reopened? It was not fixed. It looks like someone messed up resolving conflicts in the merge or something.

[mp911de]

Sure. We've started exploring configuration for ignore-case transformations with https://github.com/spring-projects/spring-data-jpa/pull/3653/files#diff-fdc19ed3d18aab513d05cd0418ab02dbaa8f1bdf5f8f8574d26d1ecdd9914486

[mp911de]

This change concerns:

• Derived Queries (JPQL)
• Querydsl
• QueryByExamplePredicateBuilder
• SimpleJpaRepository

for Sorting and predicates

[OrangeDog]

Is there any plan to actually fix this issue as originally intended (i.e. make everything use lower)? That would be much easier than adding new configuration.