Description
marcusvoltolim opened DATAJPA-1780 and commented
QuerydslPredicateExecutor ->
- Page<T> findAll(Predicate predicate, Pageable pageable);
- Iterable<T> findAll(Predicate predicate, Sort sort);
Ordering with ignore case in a not String field is generating an order by lower(column) which totally bugs the sorting result, using pure Spring-JPA no problem occurs.
Bug occurs in Oracle: incorret order, why lower convert date type with pattern "dd/MM/yyyy", maybe locale is PT-br;
In PostgreSQL and HSQL throws exception!
Oracle: incorrect order by
SELECT test.*, lower(data)
FROM (
SELECT to_date('18/09/2020', 'dd/MM/yyyy') data, 'exampleOne' name
FROM dual
UNION
SELECT to_date('17/10/2020', 'dd/MM/yyyy') data, 'exampleTwo' name
FROM dual
) test
ORDER BY lower(data) ASCHSQL: Exception
java.sql.SQLSyntaxErrorException: incompatible data type in operation in statement [select example0_.id as id1_0_, example0_.local_date as local_da2_0_, example0_.local_date_time as local_da3_0_, example0_.name as name4_0_ from example example0_ where example0_.id is not null order by lower(example0_.local_date) asc]PostgreSQL: Exception
org.postgresql.util.PSQLException: ERROR: function lower(date) does not exist
Dica: No function matches the given name and argument types. You might need to add explicit type casts.
Posição: 203Correct implementation in org.springframework.data.jpa.repository.query.QueryUtils.toJpaOrder():
!https://user-images.githubusercontent.com/9442331/93636095-b2b26580-f9c9-11ea-8131-b705cbfeb2d1.png!
Bug implentation in org.springframework.data.jpa.repository.support.Querydsl.buildOrderPropertyPathFrom():
!https://user-images.githubusercontent.com/9442331/93636076-a928fd80-f9c9-11ea-8a8f-184a5666d169.png!
Fix:
private Expression<?> buildOrderPropertyPathFrom(Order order) {
Assert.notNull(order, "Order must not be null!");
PropertyPath path = PropertyPath.from(order.getProperty(), builder.getType());
Expression<?> sortPropertyExpression = builder;
while (path != null) {
sortPropertyExpression = !path.hasNext() && order.isIgnoreCase() && String.class == path.getType() //
? Expressions.stringPath((Path<?>) sortPropertyExpression, path.getSegment()).lower() //
: Expressions.path(path.getType(), (Path<?>) sortPropertyExpression, path.getSegment());
path = path.next();
}
return sortPropertyExpression;
}Affects: 2.3.4 (Neumann SR4)
Reference URL: https://github.com/marcusvoltolim/spring-jpa-example-order-bug
Issue Links:
- DATAJPA-1198 Querydsl Creates toLowerCase Expression for all Types
("duplicates")
Referenced from: pull request #428