[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5121) Using Oracle analytical functions causes QuerySyntaxException

[Tarek Nabil (JIRA)]

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

Tarek Nabil commented on HHH-5121:
----------------------------------

Yes

> Using Oracle analytical functions causes QuerySyntaxException
> -------------------------------------------------------------
>
>                 Key: HHH-5121
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5121
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: entity-manager
>    Affects Versions: 3.5.0-Final
>         Environment: Hibernate 3.5.0-Final.
> Oracle 10g.
>            Reporter: Tarek Nabil
>         Attachments: criteria-bug.zip
>
>
> The CriteriaBuilder.function() method allows the creation of an expression which is supported by the target DB such as DB functions.
> Using this facility to call Oracle 10g (and above) analytical functions such as "COUNT(*) OVER()" produces a QuerySyntaxException.
> The attached test case illustrates the problem. It's a minimal Maven2 project which contains one domain class and one DAO.
> The domain class maps to the REGION entity from the Oracle HR schema which ships with any Oracle XE installation. I still included the script needed to create the table incase the schema is not available on the test environment.
> The DAO has two methods, one selects all the regions and the other attempts to select all the regions and the count of records returned using "COUNT(*) OVER()". A test class is used to test those two methods; the first test works successfully; the second gives the stack trace provided in "stack-trace.txt".
> I believe this bug is a major one because:
> 1- To get the total count of records, the only other option is to do two queries which is a lot more expensive.
> 2- The count is required for pagination of results which is essential for search screens.
> 3- Search screens which require dynamic queries are the main reason one would use criteria in the first place.
> I've provided instructions on the changes required to setup the Maven project in order to run the test case, which are minimal, in "readme.txt".

-- 
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