Panache Hibernate: generates wrong sql when using CAST

[nimo23]

Describe the bug

I use the following Panache filter query:

..
// JPQL-fragment throws exception
repository.filter("CAST(created as String) LIKE CONCAT(:created, '%')", "2024);
..

The JPQL fragment appears to be correct as it works when using this query in the Hibernate console. However, it seems that Panache JPA (or Hibernate?) converts this JPQL into the following SQL query, which then throws the following exception:

Schema "T1_0" not found; SQL statement:
select count(*) from Task t1_0 where CAST(t1_0.created as t1_0.String) LIKE CONCAT(?, '%') [90079-230]] [select count(*) from Task t1_0 where CAST(t1_0.created as t1_0.String) LIKE CONCAT(?, '%')]
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:66)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:191)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:153)
	at org.hibernate.sql.exec.internal.StandardStatementCreator.createStatement(StandardStatementCreator.java:49)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:235)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:171)
	at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.<init>(JdbcValuesResultSetImpl.java:74)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:355)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:137)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:102)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.executeQuery(JdbcSelectExecutor.java:91)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:165)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$1(ConcreteSqmSelectQueryPlan.java:152)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:442)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:362)
	at org.hibernate.query.sqm.internal.SqmSelectionQueryImpl.doList(SqmSelectionQueryImpl.java:299)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:136)
	at org.hibernate.query.spi.AbstractSelectionQuery.getSingleResult(AbstractSelectionQuery.java:268)
	at io.quarkus.hibernate.orm.panache.common.runtime.CommonPanacheQueryImpl.count(CommonPanacheQueryImpl.java:285)
	at io.quarkus.hibernate.orm.panache.runtime.PanacheQueryImpl.count(PanacheQueryImpl.java:145)
..
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: 
Schema "T1_0" not found; SQL statement:
select count(*) from Task t1_0 where CAST(t1_0.created as t1_0.String) LIKE CONCAT(?, '%') [90079-230]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:644)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)

Hibernate or Panache JPA wrongly converts the String-datatype to t1_0.String but String does not correspond to a field in the schema t1_0, it is simply a datatype needed for conversion.

When I use this JPQL no error is thrown and the generated SQL is correct.

..
// JPQL-fragment throws no exception, but this is not a valid JPQL (as it contains "varchar" instead of "String")
repository.filter("CAST(created as varchar) LIKE CONCAT(:created, '%')", "2024);
..

Expected behavior

No exception should be thrown for valid JQPL.

Actual behavior

Exception is thrown for valid JQPL.

How to Reproduce?

No response

Output of uname -a or ver

No response

Output of java -version

openjdk version "21.0.2" 2024-01-16

Quarkus version or git rev

3.16.1

Build tool (ie. output of mvnw --version or gradlew --version)

No response

Additional information

No response

[quarkus-bot]

/cc @FroMage (panache), @gsmet (hibernate-orm), @loicmathieu (panache), @yrodiere (hibernate-orm)

[FroMage]

This might be fixed by #41620 for Quarkus 3.17

[yrodiere]

This might be fixed by #41620 for Quarkus 3.17

I don't see a runnable reproducer here, so hard to tell :/

@nimo23 Can you please check your application against Quarkus 3.17? The latest snapshots for sure include the patch.

See https://github.com/quarkusio/quarkus/blob/main/CONTRIBUTING.md#checking-an-issue-is-fixed-in-main for instructions on how to use the latest snapshots of Quarkus in your application.

[nimo23]

I tried it today with Quarkus 3.17 and this issue remains:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Schema "T1_0" nicht gefunden
Schema "T1_0" not found; SQL statement:
select count(*) from Task t1_0 where CAST(t1_0.created as t1_0.String) LIKE CONCAT(?, '%') [90079-230]

You can try it yourself. Simply use a query like the following where you cast a java.time.timestamp into a String by JPQL (nothing else).

// created is a field declared within an entity "private Instant created;"
CAST(created as String) LIKE CONCAT(:created, '%')

The following version, using varchar instead of String, will work:

CAST(created as varchar) LIKE CONCAT(:created, '%')

[FroMage]

https://docs.jboss.org/hibernate/orm/6.6/userguide/html_single/Hibernate_User_Guide.html#pc-filter specifically states:

The @filter annotation is another way to filter out entities or collections using custom SQL criteria

And

This mapping was done to show you that the @filter condition uses a SQL condition and not a JPQL filtering predicate

But… also, the PanacheQuery.filter methods are supposed to take a filter name as first parameter, not a filter definition:

    /**
     * <p>
     * Enables a Hibernate filter during fetching of results for this query. Your filter must be declared
     * with {@link FilterDef} on your entity or package, and enabled with {@link Filter} on your entity.
     * <p>
     * WARNING: setting filters can only be done on the underlying Hibernate {@link Session} and so this
     * will modify the session's filters for the duration of obtaining the results (not while building
     * the query). Enabled filters will be removed from the session afterwards, but no effort is made to
     * preserve filters enabled on the session outside of this API.
     *
     * @param filterName The name of the filter to enable
     * @return this query, modified
     */
    public <T extends Entity> PanacheQuery<T> filter(String filterName);

So, I'm not sure what filter method you're calling and what is the type of your repository? It doesn't look like we're talking about the same thing here.

[nimo23]

This mapping was done to show you that the @filter condition uses a SQL condition and not a JPQL filtering predicate.

Ok, I didn't know that. So the following syntax is correct:

// correct syntax: uses sql
CAST(created as varchar) LIKE CONCAT(:created, '%')

// wrong syntax: uses jpql
CAST(created as String) LIKE CONCAT(:created, '%') 

I was calling the following method:

 /**
     * <p>
     * Enables a Hibernate filter during fetching of results for this query. Your filter must be declared
     * ...
     *
     * @param filterName The name of the filter to enable
     * @param parameters The set of parameters for the filter, if the filter requires parameters
     * @return this query, modified
     */
    public <T extends Entity> PanacheQuery<T> filter(String filterName, Parameters parameters);

Normally when using Hibernate you think you can use JPQL for this purpose (to remain database independent). However, in this case you may need to use SQL and implicitly risk losing database independence..