Oracle text "WHERE contains(...) > 0" clause in Query won't pass startup validation
Description
In hibernate 6.3, there is not possible to use oracle text “contains” function in HQL query. Application startup fails on query validations.
@Query("SELECT acct FROM Account acct WHERE contains(acct.oracle_text_indexed_col, '%' || :searchText || '%', 1) > 0 ")
possible workaround
@Query("SELECT acct FROM Account acct WHERE contains(acct.oracle_text_indexed_col, '%' || :searchText || '%', 1) > :parameterJavaTypeIntegerValueOf0 ")// -> parameterJavaTypeIntegerValueOf0 => "Integer.valueOf(0)"
exception:
Caused by: org.hibernate.query.SemanticException: Cannot compare left expression of type 'java.lang.Object' with right expression of type 'java.lang.Integer'
at org.hibernate.query.sqm.internal.TypecheckUtil.assertComparable(TypecheckUtil.java:338) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.<init>(SqmComparisonPredicate.java:48) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.<init>(SqmComparisonPredicate.java:34) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
at org.hibernate.query.hql.internal.SemanticQueryBuilder.createComparisonPredicate(SemanticQueryBuilder.java:2447) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:2391) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:268) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
at org.hibernate.grammars.hql.HqlParser$ComparisonPredicateContext.accept(HqlParser.java:6071) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:2270) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:268) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
at org.hibernate.grammars.hql.HqlParser$OrPredicateContext.accept(HqlParser.java:6158) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:2269) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:268) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final]
....
Debugger in org.hibernate.query.sqm.internal.TypecheckUtil.assertComparable(TypecheckUtil.java):
In hibernate 6.3, there is not possible to use oracle text “contains” function in HQL query. Application startup fails on query validations.
@Query("SELECT acct FROM Account acct WHERE contains(acct.oracle_text_indexed_col, '%' || :searchText || '%', 1) > 0 ")possible workaround
@Query("SELECT acct FROM Account acct WHERE contains(acct.oracle_text_indexed_col, '%' || :searchText || '%', 1) > :parameterJavaTypeIntegerValueOf0 ") // -> parameterJavaTypeIntegerValueOf0 => "Integer.valueOf(0)"exception:
Caused by: org.hibernate.query.SemanticException: Cannot compare left expression of type 'java.lang.Object' with right expression of type 'java.lang.Integer' at org.hibernate.query.sqm.internal.TypecheckUtil.assertComparable(TypecheckUtil.java:338) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.<init>(SqmComparisonPredicate.java:48) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.<init>(SqmComparisonPredicate.java:34) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] at org.hibernate.query.hql.internal.SemanticQueryBuilder.createComparisonPredicate(SemanticQueryBuilder.java:2447) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:2391) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:268) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] at org.hibernate.grammars.hql.HqlParser$ComparisonPredicateContext.accept(HqlParser.java:6071) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:2270) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:268) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] at org.hibernate.grammars.hql.HqlParser$OrPredicateContext.accept(HqlParser.java:6158) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:2269) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitOrPredicate(SemanticQueryBuilder.java:268) ~[hibernate-core-6.3.2.Final.jar:6.3.2.Final] ....Debugger in org.hibernate.query.sqm.internal.TypecheckUtil.assertComparable(TypecheckUtil.java):