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

[Martijn Verburg (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4761?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=36785#action_36785 ] 

Martijn Verburg commented on HHH-4761:
--------------------------------------

Any triage on this yet?

> 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