QueryException on a HQL with a subquery

lean · **Joined:** Tue Jan 22, 2008 11:22 am **Posts:** 6

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.2.5.ga

Store mapping file

Code:

<hibernate-mapping>
   <class name="foo.Store" table="stores">
      <id name="id" column="storeId" access="field">
         <generator class="native" />
      </id>

      <property name="name" />

      <many-to-one name="street" class="foo.Street" column="streetId" />

      <bag name="comments" table="comments" cascade="all" inverse="true">
         <key column="storeId"></key>
         <one-to-many class="foo.Comment" />
      </bag>
   </class>
</hibernate-mapping>

Error: aggregate function expected before ( in SELECT

This is the HQL that I'm trying to execute:

Code:

select    distinct s.street.id, 
   s.street.name, 
   (select count(*) from s.comments cm where cm.enabled = true)
from    Store s 
where    s.street.id = ?

Is something wrong with the HQL or the mapping file? because it looks like the examples in the documentation.

Thanks.

lean · **Joined:** Tue Jan 22, 2008 11:22 am **Posts:** 6

If I add an alias to the subquery:

Code:

select    distinct s.street.id, 
                s.street.name, 
                (select count(*) from s.comments cm where cm.enabled = true) as cc
from      Store s 
where    s.street.id = ?

I get this error now:

Code:

undefined alias: s

Any clue?

EDIT: That was because there wasn't an space between the "cc" alias and the "from" clause. I'm still getting the previous error.

farzad · **Posted:** Tue Jan 22, 2008 1:16 pm

Just if makes you feel better I did a similar test in JPA and it worked with the first query you had. I am not convinced there is a huge different between HQL and whatever JPA standard supports, but there might be.

Farzad-

lean · **Joined:** Tue Jan 22, 2008 11:22 am **Posts:** 6

Could this be a bug in the HQL parser?

farzad · **Posted:** Tue Jan 22, 2008 1:22 pm

lean wrote:

Could this be a bug in the HQL parser?

I will look at it a little later today but I would say no. They might have quite a different syntax. Since my JPA provider is hibernate then whatever query I tested should be handed to hibernate and if it can do it then it can do it in its own way too.

Farzad-

farzad · **Posted:** Tue Jan 22, 2008 11:05 pm

It might look weird but the query works in HB. Here is the example I did:

A person can have one business car and can belong to multiple business areas:

Code:

<hibernate-mapping>
    <class name="test.model.data.Person" table="Person" mutable="true" >

        <id name="id" type="long" unsaved-value="null">
            <column name="ID"/>
            <generator class="identity"/>
        </id>

        <property name="name" type="string">
            <column name="Name"/>
        </property>

        <set name="areas" table="PersonArea">
            <key column="PersonID"/>
            <many-to-many column="AreaID" class="test.model.data.Area"/>
        </set>

        <many-to-one name="businessCar" column="BusinessCarID" class="test.model.data.Car"/>
    </class>
</hibernate-mapping>

and for Area:

Code:

<hibernate-mapping>
    <class name="test.model.data.Area" table="Area" mutable="true">

        <id name="id" type="long" unsaved-value="null">
            <column name="ID"/>
            <generator class="identity"/>
        </id>

        <property name="name" type="string">
            <column name="Name"/>
        </property>
    </class>

</hibernate-mapping>

and for Car:

Code:

<hibernate-mapping>
    <class name="test.model.data.Car" table="CAR" mutable="true" >

        <id name="id" type="long" unsaved-value="null">
            <column name="ID"/>
            <generator class="identity"/>
        </id>

        <property name="name" type="string">
            <column name="Name"/>
        </property>
    </class>
</hibernate-mapping>

and here is the test code:

Code:

public class Driver18
{
    public static void main(String[] args)
    {
        SessionFactory sf = new Configuration().configure().buildSessionFactory();
        Session session = sf.getCurrentSession();

        session.beginTransaction();
        Query query = session.createQuery("select distinct p.businessCar.id, p.businessCar.name, (select count(*) from p.areas ar where ar.name like '%First%') from test.model.data.Person p where p.businessCar.id = 1");


        List<Object[]> results = query.list();

        for (Object[] res : results)
        {
            print(res);
        }

        session.getTransaction().commit();
        sf.close();
    }

    private static void print(Object[] res)
    {
        System.out.print(">>> [");

        for (int i = 0; i < res.length; i++)
        {
            System.out.print(res[i]);

            if (i < res.length - 1)
                System.out.print(", ");
        }

        System.out.println("]");
    }
}

and here is the generated sql:

Code:

    select
        distinct person0_.BusinessCarID as col_0_0_,
        car1_.Name as col_1_0_,
        (select
            count(*) 
        from
            PersonArea areas2_,
            Area area3_ 
        where
            person0_.ID=areas2_.PersonID 
            and areas2_.AreaID=area3_.ID 
            and (
                area3_.Name like '%First%'
            )) as col_2_0_ 
    from
        Person person0_,
        CAR car1_ 
    where
        person0_.BusinessCarID=car1_.ID 
        and person0_.BusinessCarID=1

I am testing this against SQL server. I am wondering if the difference could be the dialect. See if you see the dialect class in the stack trace of the exception you get. It might be simply your db does not support this kind of things. Also try to switch to another database to see if the error goes away. I am using the same hibernate version by the way.

Ah I was going to forget. I don't get your comments mapping. A <one-to-many> with a join table is not usually like that, as per my understanding. I do believe that table attribute is going to be ignored but I doubt if it will result in the error you get. That should give you SQL error when it talks to db.

Farzad-

vforvikash · **Posted:** Wed Jan 23, 2008 5:35 am

According to me the query is wrong.
Since the inner query "(select count(*) from s.comments cm where cm.enabled = true)" is having alias 's' which is not recognized.

So it will be better to change the query like:
select distinct s.street.id,
s.street.name,
(select count(s.comments) from Store s where s.street.id = ? and cm.enabled = true)
from Store s where s.street.id = ?

so for the inner query alias 's' will be fine.

lean · **Joined:** Tue Jan 22, 2008 11:22 am **Posts:** 6

farzad wrote:

Ah I was going to forget. I don't get your comments mapping. A <one-to-many> with a join table is not usually like that, as per my understanding. I do believe that table attribute is going to be ignored but I doubt if it will result in the error you get. That should give you SQL error when it talks to db.

Farzad-

It makes sense to me, because the other option would be many-to-many, but that wasn't the case.

Would you do it different?

vforvikash wrote:

According to me the query is wrong.
Since the inner query "(select count(*) from s.comments cm where cm.enabled = true)" is having alias 's' which is not recognized.

So it will be better to change the query like:
select distinct s.street.id,
s.street.name,
(select count(s.comments) from Store s where s.street.id = ? and cm.enabled = true)
from Store s where s.street.id = ?

so for the inner query alias 's' will be fine.

I'll try it, but according to the documentation it's allowed what I did:
http://www.hibernate.org/hib_docs/refer ... subqueries

Thanks

farzad · **Posted:** Wed Jan 23, 2008 9:50 am

lean wrote:

It makes sense to me, because the other option would be many-to-many, but that wasn't the case.

Would you do it different?

Explain the relation. I haven't seen the other side of the relation.

Farzad-

lean · **Joined:** Tue Jan 22, 2008 11:22 am **Posts:** 6

farzad wrote:

Explain the relation. I haven't seen the other side of the relation.

The comment class mapping is:

Code:

<hibernate-mapping>
   <class name="foo.Comment" table="comments">
      <id name="id" column="commentId" access="field">
         <generator class="native" />
      </id>
      <property name="comment" />
      <property name="tstamp" />
      <many-to-one name="store" class="foo.Store" column="storeId" />
   
   </class>
</hibernate-mapping>

farzad · **Posted:** Wed Jan 23, 2008 1:00 pm

It's fine. I do believe the table attribute on the bag tag is ignored and that's why you don't have a problem with that. How about the database thing? What's the status of this now?

Farzad-