4 Replies Latest reply on Feb 13, 2006 8:09 PM by Alexander Hartner

    Unexpected cartesian result in EJB3 query

    David Budworth Newbie

      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:

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

      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