EntityManager createQuery().setLimit() does not work with My

[dornus]

I have XP Pro, 4.0.3RC1, MySql 5.0.12 and ConnectorJ 3.1.10

I tried to run the following code and it gave me an error. When I comment out the setLimit() it works fine. I read through the forums and tried adding "?useServerPrepStmts=false" to my driver connection but that did not solve the problem.

Is this a bug?

 List <MyObj> results = em.createQuery( "from User p where p.emailAddress = :email order by p.lastName, p.firstName" )
 .setParameter( "email", emailAddress )
 .setFirstResult( 1 )
 .setMaxResults( 10 )
 .getResultList();

The error is:

2005-09-09 11:46:16,523 DEBUG [org.hibernate.SQL] select limit ? ? user0_.ownerId as ownerId, user0_.emailAddress as emailAdd3_20_,user0_.firstName as firstName20_, user0_.lastName as lastName20_, from user user0_ where user0_.emailAddress=? order by user0_.lastName, user0_.firstName
2005-09-09 11:46:16,523 DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement
2005-09-09 11:46:16,523 DEBUG [org.hibernate.loader.hql.QueryLoader] bindNamedParameters() [e-mail] -> email [3]
2005-09-09 11:46:16,523 DEBUG [org.hibernate.type.StringType] binding '[e-mail]' to parameter: 3
2005-09-09 11:46:16,523 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2005-09-09 11:46:16,523 DEBUG [org.hibernate.jdbc.AbstractBatcher] closing statement
2005-09-09 11:46:16,523 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection
2005-09-09 11:46:16,523 DEBUG [org.hibernate.jdbc.ConnectionManager] closing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2005-09-09 11:46:16,523 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute query [select user0_.ownerId as ownerId, user0_.emailAddress as emailAdd3_20_, user0_.firstName as firstName20_, user0_.lastName as lastName20_ from user user0_ where user0_.emailAddress=? order by user0_.lastName, user0_.firstName]
java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 1 10 user0_.ownerId as ownerId, user0_.' at line 1
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:930)
 at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1024)
 at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:296)
 at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
 at org.hibernate.loader.Loader.getResultSet(Loader.java:1537)

[dornus]

Correction. When I said comment out setLimit(), I meant to say comment out setMaxResults().

[dornus]

Anyone?

[epbernard]

It's a MySql bug
http://forum.hibernate.org/viewtopic.php?t=936854&highlight=limit+bug+mysql

[dornus]

Has this ever been resolved? Is there any links to the status of this request? Is there a workaround?

[dornus]

Anyone?

What exactly is the problem with this? Is it just because the limit clause comes at the top of the query instead of at the end of the query? If so, wouldn't it be much easier to change it to appear at the end of the query?

Even though it may be a bug in MySql, it makes Jboss's EJB3 look bad because so many people depend on this type of functionality. Things like this tend to steer people away from this product towards other ones which can implement their needs better.

It would probably take 1 or 2 lines of code to change the limit clause to appear at the bottom of the query. Then this problem (which has been lingering for 9 months without a resolution) could finally be resolved.