[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3226) HQL/JPQL query with where clause expression involving mulitple correlated subqueries does not parse
Bob Tiernay (JIRA)
noreply at atlassian.com
Tue Apr 8 09:49:33 EDT 2008
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3226?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_29970 ]
Bob Tiernay commented on HHH-3226:
----------------------------------
Here is the AST:
\-[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'
\-[AND] 'AND'
+-[AND] 'AND'
| +-[AND] 'AND'
| | +-[NOT_IN] 'not in'
| | | +-[DOT] '.'
| | | | +-[DOT] '.'
| | | | | +-[IDENT] 'a'
| | | | | \-[IDENT] 'status'
| | | | \-[IDENT] 'name'
| | | \-[IN_LIST] 'inList'
| | | +-[QUOTED_STRING] ''nst''
| | | \-[QUOTED_STRING] ''ncl''
| | \-[EQ] '='
| | +-[DOT] '.'
| | | +-[IDENT] 'a'
| | | \-[IDENT] 'seed'
| | \-[FALSE] 'FALSE'
| \-[GT] '>'
| +-[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] 't'
| | | \-[SELECT] 'SELECT'
| | | \-[MINUS] '-'
| | | +-[IDENT] 'CURRENT_DATE'
| | | \-[AGGREGATE] 'MAX'
| | | \-[DOT] '.'
| | | +-[IDENT] 't'
| | | \-[IDENT] 'date'
| | \-[WHERE] 'WHERE'
| | \-[IN] 'in'
| | +-[DOT] '.'
| | | +-[IDENT] 'a'
| | | \-[IDENT] 'id'
| | \-[IN_LIST] 'inList'
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[IDENT] 't'
| | | | \-[IDENT] 'purchaser'
| | | \-[IDENT] 'id'
| | \-[DOT] '.'
| | +-[DOT] '.'
| | | +-[IDENT] 't'
| | | \-[IDENT] 'seller'
| | \-[IDENT] 'id'
| \-[NUM_INT] '183'
\-[GT] '>'
+-[PLUS] '+'
| +-[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'
| | | | \-[STAR] '*'
| | | | +-[DOT] '.'
| | | | | +-[DOT] '.'
| | | | | | +-[DOT] '.'
| | | | | | | +-[IDENT] 't1'
| | | | | | | \-[IDENT] 'asset'
| | | | | | \-[IDENT] 'price'
| | | | | \-[IDENT] 'value'
| | | | \-[DOT] '.'
| | | | +-[IDENT] 't1'
| | | | \-[IDENT] 'numberOfUnits'
| | | \-[WHERE] 'WHERE'
| | | \-[EQ] '='
| | | +-[DOT] '.'
| | | | +-[DOT] '.'
| | | | | +-[IDENT] 't1'
| | | | | \-[IDENT] 'purchaser'
| | | | \-[IDENT] 'id'
| | | \-[DOT] '.'
| | | +-[IDENT] 'a'
| | | \-[IDENT] 'id'
| | \-[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'
| | | \-[STAR] '*'
| | | +-[DOT] '.'
| | | | +-[IDENT] 't2'
| | | | \-[IDENT] 'totalPrice'
| | | \-[DOT] '.'
| | | +-[IDENT] 't2'
| | | \-[IDENT] 'numberOfUnits'
| | \-[WHERE] 'WHERE'
| | \-[EQ] '='
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[IDENT] 't2'
| | | | \-[IDENT] 'seller'
| | | \-[IDENT] 'id'
| | \-[DOT] '.'
| | +-[IDENT] 'a'
| | \-[IDENT] 'id'
| \-[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] 'GeneralLedger'
| | | \-[ALIAS] 'gl'
| | \-[SELECT] 'SELECT'
| | \-[MINUS] '-'
| | +-[AGGREGATE] 'SUM'
| | | \-[DOT] '.'
| | | +-[IDENT] 'gl'
| | | \-[IDENT] 'debit'
| | \-[AGGREGATE] 'SUM'
| | \-[DOT] '.'
| | +-[IDENT] 'gl'
| | \-[IDENT] 'credit'
| \-[WHERE] 'WHERE'
| \-[AND] 'AND'
| +-[EQ] '='
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[IDENT] 'gl'
| | | | \-[IDENT] 'glAccount'
| | | \-[IDENT] 'id'
| | \-[NUM_INT] '15'
| \-[EQ] '='
| +-[DOT] '.'
| | +-[DOT] '.'
| | | +-[IDENT] 'gl'
| | | \-[IDENT] 'account'
| | \-[IDENT] 'id'
| \-[DOT] '.'
| +-[IDENT] 'a'
| \-[IDENT] 'id'
\-[NUM_DOUBLE] '150.00'
> 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
More information about the hibernate-issues
mailing list