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.

Attachments

1

Activity

Show:

Steve Ebersole May 11, 2016 at 7:47 PM

Chris, is there something especially tricky or controversial in your PR? If not just push it

Chris Cranford May 11, 2016 at 4:06 PM

There was a slight problem still with the PR fix as it relates to this specific issue with parsing.
I'll update the PR related to to include this.

Chris Cranford May 11, 2016 at 3:36 PM

I'll double check, but I have a pending PR which addresses the parsing problem in the SQL Server limit handler logic which may address this problem.

https://github.com/hibernate/hibernate-orm/pull/1349

Fixed

Details

Assignee

Reporter

Fix versions

Affects versions

Priority

Created May 11, 2016 at 3:31 PM
Updated June 2, 2016 at 3:51 AM
Resolved May 12, 2016 at 3:24 PM