[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-4761) Projection row count fails with MySQL causing paged results to fail
Gail Badner (JIRA)
noreply at atlassian.com
Thu Apr 29 15:44:29 EDT 2010
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4761?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=36787#action_36787 ]
Gail Badner commented on HHH-4761:
----------------------------------
This may be an issue for other dialects as well.
Please checkout the trunk version http://anonsvn.jboss.org/repos/hibernate/core/trunk/, update CriteriaQueryTest.java to reproduce your issues, and attach a patch using "svn diff".
Thanks,
Gail
> 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
More information about the hibernate-issues
mailing list