Selecting DISTINCT with query.setFirstResult generates bad SQL for MSSQL Server 2019

[johnmanko]

According to Microsoft, the following issue is a problem with EclipseLink. Perhaps this can be verified.

Microsoft SQL Server JDBC Driver version

10.2.0 and 9.4.1

EclipseLink version

2.7.9.payara-p1

Payara Server version

5.2022.1

SQL Server version

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)   Sep 24 2019 13:48:23   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor) 

Client Operating System

Ubuntu 21.10

JAVA/JVM version

openjdk version "11.0.14.1" 2022-02-08
OpenJDK Runtime Environment (build 11.0.14.1+1-Ubuntu-0ubuntu1.21.10)
OpenJDK 64-Bit Server VM (build 11.0.14.1+1-Ubuntu-0ubuntu1.21.10, mixed mode, sharing)

Table schema

CREATE TABLE [TESTTABLE](
	[id] [varchar](255) NOT NULL,
	[UUID] [varchar](36) NOT NULL,
	[DESCRIPTION] [varchar](255) NULL
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[UUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Problem description

When using javax.persistence.criteria.CriteriaBuilder to query distinct records with an offset, the resulting SQL is invalid for SQL Server 2019.

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<TestTable> query = cb.createQuery(TestTable.class);
        Root<TestTable> entity = query.from(TestTable.class);
        query.distinct(true);
        // code
        TypedQuery<TestTable> q = em.createQuery(query);
        List<TestTable> list = q.setFirstResult(0).setMaxResults(10).getResultList();

The generated SQL does not work on 2019. Earlier version of SQL generate different SQL statements.

Generated:

SELECT DISTINCT ID, UUID, DESCRIPTION FROM TESTTABLE ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT null)) OFFSET ? ROWS FETCH NEXT ? ROWS ONLY

Generated with values:

SELECT DISTINCT ID, UUID, DESCRIPTION FROM TESTTABLE ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT null)) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Expected behavior

The follow is generated when running against Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ):

SET ROWCOUNT 10
SELECT DISTINCT ID, UUID, DESCRIPTION FROM TESTTABLE 

Actual behavior

SQL statement doesn't run because the generated ORDER BY and ROW_NUMBER() are not part of the DISTINCT clause.

Error message/stack trace

Error from Server Manager Studio:

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Server log:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.payara-p1): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Any other details that can be helpful

JDBC trace logs

None available.

[johnmanko]

This is still a problem with Payara 5.2022.3 (using Eclipselink 2.7.9.payara-p2) and MS SQL JDBC mssql-jdbc-11.2.0.jre11.jar.

Can I get some feedback on this? Again, according to Microsoft, this is an Eclipselink issue.

[johnmanko]

Related: #963