HIbernate is generating a SELECT statement using LIMIT (see above stack trace) that is being rejected. The coded select statement does not contain the LIMIT clause.

The config settings for the session factory are:
cfg.setProperty("hibernate.dialect", "net.sf.hibernate.dialect.HSQLDialect");
cfg.setProperty("hibernate.connection.driver_class", "org.hsqldb.jdbcDriver");


Is this a setup issue with Hibernate?

Hibernate version:2.1.4

Mapping documents:

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

Full stack trace of any exception that occurs:
java.sql.SQLException: Unexpected token: LIMIT in statement [select db3proje0_.dbId as dbId, db3proje0_.version as version, db3proje0_.userName as userName, db3proje0_.guid as guid, db3proje0_.event as event, db3proje0_.projectName as projectN6_, db3proje0_.ID_Secondary as ID_Secon7_, db3proje0_.ID_Production as ID_Produ8_, db3proje0_.archiveId as archiveId, db3proje0_.ID_Additional as ID_Addi10_, db3proje0_.projectTitle as project11_, db3proje0_.Title_Secondary as Title_S12_, db3proje0_.Title_Production as Title_P13_, db3proje0_.Title_Archive as Title_A14_, db3proje0_.Title_Additional as Title_A15_, db3proje0_.description as descrip16_, db3proje0_.Description_Secondary as Descrip17_, db3proje0_.Description_Sound as Descrip18_, db3proje0_.Description_Archive as Descrip19_, db3proje0_.Description_Subject as Descrip20_, db3proje0_.Description_Additional as Descrip21_, db3proje0_.comment as comment, db3proje0_.keywords as keywords, db3proje0_.createDate as createDate, db3proje0_.Date_Due as Date_Due, db3proje0_.projectStartDate as project26_, db3proje0_.lastModifyTime as lastMod27_, db3proje0_.Date_Completed as Date_Co28_, db3proje0_.broadcastDate as broadca29_, db3proje0_.lastBroadcastDate as lastBro30_, db3proje0_.Date_Repeat as Date_Re31_, db3proje0_.NumberRepeats as NumberR32_, db3proje0_.Date_Accessed as Date_Ac33_, db3proje0_.Date_Archived as Date_Ar34_, db3proje0_.inciteIdbPath as inciteI35_, db3proje0_.ipcFilePath as ipcFile36_, db3proje0_.Location_Tape as Locatio37_, db3proje0_.Location_Geographical as Locatio38_, db3proje0_.Location_Recording as Locatio39_, db3proje0_.Location_Additional as Locatio40_, db3proje0_.destinationProgram as destina41_, db3proje0_.destinationChannel as destina42_, db3proje0_.projectCreator as project43_, db3proje0_.editor as editor, db3proje0_.News_Editor as News_Ed45_, db3proje0_.Editor_in_Chief as Editor_46_, db3proje0_.Photographer as Photogr47_, db3proje0_.Reporter as Reporter, db3proje0_.Presenter as Presenter, db3proje0_.Participants as Partici50_, db3proje0_.Additional_Person as Additio51_, db3proje0_.journalist as journalist, db3proje0_.assignedTo as assignedTo, db3proje0_.Language as Language, db3proje0_.Medium as Medium, db3proje0_.Format_Standards as Format_56_, db3proje0_.Film_Type as Film_Type, db3proje0_.Sound_Type as Sound_Type, db3proje0_.VersionInfo as Version59_, db3proje0_.Category as Category, db3proje0_.Addition_Info as Additio61_, db3proje0_.projectGroup as project62_, db3proje0_.status as status, db3proje0_.Project_Priority as Project64_, db3proje0_.User_Project_Priority as User_Pr65_, db3proje0_.projectOrigin as project66_, db3proje0_.projectEventType as project67_, db3proje0_.parentProjectGuid as parentP68_, db3proje0_.archiveStatus as archive69_, db3proje0_.archiveTapeId as archive70_, db3proje0_.archiveTapeDesc as archive71_ from PROJECTS_DB3 db3proje0_ where (parentProjectGuid='611557f5-2c79-43aa-83b8-381988eae71a' ) order by projectName limit 0 , 1]
at org.hsqldb.Trace.getError(Unknown Source)
at org.hsqldb.jdbcResultSet.<init>(Unknown Source)
at org.hsqldb.jdbcConnection.executeStandalone(Unknown Source)
at org.hsqldb.jdbcConnection.execute(Unknown Source)
at org.hsqldb.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbcStatement.executeQuery(Unknown Source)
at org.hsqldb.jdbcPreparedStatement.executeQuery(Unknown Source)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:71)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:551)
at net.sf.hibernate.loader.Loader.doFind(Loader.java:140)
at net.sf.hibernate.loader.Loader.find(Loader.java:620)
at net.sf.hibernate.hql.QueryTranslator.find(QueryTranslator.java:928)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1343)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1322)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1314)


Name and version of the database you are using:
HSQLDB 1.7.2.2

Debug level Hibernate log excerpt:

code?
log?

I detect something similar. In the case of

session.createQuery("SELECT foo FROM myFoo AS foo LIMIT 100 OFFSET 0")

> net.sf.hibernate.QueryException: unexpected token: LIMIT ... is thrown. But for

session.createQuery("SELECT foo FROM myFoo AS foo ORDER BY foo.xxx LIMIT 100 OFFSET 0") the query is translated and executed fautless.

Do anybody know why this exception occures? Or did anybody have the same exception in an analogue case?

BTW: I don't want to use setMaxResult() and setFirstResult() ;-)

Hibernate version: 2.1.2
Database using: MySQL 4.0