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

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!