search

darrachequesne / spring-data-jpa-datatables

Spring Data JPA extension to work with the great jQuery plugin DataTables (https://datatables.net/)
Apache License 2.0
448 stars 172 forks source link

Searching for a record with special (national) character "ř" doesn't work #158

Closed laubrino closed 8 months ago

laubrino commented 8 months ago

Steps to reproduce:

  1. create a table users or view in SQL Server DB with column name of type nvarchar
  2. insert a record into that table with Řehoř as a name
  3. search for Řehoř with datatables

This is how Hibernate debug log looks like:

2024-03-22 13:30:52.826 DEBUG --- [nio-8080-exec-2] org.hibernate.SQL                        :
    select
        pv1_0.id,
        pv1_0.name
    from
        users pv1_0 
    where
         lower(cast(pv1_0.name as varchar(max))) like ? escape '~' 

2024-03-22 13:30:52.826 TRACE --- [nio-8080-exec-2] org.hibernate.orm.jdbc.bind              : binding parameter (1:VARCHAR) <- [%řehoř%]
laubrino commented 8 months ago

I believe the problem is with the cast from nvarchar to varchar which will cause "ř" becoming "r" and therefore will not match to N'řehoř'. Is it a bug in here or do I have e.g. collation wrong? Currently it's SQL_Latin1_General_CP1_CI_AS

laubrino commented 8 months ago

I've noticed in debug mode that org.springframework.data.jpa.datatables.GlobalFilter.createPredicate(From<?, ?> from, CriteriaBuilder criteriaBuilder, String attributeName) is being called

darrachequesne commented 8 months ago

Hi! I could indeed reproduce the issue, let me dig into this.

darrachequesne commented 8 months ago

This should be fixed by https://github.com/darrachequesne/spring-data-jpa-datatables/commit/f1e0ecdcc73c3983683d4ddbcfe62fdc7862a70b, included in release 6.0.3. Could you please check?

laubrino commented 8 months ago

Yes, your fix works. Thank you for such quick fix! 👍