[hibernate-issues] [Hibernate-JIRA] Created: (HHH-4761) Projection row count fails with MySQL causing paged results to fail

[Martijn Verburg (JIRA)]

Projection row count fails with MySQL causing paged results to fail
-------------------------------------------------------------------

                 Key: HHH-4761
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4761
             Project: Hibernate Core
          Issue Type: Bug
          Components: core
    Affects Versions: 3.3.2
         Environment: MySQL community 5.1.41 with latest J connector, running on Jboss 4.2.3.GA (Sun JDK 1.5.0_x), This case exists with MySQL, MySQL5 and MySQL5InnoDB dialects
            Reporter: Martijn Verburg


Hi all

I have a piece of Java code that gets back a paged result set is as follows

  // Lots of general criteria above this which generates the WHERE clause
  ..
  ..
  criteria.setMaxResults(maxResults);
  criteria.setFirstResult(firstResult);
  criteria.addOrder(Order.desc("id"));
  
  // Execute the actual search (**this works and brings back the expected list**)
  List<WiretapEventHeader> wiretapResults = criteria.list();

  // Now execute a std hibernate method to get the rowcount so we can page correctly
  criteria.setProjection(Projections.rowCount());
  Integer rowCount = 0;
  List<Integer> rowCountList = criteria.list();
  
  // BUG? We never go into this if case if we go beyond the first page
  if (!rowCountList.isEmpty())
  {
    rowCount = rowCountList.get(0);
  }
  // Continuation of BUG? Always pass in 0 as rowCount when we go beyond the first page
  return new PagedWiretapSearchResult(wiretapResults, rowCount, firstResult);

* The SQL it runs to perform a rowcount when listing the first page (which is successful) is:

select count(*) as y0_ from IkasanWiretap this_ where this_.ModuleName in (?, ?) order by this_.Id asc limit ?

* The SQL it runs to perform a rowcount when listing the subsequent pages (BUG? not successful) is:

select count(*) as y0_ from IkasanWiretap this_ where this_.ModuleName in (?, ?) order by this_.Id asc limit ?, ?

I simplified this down to the examples I gave the MySQL guys in http://bugs.mysql.com/bug.php?id=50005 and ran those from the MySQL query console, when providing the limit ?, ? case, you simply do not get back what I think are sensible results.  In fact more often than not you get NULL list returned which causes the PagedWiretapSearchResult(wiretapResults, rowCount, firstResult) to execute with 0 rowCount which then returns a pagedResultSet with resultSize == 0

So I think Hibernate core needs to use alternative SQL to get the Projected rowcount from MySQL5

I've run the exact same code against Sybase without error.

Hope that all made sense, let me know if you need more details!

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira