BadJpqlGrammarException with latest release when using order by utl_raw.cast_to_varchar2((nlssort(lower(entityalias.namefield), 'nls_sort=binary_ai'))) ASC

[waldeckmatheus]

Hi,

Summary of current problem resulting in BadJpqlGrammarException when using Database Function

• Trying to upgrade Spring Boot 2.x to 3.1.2
• Problem in content of "org.springframework.data.jpa.repository.Query".
  • It's working from previous version: 2.7.x and 2.1.x
  • Occurs when declaring ORDER BY, WHERE CLAUSE and using Database Function with JPQL/HQL, like below.
    • Current ORM: Hibernate ORM core version 6.2.6.Final
    • Exception: org.springframework.data.jpa.repository.query.BadJpqlGrammarException

SELECT b
FROM MyEntity b
WHERE b.status = :status
      AND utl_raw.cast_to_varchar2((nlssort(lower(b.name), 'nls_sort=binary_ai'))) LIKE lower(:name)
ORDER BY utl_raw.cast_to_varchar2((nlssort(lower(b.name), 'nls_sort=binary_ai'))) ASC

---

• 1. We are facing a problem like
  • BadJpqlGrammarException with latest relase when using ORDER BY NULLS LAST #2962
  • but using in JPQL/HQL query:
    • order by Oracle Function like this one: utl_raw.cast_to_varchar2((nlssort(lower(jpaentityalias.namefield), 'nls_sort=binary_ai'))) ASC
    • and/or Oracle/Database function in "WHERE clause"
• 2. Currently version in pom.xml:
  • Spring Boot 3.1.2 (spring-data-bom version 2023.0.2)
• 3. Previous version used with Oracle Function in "order by" and "where clause" and returning ok:
  • 2.7.x and 2.1.x
• 4. Example of used query

@Query("select b "
			+ "from Bairro b "
			+ "where b.situacao = :situacao "
			+ "and utl_raw.cast_to_varchar2((nlssort(lower(b.nome), 'nls_sort=binary_ai'))) like lower(:nome) "
			+ "order by utl_raw.cast_to_varchar2((nlssort(lower(b.nome), 'nls_sort=binary_ai'))) ASC")
	List<Bairro> listBySituacaoNome(Integer situacao, String nome);

• 5. Translation portuguese to english
  • business Entity "bairro" -> neighborhood
  • word "situacao" -> situation or current status
• 6. Returned message when executing another query with same Oracle Function:

org.springframework.dao.InvalidDataAccessApiUsageException: org.springframework.data.jpa.repository.query.BadJpqlGrammarException: Line 1:109 mismatched input '(' expecting {<EOF>, ',', '+', '-', '/', '||', '[', '.', ASC, '*', BY, DAY, DESC, EPOCH, EXCEPT, FETCH, HOUR, INTERSECT, LIMIT, MINUTE, MONTH, NANOSECOND, NULLS, OFFSET, QUARTER, SECOND, UNION, WEEK, YEAR}; Bad JPQL grammar [select u from Unidade u JOIN u.municipio as m where u.situacao = :situacao  order by utl_raw.cast_to_varchar2((nlssort(lower(u.nome), 'nls_sort=binary_ai'))) ASC ]

• 7. Replacing or modifying query
  • X: "order by utl_raw.cast_to_varchar2((nlssort(lower(b.nome), 'nls_sort=binary_ai'))) ASC"
  • to Z: "order by b.nome ASC"
    • results success but it was lost "order/position" when using accentuated chars.
• 8. Attempts
  • Including Dialect on application properties: spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect
    • not solved
  • Creating database function to reduce subqueries or subfunctions in JPQL/HQL query and call simple function like: SCHEMAFROMDB.FN_UTLRAW_NLS_LOWER(b.nome)
    • not solved

Any advice?
Thank you for your time.

[waldeckmatheus]

Working Alternative - SOLVED using simplified expression

From attempt: Creating database function to reduce subqueries or subfunctions in JPQL/HQL query and call simple function like: SCHEMAFROMDB.FN_UTLRAW_NLS_LOWER(b.nome)

• It was resulting success with SIMPLE NAME of DATABASE FUNCTION, declared on query, WITHOUT DATABASE SCHEME explicited declaration, like below.

@Query("select b "
			+ "from Bairro b "
			+ "where b.situacao = :situacao "
			+ "and CTM_UTLRAW_NLSSORT_LOWER(b.nome) like lower(:nome) "
			+ "order by CTM_UTLRAW_NLSSORT_LOWER(b.nome) ASC")
	List<Bairro> listBySituacaoNome(Integer situacao, String nome);

[gregturn]

Fixed. Merged to main. Backported to 3.1.x.