9:10 a.m.
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3226?page=c...
]
Bob Tiernay commented on HHH-3226:
----------------------------------
I've minimized/contrived the query to be as simple as possible to indicate the same
failure:
SELECT COUNT(a) FROM Account a WHERE ((SELECT SUM(1) FROM Trade t1) - (SELECT SUM(1) FROM
Trade t2)) = 0
0 [main] ERROR org.hibernate.hql.PARSER - <AST>:0:0: unexpected AST node: query
0 [main] ERROR org.hibernate.hql.PARSER - <AST>:0:0: unexpected AST node: query
\-[QUERY] 'query'
+-[SELECT_FROM] 'SELECT_FROM'
| +-[FROM] 'FROM'
| | \-[RANGE] 'RANGE'
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[DOT] '.'
| | | | | +-[DOT] '.'
| | | | | | +-[DOT] '.'
| | | | | | | +-[IDENT] 'com'
| | | | | | | \-[IDENT] 'cit'
| | | | | | \-[IDENT] 'venulum'
| | | | | \-[IDENT] 'domain'
| | | | \-[IDENT] 'entity'
| | | \-[IDENT] 'Account'
| | \-[ALIAS] 'a'
| \-[SELECT] 'SELECT'
| \-[COUNT] 'COUNT'
| \-[IDENT] 'a'
\-[WHERE] 'WHERE'
\-[EQ] '='
+-[MINUS] '-'
| +-[QUERY] 'query'
| | \-[SELECT_FROM] 'SELECT_FROM'
| | +-[FROM] 'FROM'
| | | \-[RANGE] 'RANGE'
| | | +-[DOT] '.'
| | | | +-[DOT] '.'
| | | | | +-[DOT] '.'
| | | | | | +-[DOT] '.'
| | | | | | | +-[DOT] '.'
| | | | | | | | +-[IDENT] 'com'
| | | | | | | | \-[IDENT] 'cit'
| | | | | | | \-[IDENT] 'venulum'
| | | | | | \-[IDENT] 'domain'
| | | | | \-[IDENT] 'entity'
| | | | \-[IDENT] 'Trade'
| | | \-[ALIAS] 't1'
| | \-[SELECT] 'SELECT'
| | \-[AGGREGATE] 'SUM'
| | \-[NUM_INT] '1'
| \-[QUERY] 'query'
| \-[SELECT_FROM] 'SELECT_FROM'
| +-[FROM] 'FROM'
| | \-[RANGE] 'RANGE'
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[DOT] '.'
| | | | | +-[DOT] '.'
| | | | | | +-[DOT] '.'
| | | | | | | +-[IDENT] 'com'
| | | | | | | \-[IDENT] 'cit'
| | | | | | \-[IDENT] 'venulum'
| | | | | \-[IDENT] 'domain'
| | | | \-[IDENT] 'entity'
| | | \-[IDENT] 'Trade'
| | \-[ALIAS] 't2'
| \-[SELECT] 'SELECT'
| \-[AGGREGATE] 'SUM'
| \-[NUM_INT] '1'
\-[NUM_INT] '0'
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira