Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Specifying parameters in sorting will throw an exception #40962

Open
sdlzhd opened this issue Jun 4, 2024 · 9 comments · May be fixed by #41620
Open

Specifying parameters in sorting will throw an exception #40962

sdlzhd opened this issue Jun 4, 2024 · 9 comments · May be fixed by #41620
Labels
area/panache kind/bug Something isn't working

Comments

@sdlzhd
Copy link

sdlzhd commented Jun 4, 2024

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

image
@sdlzhd sdlzhd added the kind/bug Something isn't working label Jun 4, 2024
@quarkus-bot
Copy link

quarkus-bot bot commented Jun 4, 2024

/cc @FroMage (panache), @loicmathieu (panache)
@FroMage
Copy link
Member

FroMage commented Jun 5, 2024

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
Copy link
Member

FroMage commented Jun 5, 2024

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
Copy link
Member

FroMage commented Jun 5, 2024

I could try using the new API for counting results in ORM :)
@FroMage
Copy link
Member

FroMage commented Jun 5, 2024

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
Copy link
Author

sdlzhd commented Jun 5, 2024

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
Copy link
Member

FroMage commented Jun 5, 2024

I've reported https://hibernate.atlassian.net/browse/HHH-18234
@FroMage
Copy link
Member

FroMage commented Jun 5, 2024

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
Copy link
Member

FroMage commented Jun 6, 2024

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.
FroMage added a commit that referenced this issue Jun 6, 2024
@FroMage
ORM/Panache: rely on ORM getResultCount
d2dcb5e 
This allows us to not care about which parameters are set on the count
query, since `order by` parameters are ignored by ORM. Which is not
the case if we write our own count query.

Note that this is not yet available in HR, so we have to keep the count
query logic around until that's available.

Fixes #40962
This was referenced Jun 6, 2024
Closed
Merged
FroMage added a commit to FroMage/quarkus that referenced this issue Jul 2, 2024
@FroMage
ORM/Panache: rely on ORM getResultCount
941cc29 
This allows us to not care about which parameters are set on the count
query, since `order by` parameters are ignored by ORM. Which is not
the case if we write our own count query.

Note that this is not yet available in HR, so we have to keep the count
query logic around until that's available.

Fixes quarkusio#40962
This was referenced Jul 2, 2024
Closed
Draft
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/panache kind/bug Something isn't working
3 participants
@FroMage @geoand @sdlzhd