I performed an HQL query and I think it may reveal a bug - feedback is welcome. I am in an environment which uses Oracle9Dialect and an Oracle 10.2 database.

The tables are (sanitized):

ORDERS
====
ID (primary key)

PRODUCTS
=====
ID (primary key)
ORDERID (foreign key)

The classes contain:

Order
=================
+ID
+products (one-to-many)

Product
=================
+ID
+order (many-to-one)

The HQL query was:

from Product p WHERE p in(select theOrder.products from Order theOrder where theOrder.Id = 123) AND rownum = 1

This should return one result. (The internal WHERE clause has a purpose in the actual application, in this example it makes rownum pointless).


The SQL produced was:

select product0_.Id as Id7_ from PRODUCTS product0_
WHERE
(product0_.Id in
(select . from ORDERS order1_,
PRODUCTS product2_
where order1_.Id=product2_.ORDERID and order1_.Id=-164001)) and rownum=1


The problem is "SELECT . ", and the query fails. If it had produced "SELECT product2_.Id" it would have worked.

Cheers!