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

[Tarek Nabil (JIRA)]

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