SQLServerDialect does not wrap TOP statement in parenthesis

[loicprieur]

Using .take(x) with a runtime value on MS SQL Server causes the query to fail.

Moreover parenthesis are recommended by Microsoft: https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver16#compatibility-support

Version: 4.6.0
Module: quill-jdbc / quill-engine (not sure)
Database: mssqlserver

Expected behavior

Expected query to succeed and keep only the first x results (where x is take's parameter).

Actual behavior

query fails with:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'

Steps to reproduce the behavior

val limit = 10
val q = quote {
   query[Person].take(lift(limit))
}

ctx.run(q)

Workaround

2 options:

• drop and take which makes the dialect use the OFFSET FETCH syntax, which in turn requires the query to be sorted, and indexed.
• Use a custom Dialect with the correct syntax around TOP parameter.

@getquill/maintainers