Distinct with maxResults fails under SQLServer

Hibernate ORM
1

I’ve got a query using distinct and maxResults on an entity with jpql:

Query q = session.createQuery("select distinct idTypeConfig from TMIDTypeConfig idTypeConfig");
 if (maxResults != null) q.setMaxResults(maxResults);
List<TMIDTypeConfig> results = q.list();

When this executes, the sql generated is:

21 Dec 2022 17:22:33,078   DEBUG    [SQL [Session#2835de0c] main]   select distinct top (?) t1_0.ID_TYPE_PK,t1_0.CFIND_ISSUER,t1_0.CREATED_DATE_TIME,t1_0.DESCRIPTION,t1_0.LAST_UPDATED_DATE_TIME,t1_0.NAME from CFG_ID_TYPE_T t1_0 order by @@version

This causes an exception:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-8.2.1.jre11.jar:?]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) ~[mssql-jdbc-8.2.1.jre11.jar:?]

because hibernate adds “order by @@version” to the generated SQL when using maxResults();

Is there a way to prevent this? Or is there some other workaround?

The entity in here is simple, and this query doesn’t need the distinct, but the actual case where I found this in is more complicated, and the query joins to related entities, so the distinct is necessary.

2

Please create a JIRA issue for this. The simple workaround is that you simply specify an explicit ordering. The order by @@version is only added because in SQL Server it is illegal to use the top clause without order by.

3

Regarding:

The order by @@version is only added because in SQL Server it is illegal to use the top clause without order by.

In SQL Server Management Studio, I just ran the following without error:

select top 10  * from CFG_ID_TYPE_T;

SQL Server version is reported as Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

I created the issue as [HHH-15928] Distinct with maxResults fails under SQLServer - Hibernate JIRA (atlassian.net)

4

Might be that it is in subqueries where the order by is required or on older versions. We’ll investigate it. Thanks for the report.