I think is should be considered a bug, but it says to post here first...

When applying getSingleResult() to a native stored procedure call query on MSSQL2000 hibernate the call (query text) is erronously modified by this code on line 64 of SQLServerDialect.java which assumes that the query is a "select" and if that's not the case (selectDistinctIndex == selectIndex == -1) causes "top 2" to be added where it would have been appropriate, had the query actually been a select distinct:

static int getAfterSelectInsertPoint(String sql) {
int selectIndex = sql.toLowerCase().indexOf( "select" );
final int selectDistinctIndex = sql.toLowerCase().indexOf( "select distinct" );
return selectIndex + ( selectDistinctIndex == selectIndex ? 15 : 6 );
}



Hibernate version: 3.3.1

Mapping documents:

Code between sessionFactory.openSession() and session.close():
Using EntityManager:

em.createNamedQuery("getNewOrderNo").setParameter("merchant", 70).getSingleResult();

refering to

@SqlResultSetMapping(name="getNewOrderNoMapping", columns={ @ColumnResult(name="order_no") } )
@NamedNativeQuery(name="getNewOrderNo", query="{call get_order_no(:merchant)}", resultSetMapping="getNewOrderNoMapping")

Full stack trace of any exception that occurs:

16:17:23,837 WARN JDBCExceptionReporter:100 - SQL Error: 170, SQLState: S0001
16:17:23,837 ERROR JDBCExceptionReporter:101 - Line 1: Incorrect syntax near '{'.
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:128)
at [my code above]
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:88)
... 1 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: Incorrect syntax near '{'.
at [...]
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 8 more

Name and version of the database you are using: SQL Server 2000

The generated SQL (show_sql=true):

{call get_orde top 2r_no(
?
)}