Uploaded image for project: 'Hibernate ORM'
  1. HHH-10736

SQLServer dialect issue with native sql

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects versions: 4.3.11
    • Fix versions: 5.0.10, 5.2.0, 5.1.1
    • Components: None
    • Labels:
      None
    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true
    • Sprint:

      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 TOP(?)t

      • 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

          Issue links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: