Is it possible to avoid adding " = true" for a boolean function in "where" clause of HQL query

Question

I am using PostgreSQL and JPA/Hibernate. I need to build an HQL query that has PostgreSQL regex comparison operator inside (name ~ 'pattern'). I have the following dialect:

public class PostgreSQLExtendedDialect extends PostgreSQL95Dialect {

    public PostgreSQLExtendedDialect() {
        registerFunction("regexp_matches", new SQLFunctionTemplate(BooleanType.INSTANCE, "(?1 ~ ?2)"));
    }

}

However, the following query ...

entityManager.createQuery("select p from Person p where regexp_matches(p.name, ?1)")
        .setParameter(1, "qwe")
        .getResultList();

... fails with following exception:

antlr.NoViableAltException: unexpected AST node: (
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2146)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:815)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:609)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:271)

In the same time, the following query ...

entityManager.createQuery("select p from Person p where regexp_matches(p.name, ?1) = true")
        .setParameter(1, "qwe")
        .getResultList();

... works, but produces the following weird SQL:

where
    person0_.name ~ ?=true

I would like to know if it's possible to avoid adding weird " = true" suffix to HQL part that is already a boolean expression. Ideally, maybe there is some ability to register just operator, so that I could write "p.name ~ ?1" directly in HQL.

Note. I know that I could write native query, but I have rather complex HQL query that would be a huge giant in SQL version, so I need to keep HQL.

Thanks!

Christian Beikov · Accepted Answer · 2020-05-22 14:37:11Z

2

It's not possible. Mabye in Hibernate 6, but there is no way to do it before because functions are always supposed to produce scalar values and if you need a predicate, you need to use a JPQL/HQL predicate.

You could introduce a custom SQLFunction implementation that renders the HQL function invocation regexp_matches(p.name, ?1) to regexp_matches(p.name, ?1) and true = so that the overall generated query will be regexp_matches(p.name, ?1) and true = true when using regexp_matches(p.name, ?1) = true.

PostgreSQL can optimize away the tautology true = true.

answered May 22, 2020 at 14:37

Christian Beikov

16.6k2 gold badges36 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is it possible to avoid adding " = true" for a boolean function in "where" clause of HQL query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related