Returned entity validation for case sensitive string parameters in auto-generated queries

[LucianCSD]

I noticed that there is no validation for the returned entities in case of case sensitive string parameters.
By default, I would expect that if I don't mention that I want the query to ignore the case, the returned result fails to validate against the passed case sensitive parameters.

interface CustomerRepository extends ReactiveCrudRepository<Customer, Long> {
    Mono<Customer> findByName(String name);
}

In this example, I would expect the method to return a found customer entity by the passed case sensitive "name" parameter, but it seems it's not the case, searching by the name "john" has the same behavior as searching by the name "JoHn".

app db connection config

spring:
  r2dbc:
    url: r2dbc:mysql://${HOST}:3306/<schema>
    username: ${USERNAME}
    password: ${PASSWORD}

Dependencies :

org.springframework.boot:spring-boot-starter-data-r2dbc:2.5.3
org.springframework.data:spring-data-commons:2.5.3
org.springframework.data:spring-data-r2dbc:1.3.3
org.springframework.data:spring-data-relational:2.2.3
dev.miku:r2dbc-mysql:0.8.2.RELEASE

https://docs.spring.io/spring-data/r2dbc/docs/current/reference/html/#repositories.query-methods.query-creation

[mirromutth]

Hi there, apologies for the late reply.

In my limited experience with MySQL, that should not be a driver issue. It's MySQL feature of character collation.

Whenever you CREATE TABLE/DATABASE/SCHEMA in MySQL, the table/database/schema has a character set and a character collation. Each character set has a default collation, see also MySQL 5.7 Reference - Character Sets and Collations. Therefore, if you use MySQL 5.7.x and utf8 character set, it should be utf8_general_ci.

I think you have noticed, the postfix _ci means case insensitive. Therefore, whether you are using drivers, database tools, or some else, the MySQL server will always match the value with case insensitive.

You can have a couple of choices:

• Use collation utf8_bin in schema and tables (probably the easiest if you have to use MySQL 5.7.x and utf8 character set)
• Use collation utf8mb4_0900_as_cs in schema and tables, upgrade MySQL to 8.0.x.

I'm trying to close this issue due to it is not a driver issue. If you think there should be have a new progress on this problem, please reopen it.

Best regards,