[JIRA] (HHH-16347) The ORDER BY clause in a Window Function doesn't work when using multiple columns
by Vlad Mihalcea (JIRA)
Vlad Mihalcea ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZmNlMGYyMGQ3... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16347?atlOrigin=eyJpIjoiZmNlMG... ) HHH-16347 ( https://hibernate.atlassian.net/browse/HHH-16347?atlOrigin=eyJpIjoiZmNlMG... ) The ORDER BY clause in a Window Function doesn't work when using multiple columns ( https://hibernate.atlassian.net/browse/HHH-16347?atlOrigin=eyJpIjoiZmNlMG... )
Issue Type: Bug Affects Versions: 6.1.7 Assignee: Unassigned Components: hibernate-core Created: 21/Mar/2023 08:27 AM Priority: Major Reporter: Vlad Mihalcea ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... )
The following JPQL query works just fine:
List<Post> posts = session.createQuery("""
select p
from Post p
left join fetch p.comments pc
where p.id in (
select id
from (
select
id as id,
dense_rank() over (order by createdOn ASC) as ranking
from Post
where title like :titlePattern
) pr
where ranking <= :rank
)
""",
Post.class)
.setParameter("titlePattern", "High-Performance Java Persistence %")
.setParameter("rank", 5)
.getResultList();
However, if we change the ORDER BY clause of the dense_rank() function so that instead of:
dense_rank() over (order by createdOn ASC)
we use this:
dense_rank() over (order by createdOn, id ASC)
The query fails with the following error:
java.lang.UnsupportedOperationException
at org.hibernate.query.sqm.sql.internal.SqlAstProcessingStateImpl.getSelections(SqlAstProcessingStateImpl.java:122)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.resolveGroupOrOrderByExpression(BaseSqmToSqlAstConverter.java:2115)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSortSpecification(BaseSqmToSqlAstConverter.java:2313)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitOver(BaseSqmToSqlAstConverter.java:5456)
at org.hibernate.query.sqm.tree.expression.SqmOver.accept(SqmOver.java:132)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelection(BaseSqmToSqlAstConverter.java:2008)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectClause(BaseSqmToSqlAstConverter.java:1951)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:1819)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:416)
at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:122)
at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:213)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1679)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSubQueryExpression(BaseSqmToSqlAstConverter.java:6091)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSubQueryExpression(BaseSqmToSqlAstConverter.java:416)
at org.hibernate.query.sqm.tree.select.SqmSubQuery.accept(SqmSubQuery.java:641)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeFromClauseRoot(BaseSqmToSqlAstConverter.java:2570)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
at org.hibernate.query.sqm.tree.from.SqmFromClause.visitRoots(SqmFromClause.java:80)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFromClause(BaseSqmToSqlAstConverter.java:2365)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:1817)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:416)
at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:122)
at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:213)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1679)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSubQueryExpression(BaseSqmToSqlAstConverter.java:6091)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSubQueryExpression(BaseSqmToSqlAstConverter.java:416)
at org.hibernate.query.sqm.tree.select.SqmSubQuery.accept(SqmSubQuery.java:641)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWithInferredType(BaseSqmToSqlAstConverter.java:6200)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitInSubQueryPredicate(BaseSqmToSqlAstConverter.java:6863)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitInSubQueryPredicate(BaseSqmToSqlAstConverter.java:416)
at org.hibernate.query.sqm.tree.predicate.SqmInSubQueryPredicate.accept(SqmInSubQueryPredicate.java:87)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWhereClause(BaseSqmToSqlAstConverter.java:2276)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:1823)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:416)
at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:122)
at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:213)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1679)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:1477)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:416)
at org.hibernate.query.sqm.tree.select.SqmSelectStatement.accept(SqmSelectStatement.java:213)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.translate(BaseSqmToSqlAstConverter.java:711)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.buildCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:380)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:300)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:276)
at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:571)
at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:363)
at org.hibernate.query.sqm.internal.QuerySqmImpl.list(QuerySqmImpl.java:1073)
at org.hibernate.query.Query.getResultList(Query.java:94)
( https://hibernate.atlassian.net/browse/HHH-16347#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16347#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100218- sha1:518c67a )
3 years