MSSQL에서 offset 관련 UnsupportedOperationException: query result offset is not supported 에러 발생

MSSQL offset 에러

에러 발생

MSSQL을 사용하면서 JPA와 QueryDSL을 활용하여 페이징 처리를 하다가 첫 페이지 조회 할때는 TOP 쿼리를 사용하여 데이터를 성공적으로 가져오지만, 두 번째 페이지부터는 OFFSET을 사용해야 하는데 이때 OFFSET이 존재하지 않는다는 에러가 발생했다.

 

 

에러 로그

에러 로그는 다음과 같다.

 

[12:27:08.181][DEBUG] org.springframework.orm.jpa.JpaTransactionManager[doCleanupAfterCompletion:648] - Closing JPA EntityManager [SessionImpl(1815722544<open>)] after transaction [12:27:08.181][INFO ] org.springframework.test.context.transaction.TransactionContext[endTransaction:139] - Rolled back transaction for test: [DefaultTestContext@5c6fae3c testClass = RepositoryImplTest, testInstance = RepositoryImplTest@1bef1304, testMethod = tempMethodPageTwoSuccess@RepositoryImplTest, testException = java.lang.UnsupportedOperationException: query result offset is not supported, mergedContextConfiguration = [MergedContextConfiguration@504216ff testClass = RepositoryImplTest, locations = '{}', classes = '{class ServiceApplication}', contextInitializerClasses = '[]', activeProfiles = '{local}', 

 

에러 분석

java.lang.UnsupportedOperationException: query result offset is not supported 오류는 SQL Server에서 페이지네이션을 처리하는 과정에서 offset을 사용하는 쿼리를 실행할 때 발생할 수 있다. 이는 특히 SQL Server의 특정 버전에서 OFFSET-FETCH 구문을 지원하지 않을 때 더 흔하게 나타난다. SQL Server 2012 이전 버전은 이 구문을 지원하지 않는다.

 

 

해결 방안

해결 방안에는 두가지가 있다. 일단 SQL Server 버전을 확인해보거나 Hibernate Dialect 설정을 수정하는것이다.

 

1. SQL Server 버전 확인하기

SQL Server의 버전을 확인하고, 만약 SQL Server 2012 이전 버전을 사용 중이라면 OFFSET-FETCH 구문 대신 다른 방법으로 페이지네이션을 구현해야한다. 예를 들어, ROW_NUMBER() 함수를 사용하는 방법이 있다.

 

SQL Server의 버전을 확인하려면, SQL Server Management Studio (SSMS)나 다른 SQL 클라이언트를 사용하여 다음 SQL 쿼리를 실행하면 된다:

 

SELECT @@VERSION;

 

이 쿼리는 SQL Server의 버전 정보를 포함한 문자열을 반환한다. 반환된 정보에서, SQL Server의 정확한 버전 번호와 빌드 번호를 확인할 수 있다. 예를 들어, "SQL Server 2012 (SP1) - 11.0.3000.00 (X64)"와 같은 문자열이 반환될 수 있으며, 여기서 "11.0.3000.00"이 버전 번호다.

 

위 쿼리를 실행하면 나는 다음과 같이 나왔다.

 

SQL Server 버전 확인 결과

 

이 결과에서 "15.0.4223.1"은 SQL Server 2019의 버전 번호이며, 이는 SQL Server 2012(버전 번호 11.x) 이후에 출시된 버전이다.

 

그럼 일단 이 문제는 아닌것같다.

 

2. Hibernate Dialect 설정

Hibernate 또는 Spring Data JPA를 사용하는 경우, application.properties 또는 application.yml 파일에서 적절한 SQL Server Dialect를 설정해야 한다. 예를 들어, SQL Server 2012 이상 버전을 사용하는 경우 다음과 같이 설정할 수 있다:

 

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

 

이 설정은 Hibernate가 OFFSET-FETCH 구문을 올바르게 생성하도록 도와준다.

 

기존에 내가 사용하던 dialect 설정은 다음과 같다.

 

jpa:  
    database-platform: org.hibernate.dialect.SQLServerDialect  

 

이거를 다음과 같이 수정했다.

jpa:  
    database: sql-server  
    database-platform: org.hibernate.dialect.SQLServer2012Dialect  

 

이렇게 수정하고 나니 2페이지도 잘 나온다!

WITH query AS (SELECT
        inner_query.*,
        ROW_NUMBER() OVER (
    ORDER BY
        CURRENT_TIMESTAMP) as __hibernate_row_nr__ 
    FROM
        ( select
            data0_.id as col_0_0_,
            data0_.name as col_1_0_ 
        from
            tbl_data dataent0_ ) inner_query ) SELECT
            col_0_0_,
            col_1_0_ 
        FROM
            query 
        WHERE
            __hibernate_row_nr__ >= ? 
            AND __hibernate_row_nr__ < ?

 

 

근본적인 해결 원인

실제 데이터베이스가 SQL Server 2019 버전임에도 불구하고 발생한 java.lang.UnsupportedOperationException: query result offset is not supported 오류의 근본 원인은 JPA에서 사용하는 Hibernate Dialect 설정에 있었다. 실제 데이터베이스 환경이 SQL Server 2019와 같이 최신 버전임에도 불구하고, JPA(Hibernate) 설정에서 사용된 Dialect가 기본값이거나 SQL Server 2012 이전 버전을 기반으로 하는 설정이었기 때문에 발생한 문제였다.

 

SQL Server 2019는 OFFSET-FETCH 구문을 포함한 다양한 최신 SQL 기능을 지원한다. 이러한 최신 기능을 사용하기 위해서는 JPA(Hibernate)에서도 이를 인식하고 올바르게 처리할 수 있도록 적절한 Dialect 설정이 필요하다. OFFSET-FETCH 구문은 SQL Server 2012부터 도입된 페이징 처리를 위한 표준 SQL 구문이며, 이를 통해 보다 효율적인 데이터 액세스가 가능해진다.

 

문제가 발생한 이유는 JPA(Hibernate) 설정에서 사용된 Dialect가 SQL Server의 이러한 최신 기능을 인식하지 못하고, 구버전의 데이터베이스를 위한 페이징 처리 방식을 시도했기 때문이다. 결과적으로, 최신 버전의 SQL Server에서 지원하는 OFFSET-FETCH 기반의 페이징 처리를 수행할 수 없었고, 이로 인해 해당 오류가 발생했다.

 

이 문제를 해결하기 위해 database-platform 설정을 org.hibernate.dialect.SQLServer2012Dialect로 명시적으로 변경함으로써, Hibernate가 SQL Server 2012 이상에서 도입된 최신 SQL 기능, 특히 OFFSET-FETCH 구문을 올바르게 인식하고 사용할 수 있게 되었다. 이 변경을 통해 JPA(Hibernate)는 실제 데이터베이스 환경인 SQL Server 2019의 기능을 정확히 반영하여 효율적인 페이징 처리를 수행할 수 있게 되었으며, 오류를 해결할 수 있었다.

 

결론적으로, 실제 데이터베이스 버전과 JPA(Hibernate) 설정 사이의 불일치가 문제의 원인이었으며, 적절한 Dialect 설정을 통해 데이터베이스의 최신 기능을 정확히 활용하고, 페이징 처리와 같은 중요한 작업을 성공적으로 수행할 수 있게 되었다!!