MSSQL: TOP keyword is appended in wrong place when select with DISTINCT

[longgt]

Describe the bug In MSSQL, execute a query with both DISTINCT and limit causes invalid syntax exception.

The root cause might come from the below code while Doma is trying to inject TOP keyword into SELECT clause https://github.com/domaframework/doma/blob/bcc3e124910e6cf4caebf0277f17d21d486fa730/doma-core/src/main/java/org/seasar/doma/internal/jdbc/dialect/Mssql2008PagingTransformer.java#L39

Expected behavior

As described in the link below, the TOP keyword should be appended after DISTINCT (if exists), not right after SELECT https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver16

Actual behavior The TOP keyword is append right after SELECT which causes invalid syntax error while executing.

To Reproduce Execute with offset = 0, limit = 50 for the below query

SELECT   DISTINCT FirstName,
                  LastName
FROM     Person.Person
ORDER BY LastName

The current generated SQL of Doma (at debug step of org.seasar.doma.internal.jdbc.dialect.MssqlPagingTransformer)

SELECT TOP(50) DISTINCT FirstName,
                  LastName
FROM     Person.Person
ORDER BY LastName

An exception with message Incorrect syntax near the keyword 'DISTINCT' was thrown.

It should be generated as

SELECT  DISTINCT TOP(50)  FirstName,
                  LastName
FROM     Person.Person
ORDER BY LastName

The workaround is creating a Dialect with forces to use the OFFSET FETCH Clause for a pagination.

@Configuration
public class DomaConfiguration {
    @Bean
    public Dialect dialect(Environment environment) {
        return new MssqlDialect(new MssqlJdbcMappingVisitor(), new MssqlSqlLogFormattingVisitor(), new MssqlExpressionFunctions(), true);
    }
}

Environment (please complete the following information):

• Doma version or git rev: 2.35.0
• IDE version (ie. Eclipse or IntelliJ IDEA): Eclipse
• Output of java -version: 1.8.0_371, vendor: Oracle Corporation
• Build tool (ie. output of mvnw --version or gradlew --version): Maven 3.9.5
• Database version: Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64)
• JDBC Driver version: com.microsoft.sqlserver:mssql-jdbc:10.2.3.jre8

[nakamura-to]

Thanks for your feedback.