Specifying parameters in sorting will throw an exception

[sdlzhd]

Describe the bug

I'm using Panache to simplify Hibernate queries. When I use the following query, I get an exception:

find("name = :name ORDER BY ST_Distance(location, :location) DESC", Map.of("name", "Demo","location", point))

point is a org.locationtech.jts.geom.Point

But, If I create the query directly using Hibernate, everything is fine.

this.getEntityManager().createQuery("FROM Store ORDER BY ST_Distance(location, :location)")
    .setParameter("name", "Demo")
    .setParameter("location", point);

Expected behavior

Set the parameters correctly

Actual behavior

UnknownParameterException: No parameter named ':location' in query with named parameters []

java.lang.IllegalArgumentException: No parameter named ':location' in query with named parameters []
	at org.hibernate.query.internal.ParameterMetadataImpl.getQueryParameter(ParameterMetadataImpl.java:262)
	at org.hibernate.query.spi.AbstractCommonQueryContract.setParameter(AbstractCommonQueryContract.java:826)
	at org.hibernate.query.spi.AbstractSelectionQuery.setParameter(AbstractSelectionQuery.java:882)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.setParameter(QuerySqmImpl.java:1200)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.setParameter(QuerySqmImpl.java:136)
	at io.quarkus.hibernate.orm.panache.common.runtime.AbstractJpaOperations.bindParameters(AbstractJpaOperations.java:162)
	at io.quarkus.hibernate.orm.panache.common.runtime.CommonPanacheQueryImpl.count(CommonPanacheQueryImpl.java:290)
	at io.quarkus.hibernate.orm.panache.runtime.PanacheQueryImpl.count(PanacheQueryImpl.java:144)
//// MORE ////

How to Reproduce?

In the description part gives a simple query, only need to create a table, containing the Point/geom type field;

Regardless of the database, the behavior is the same in MySQL and Postgresql

Output of uname -a or ver

Linux 5.15.146.1-microsoft-standard-WSL2 Ubuntu

Output of java -version

OpenJDK Runtime Environment Temurin-21.0.3+9

Quarkus version or git rev

3.11.0

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

maven 3.9.7

Additional information

The sort is not part of the query

[quarkus-bot]

/cc @FroMage (panache), @loicmathieu (panache)

[FroMage]

Hi,

Thanks for reporting. This is due to you calling a count() method on a query where one of the parameter is only used in the order by clause, which is not used by the count query (ordering is not useful for counting). That's rather unfortunate.

I'm not sure yet how to fix this.

As a workaround, you can drop the location parameter when using count.

[FroMage]

I could use the HQL parser to verify which parameters to not bind from the count query, but that's going to make it more expensive for everyone.

[FroMage]

I could try using the new API for counting results in ORM :)

[FroMage]

It appears to. Good news, this makes it upstream's problem next time it happens. But it breaks projections, I have to look at that.

[sdlzhd]

Thank you for your work. I am using paging query and I do not want to open a query to count.

PanacheQuery<Store> page = find(query, params).page(Page.of(0, 10));
long count = page.count();
List<Store> list = page.list();

[FroMage]

I've reported https://hibernate.atlassian.net/browse/HHH-18234

[FroMage]

Thank you for your work. I am using paging query and I do not want to open a query to count.

I understand, but in any case there will always be two queries, as the count query is executed separately, even if you don't see it. So as a workaround, to get you unstuck until we have a fix, you can create a separate query for counting.

[FroMage]

There's a fix for that in ORM 6.5.3. I'll make a PR to fix this when we upgrade to that version.