JPASQL.limit causes syntax error on SQL Server

[nyc2]

Using JPASQL.limit in Querydsl 3.1.1 on SQL Server produces syntax errors:
11:30:19,835 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Warning: 102, SQLState: 42000
11:30:19,837 WARN  [org.hibernate.util.JDBCExceptionReporter] Preparing the statement failed: Incorrect syntax near '@P0'.
11:30:19,838 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 102, SQLState: 42000
11:30:19,839 ERROR [org.hibernate.util.JDBCExceptionReporter] Incorrect syntax near '@P0'.
11:30:19,841 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 156, SQLState: S1000
11:30:19,842 ERROR [org.hibernate.util.JDBCExceptionReporter] Incorrect syntax near the keyword 'group'.
javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.SQLGrammarException: could not execute query

SQL Server profiler reveals what's going wrong here:
declare @p1 int
exec sp_prepare @p1 output,N'@P0 bigint,...',N'select top @P0 ... from ...',1
select @p1

According to http://msdn.microsoft.com/en-us/library/ms189463.aspx the top-clause syntax is:

[ 
    TOP (expression) [PERCENT]
    [ WITH TIES ]
]

Therefore the top-expression must be surrounded by parentheses. Oddly enough, parentheses are optional in ad-hoc queries like select top 1 from Tbl but mandatory in parameterized queries.

In earlier Querydsl versions limiting was done by means of common table expressions and appropriate row_number selection like: with inner_query as (select ...) select * from inner_query where row_number <= @P0

By the way, the fetch-first-clause standardized by ISO could be of interest in future, see http://en.wikipedia.org/wiki/Select_%28SQL%29#FETCH_FIRST_clause.


Br,
nyc2

[nyc2]

Temporary workaround:
public class SQLServerTemplatesExt extends SQLServerTemplates {
    public SQLServerTemplatesExt() {
        super();
        fixTopTemplate();
    }
    public SQLServerTemplatesExt(boolean quote) {
        super(quote);
        fixTopTemplate();
    }
    public SQLServerTemplatesExt(char escape, boolean quote) {
        super(escape, quote);
        fixTopTemplate();
    }
    private void fixTopTemplate() {
        try {
            // Top expression must be surrounded by parentheses
            Field field = SQLServerTemplates.class.getDeclaredField("topTemplate");
            field.setAccessible(true);
            field.set(this, "top ({0}) ");
        }
        catch (Exception ex) {
            throw new RuntimeException(ex);
        }
    }
}

[nyc2]

Created an issue: https://github.com/mysema/querydsl/issues/424