Hibernate version: 3.1.2
Name and version of the database you are using: Oracle 9.2

Hi,

I am executing a hql query that has a couple of subselects, and an order by clause. It is to search a database for people with a firstname, city, and email criteria. For some reason the generated sql has missing criteria after the email subselect.

Here is the HQL:

select
party from Party party
where
party.urn in
(
select
party.urn
from
Party party,
Contactpointmap contactpointmap,
Contactpoint contactpoint
where
party.urn=contactpointmap.party.urn and
contactpointmap.contactpoint.contactpointurn=contactpoint.contactpointurn and
lower(contactpoint.electronicaddress) like '%@%')
) and
party.urn in
(
select
party.urn
from
Party party,
Contactpointmap contactpointmap,
Contactpoint contactpoint,
Address address
where
party.urn=contactpointmap.party.urn and
contactpointmap.contactpoint.contactpointurn=contactpoint.contactpointurn and
contactpoint.address.addressid=address.addressid and
lower(address.city) like 'dublin'
) and
lower(party.person.firstname) like 'a%' and
(
party.person is not null and
party.person.purn <> 0
)
order by
party.person.lastname,
party.person.firstname asc

So everything after that first subselect is missing from the sql. If I move all of the other criteria before that email subselect, they are included. But still the order by clause is omitted. There is nothing unusual in the hbm files. And it seems to be only affected by where the email subselect is, it doesn't matter where the other subselect is. If I remove the email subselect, everything works as expected.

The generated sql still executes without error, and returns results expected for the sql, but only applys the email criterion.

Am I doing something wrong? Thanks if you can help.

Juo

try to simplify your HQL to something like this:
select
contactpointmap.party
from
Party party,
Contactpointmap contactpointmap,
Contactpoint contactpoint ,
Address address
where
contactpointmap.contactpoint.contactpointurn=contactpointmap.party.urn and
lower(contactpoint.electronicaddress) like '%@%' and
lower(address.city) like 'dublin'
lower(contactpointmap .party.person.firstname) like 'a%' and
contactpointmap .party.person is not null and
contactpointmap .party.person.purn <> 0

order by
contactpointmap .party.person.lastname,
contactpointmap .party.person.firstname asc

Thanks Konstantin,

I wish I could do that, unfortunately there is a different contactpoint record for each address, email, etc. So I can't directly join their criteria, that would only work if they were linked to the same record. So I must do separate subselects.

Even still, there isn't anything wrong with the query, not that I can see, I can't see why it would drop the criteria after a specific subselect. Anyone any ideas?

Thanks again,
Juo

Then simply use SQL queries support in H
http://www.hibernate.org/hib_docs/v3/re ... rysql.html

although I strongly advice against using subqueries and 'in' condition because of their inherently slow performance.

Thanks again Konstantin,

One thing I could do is reference the same tables more than once with a different alias, per criterion. Eg
select party from
Party party,
Contactpointmap cpm1,
Contactpoint cp1,
Contactpointmap cpm2,
Contactpoint cp2,
Address a2
where
party.urn=cpm1.party.urn and cpm1.contactpoint.contactpointurn=cp1.contactpointurn and
cp1.electronicaddress like '%@%' and
party.urn=cpm2.party.urn and cpm2.contactpoint.contactpointurn=cp2.contactpointurn and
cp2.address.addressid=a2.addressid and
a2.city = 'Dublin'

Is this what you were suggesting? This should work, I'll have to see is it more efficient than a subselect or not. Its still going into the table the same number of times, so theres probably not much in it.

Cheers,
Juo

Looking at your query I fail to see why do you need two aliases, it looks to me that one alias will do:
Party party,
Contactpointmap cpm,
Contactpoint cp,
Address a
where
party.urn=cpm.party.urn and cpm.contactpoint.contactpointurn=cp.contactpointurn and
cp.electronicaddress like '%@%' and
cp.address.addressid=a.addressid and
a.city = 'Dublin'

Ant by the way: H does not think in terms of tables, and it helps not to think that HQL has something to do with tables. HQL is constructed in terms of objects and their properties.

Hi Konstantin,

The reason is because joining directly as you suggested would only work if my address and email records pointed to the same contactpoint record. But they have a separate contactpoint record each.

A direct join would only pick up individual partys that point to contactpoint records that obey all of the criteria, not partys that have a contactpoint record that obeys the email criterion and another contactpoint record that obeys the address criterion, etc.

So the searches upon each criterion must be executed separately somehow. At least thats the only way I can see to do it.

Thanks again,
Juo

Then join pair of criteria with OR, still do not need two aliases

where (...........)
OR
(...........)