These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]

Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: Correlated subquery referencing problem in Formula
PostPosted: Mon Dec 08, 2008 2:57 pm 

Joined: Mon Dec 08, 2008 1:54 pm
Posts: 1

I'm trying to use @Formula to computed a "balance" property, which is the sum of the "amount" property on a set of detail records.

@Formula allows an SQL fragment, including subselects, as a property, so I'm trying to use a correlated subquery as I would in SQL. However I'm unable to refer to the outer table alias. The @Formula SQL fragment behaviour does not match the HQL subquery behaviour, where an unqualified ambiguous name would be resolved to the outer query.

Use case:

public class Account {
    @Id @Column(name="_rowid")
    int id;
    String a;
    String b;
    String c;

    @OneToMany(mappedBy="accountID", fetch=FetchType.LAZY)
    List<Fee> fees;

    @Formula("(select sum(f.amount) - sum(f.remitted) from Fee f " +
         "where f.accountID = _rowid and f.type = 'B')")
    BigDecimal balance;

    // Getters and setters omitted

public class Fee {
    @Id @Column(name="_rowid")
    int id;

    @ManyToOne @JoinColumn(name="accountID")
    Account account;

    BigDecimal amount;
    BigDecimal remitted;

    char type = 'B';

    // Getters and setters omitted

In the formula expression, on the balance property of Account, "_rowid" is a reference to the table in the containing query. Unfortunately _rowid is a column name in both the inner and outer tables. I would prefix this with a table alias, but I can't because I do not know the table alias as hibernate builds the query containing the formula.

In the generated SQL, the unqualified "_rowid" refers to the inner query (Fee f._rowid) not the outer query, which causes a horrendous query to be executed.

The hib docs: http://www.hibernate.org/hib_docs/annot ... ml#d0e2273
state that: "The SQL fragment can be as complex as you want and even include subselects." There is no mention however of correlated subselects.

Subqueries are supported in HQL queries:
http://www.hibernate.org/hib_docs/v3/re ... eries.html
http://docs.huihoo.com/hibernate/hibern ... ryhql.html (heading "Subqueries")
"Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed. "

The HQL subselect behaviour is to treat unqualified ambiguous aliases as references to the outer query. Databases seem to do the opposite, and treat them as references to the inner query (at least in Sybase and PostgreSQL).

If the formula could be an HQL scalar query I don't think I'd have this problem. Sadly when it is an SQL fragment it seems I am unable to when the same column name is in both the inner and outer tables.

I only have read-only access to the database in question and I cannot change column names as a workaround.

The only workaround I've found so far is putting the guessed prefix "account0_." on "_rowid" in the formula SQL fragment, which works for simple entity loading but unfortunately fails on criteria queries where the prefix is not "account0_" but instead is "this_.". (In the SQL of em.findById() and em.createQuery().getResults() the prefix hibernate generates is usually "account0_.", whereas in criteria queries it is "this_.".)

My environment is JBoss 4.2.0.GA, EJB3 code (using hibernate extensions).
Sybase 12 database (with "Sybase" dialect).

I'm going to have to remove the @Formulas but I can't think of another way to compute the balance, except by fetching the fees association and computing the sum myself when getBalance() is called.

Can anyone help me refer to the outer table, or suggest an alternative way to compute the balance property of these objects, without fetching all the "fees" detail records and computed the sum myself in code? The balance property is always accessed but the Fees association is rarely used and I don't want to fetch these records if I can avoid it.

If there is no alternative can Hibernate be extended to better support this use case?

Best Regards,


 Post subject: Re: Correlated subquery referencing problem in Formula
PostPosted: Fri May 21, 2010 4:43 pm 

Joined: Mon Mar 08, 2004 12:32 pm
Posts: 7
This is probably way too late to help you, but if you have an unqualified column name, Hibernate will prepend it with the table alias. I just discovered this now, and it's very good news for me. :-)

Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.