[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3226) HQL/JPQL query with where clause expression involving mulitple correlated subqueries does not parse

[Bob Tiernay (JIRA)]

HQL/JPQL query with where clause expression involving mulitple correlated subqueries does not parse
---------------------------------------------------------------------------------------------------

                 Key: HHH-3226
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3226
             Project: Hibernate3
          Issue Type: Bug
    Affects Versions: 3.2.6
            Reporter: Bob Tiernay


The following named query will not parse:

@NamedQuery(name = "Account.findTotalInactiveCount",  query = "SELECT COUNT(a) FROM Account a WHERE a.status.name NOT IN ('nst', 'ncl') AND a.seed = FALSE AND (SELECT CURRENT_DATE - MAX(t.date) FROM Trade t WHERE a.id IN (t.purchaser.id, t.seller.id)) > 183 AND ((SELECT SUM(t1.asset.price.value * t1.numberOfUnits) FROM Trade t1 WHERE t1.purchaser.id = a.id) - (SELECT SUM(t2.totalPrice * t2.numberOfUnits) FROM Trade t2 WHERE t2.seller.id = a.id) + (SELECT SUM(gl.debit) - SUM(gl.credit) FROM GeneralLedger gl WHERE gl.glAccount.id = 15 AND gl.account.id = a.id)) > 150.00")

The error(s) issued from Hibernate are:

0    [main] ERROR org.hibernate.hql.PARSER  - <AST>:0:0: unexpected AST node: query
15   [main] ERROR org.hibernate.hql.PARSER  - <AST>:0:0: unexpected AST node: query

For readability, I have formatted the query:

SELECT	COUNT(a) 
FROM 	Account a 
WHERE 	a.status.name NOT IN ('nst', 'ncl') AND 
	a.seed = FALSE AND 
	(
		SELECT CURRENT_DATE - MAX(t.date) 
		FROM	Trade t 
		WHERE	a.id IN (t.purchaser.id, t.seller.id)
	) > 183 AND 
	(
		(SELECT	SUM(t1.asset.price.value * t1.numberOfUnits) 
		 FROM	Trade t1 
		 WHERE	t1.purchaser.id = a.id) - 
		
		(SELECT	SUM(t2.totalPrice * t2.numberOfUnits) 
		 FROM 	Trade t2 
		 WHERE 	t2.seller.id = a.id) + 
		
		(SELECT	SUM(gl.debit) - SUM(gl.credit) 
		 FROM	GeneralLedger gl 
		 WHERE 	gl.glAccount.id = 15 AND 
			gl.account.id = a.id)
	) > 150.00

As an aside, I've tried reproducing this query using the Criteria API with no luck.  There doesn't seem to be a way to combine the results of subqueries for comparison.

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