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

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