SQLServer dialect issue with native sql

Description

SQLServer dialect issue with native sql when setLimit() is present. After investigating it is found that Hibernate is expecting a space after select keyword. That means if query has a newline (enter) after the select keyword. we see the below exception.

query that creates the below exception:
select

  • from employee e where e.first_name = :firstName

exception:

May 11, 2016 11:08:45 AM org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory <init>
INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: selec TOPt

  • from employee e where e.first_name = ?
    May 11, 2016 11:08:46 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
    WARN: SQL Error: 156, SQLState: S0001
    May 11, 2016 11:08:46 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
    ERROR: Incorrect syntax near the keyword 'TOP'.
    could not extract ResultSet
    org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
    at org.hibernate.loader.Loader.doQuery(Loader.java:910)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
    at org.hibernate.loader.Loader.doList(Loader.java:2554)
    at org.hibernate.loader.Loader.doList(Loader.java:2540)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
    at org.hibernate.loader.Loader.list(Loader.java:2365)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:353)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1909)
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:311)
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:141)
    at hibernate.test.QueryTest.main(QueryTest.java:101)
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'TOP'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
    ... 14 more

I have also check for the below query and it fails with the same above exception.
select
1

Attaching the test case to reproduce the issue.

Environment

None

Status

Assignee

Chris Cranford

Reporter

Anil

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

4.3.11

Priority

Major
Configure