Hi Folks,

I'm kind of stumped on why I'm getting a cartesian product in my query.

The query is:
from Order o where trunc(o.activity.activityDate) = trunc(sysdate) order by id

In effect, I'm trying to get:
select o.* from ORDER o where o.ORDER_ID in ( select a.ORDER_ID from ACTIVITY a where trunc(a.ACTIVITY_DATE) = trunc(sysdate))

* read as, get all orders that had any activity for today *

Order has a bi-directional @ManyToOne relationship to Activity.

The relationships are defined:
Order:

@OneToMany(fetch = FetchType.LAZY, mappedBy = "order")
@OrderBy("createdDate")
public List<Activity> getActivity() { return activityRecords; }

Activity:

@ManyToOne
@JoinColumn(name="order_id")
public Order getOrder() { return order; }

My dataset has 2 Orders with 1 activity each, and 4 Orders with 2 Activities each.

If I copy/past the SQL generated by the query into SQLPlus (oracle), I get back 10 records (the 4 orders show up twice) when I should get just 6 records.

I realize I can easily get around this by manually de-duping the list, or changing the query to "select distinct o from Order o where..."

But I'm just wondering if it's a bug in the query engine? Or is it expected behavior.


Actual SQL generated is (stripped column aliases for readability):

select ...SNIP ORDER table column alias stuff...
 from Order order0_, activity activity1_ where
 order0_.Order_id=activity1_.order_id and
 trunc(activity1_.ACTIVITY_DATE)=trunc(sysdate) order by
 order0_.Order_id

Ideas?