Why are LAZY relationships being EAGERly loaded?

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

I'm not sure if this is a potential bug, a known-issue, or I'm just finally slipping into madness - maybe it's all-of-the-above?

I'm getting some eagerly-loaded queries being fired off, even after calling left-join-fetch on a lazily-loaded relationship!

For example:

Code:

@Entity
@Table(name="pub.\"order\"")
public class CustomerOrder implements Serializable
{
  @Id
  @Column(name="`Order-no`")
  private Integer orderno;

  @OneToOne(mappedBy="customerOrder", fetch=FetchType.LAZY)
  private OrderTrafficDetail orderTrafficDetail;
............................
}

@Entity
@Table(name="pub.\"ord-traf-d\"")
public class OrderTrafficDetail implements Serializable
{
   @Id
  @Column(name="`load-no`", nullable=false)
  private String loadNo;

  @OneToOne(fetch=FetchType.LAZY)
  @JoinColumn(name="`order-no`", insertable=false, updatable=false)
  private CustomerOrder customerOrder;
............................
}

....there are other entities involved here...but this is where it's happening...I use this query and get these results, since there are 7 records:

Code:

15:28:08,682 INFO  [STDOUT] Hibernate: 
    /* select
        distinct o 
    from
        CustomerOrder o 
    left join
        fetch o.customer 
    left join
        fetch o.orderTrafficDetail 
    where
        o.orderTrafficDetail.loadNo = :loadNo 
        and o.orderdate > :orderdate */ select
            distinct customeror0_."Order-no" as Order1_285_0_,
            customer1_."Cust-no" as Cust1_284_1_,
            ordertraff2_."load-no" as load1_292_2_,
            customeror0_."Ar-entity" as Ar2_285_0_,
            customeror0_."In-entity" as In3_285_0_,
            customeror0_."Entity-code" as Entity4_285_0_,
            customeror0_."Cust-no" as Cust5_285_0_,
            customeror0_."Order-date" as Order6_285_0_,
            customeror0_."Ship-name" as Ship7_285_0_,
            customeror0_."Ship-city" as Ship8_285_0_,
            customeror0_."Ship-st" as Ship9_285_0_,
            customeror0_."Ship-country" as Ship10_285_0_,
            customeror0_."Ship-zip" as Ship11_285_0_,
            customeror0_."Invoice-no" as Invoice12_285_0_,
            customeror0_."Invoice-date" as Invoice13_285_0_,
            customeror0_."Qty-orig-ord" as Qty14_285_0_,
            customeror0_."Qty-shipped" as Qty15_285_0_,
            customeror0_."Qty-open-ord" as Qty16_285_0_,
            customeror0_."Ship-address" as Ship17_285_0_,
            customeror0_."ship-date" as ship18_285_0_,
            customer1_."Name" as Name2_284_1_,
            customer1_."City" as City3_284_1_,
            customer1_."St" as St4_284_1_,
            customer1_."Zip-code" as Zip5_284_1_,
            customer1_."Country" as Country6_284_1_,
            customer1_."Contact" as Contact7_284_1_,
            customer1_."Country-code" as Country8_284_1_,
            customer1_."Address" as Address9_284_1_,
            ordertraff2_."order-no" as order2_292_2_,
            ordertraff2_."stop-no" as stop3_292_2_,
            ordertraff2_."shipment-type" as shipment4_292_2_,
            ordertraff2_."delvy-date" as delvy5_292_2_,
            ordertraff2_."delvy-time" as delvy6_292_2_,
            ordertraff2_."load-at-whs" as load7_292_2_ 
        from
            pub."order" customeror0_ 
        left outer join
            pub."customer" customer1_ 
                on customeror0_."Cust-no"=customer1_."Cust-no" 
        left outer join
            pub."ord-traf-d" ordertraff2_ 
                on customeror0_."Order-no"=ordertraff2_."order-no" 
        where
            ordertraff2_."load-no"=? 
            and customeror0_."Order-date">?
15:28:10,685 INFO  [STDOUT] Hibernate: 
    /* load com.abfoods.model.OrderTrafficDetail */ select
        ordertraff0_."load-no" as load1_292_0_,
        ordertraff0_."order-no" as order2_292_0_,
        ordertraff0_."stop-no" as stop3_292_0_,
        ordertraff0_."shipment-type" as shipment4_292_0_,
        ordertraff0_."delvy-date" as delvy5_292_0_,
        ordertraff0_."delvy-time" as delvy6_292_0_,
        ordertraff0_."load-at-whs" as load7_292_0_ 
    from
        pub."ord-traf-d" ordertraff0_ 
    where
        ordertraff0_."order-no"=?
15:28:11,125 INFO  [STDOUT] Hibernate: 
    /* load com.abfoods.model.OrderTrafficDetail */ select
        ordertraff0_."load-no" as load1_292_0_,
        ordertraff0_."order-no" as order2_292_0_,
        ordertraff0_."stop-no" as stop3_292_0_,
        ordertraff0_."shipment-type" as shipment4_292_0_,
        ordertraff0_."delvy-date" as delvy5_292_0_,
        ordertraff0_."delvy-time" as delvy6_292_0_,
        ordertraff0_."load-at-whs" as load7_292_0_ 
    from
        pub."ord-traf-d" ordertraff0_ 
    where
        ordertraff0_."order-no"=?
15:28:11,556 INFO  [STDOUT] Hibernate: 
    /* load com.abfoods.model.OrderTrafficDetail */ select
        ordertraff0_."load-no" as load1_292_0_,
        ordertraff0_."order-no" as order2_292_0_,
        ordertraff0_."stop-no" as stop3_292_0_,
        ordertraff0_."shipment-type" as shipment4_292_0_,
        ordertraff0_."delvy-date" as delvy5_292_0_,
        ordertraff0_."delvy-time" as delvy6_292_0_,
        ordertraff0_."load-at-whs" as load7_292_0_ 
    from
        pub."ord-traf-d" ordertraff0_ 
    where
        ordertraff0_."order-no"=?
15:28:11,826 INFO  [STDOUT] Hibernate: 
    /* load com.abfoods.model.OrderTrafficDetail */ select
        ordertraff0_."load-no" as load1_292_0_,
        ordertraff0_."order-no" as order2_292_0_,
        ordertraff0_."stop-no" as stop3_292_0_,
        ordertraff0_."shipment-type" as shipment4_292_0_,
        ordertraff0_."delvy-date" as delvy5_292_0_,
        ordertraff0_."delvy-time" as delvy6_292_0_,
        ordertraff0_."load-at-whs" as load7_292_0_ 
    from
        pub."ord-traf-d" ordertraff0_ 
    where
        ordertraff0_."order-no"=?
15:28:12,067 INFO  [STDOUT] Hibernate: 
    /* load com.abfoods.model.OrderTrafficDetail */ select
        ordertraff0_."load-no" as load1_292_0_,
        ordertraff0_."order-no" as order2_292_0_,
        ordertraff0_."stop-no" as stop3_292_0_,
        ordertraff0_."shipment-type" as shipment4_292_0_,
        ordertraff0_."delvy-date" as delvy5_292_0_,
        ordertraff0_."delvy-time" as delvy6_292_0_,
        ordertraff0_."load-at-whs" as load7_292_0_ 
    from
        pub."ord-traf-d" ordertraff0_ 
    where
        ordertraff0_."order-no"=?
15:28:12,307 INFO  [STDOUT] Hibernate: 
    /* load com.abfoods.model.OrderTrafficDetail */ select
        ordertraff0_."load-no" as load1_292_0_,
        ordertraff0_."order-no" as order2_292_0_,
        ordertraff0_."stop-no" as stop3_292_0_,
        ordertraff0_."shipment-type" as shipment4_292_0_,
        ordertraff0_."delvy-date" as delvy5_292_0_,
        ordertraff0_."delvy-time" as delvy6_292_0_,
        ordertraff0_."load-at-whs" as load7_292_0_ 
    from
        pub."ord-traf-d" ordertraff0_ 
    where
        ordertraff0_."order-no"=?
15:28:12,548 INFO  [STDOUT] Hibernate: 
    /* load com.abfoods.model.OrderTrafficDetail */ select
        ordertraff0_."load-no" as load1_292_0_,
        ordertraff0_."order-no" as order2_292_0_,
        ordertraff0_."stop-no" as stop3_292_0_,
        ordertraff0_."shipment-type" as shipment4_292_0_,
        ordertraff0_."delvy-date" as delvy5_292_0_,
        ordertraff0_."delvy-time" as delvy6_292_0_,
        ordertraff0_."load-at-whs" as load7_292_0_ 
    from
        pub."ord-traf-d" ordertraff0_ 
    where
        ordertraff0_."order-no"=?

I vaguely remember a discussion on here recommending using @ManyToOne for something like this but I can't find the thread now...either way - this is standard JPA...it should work.

Thanks!

EDIT

I changed this so that one side of the relationship is @ManyToOne (not what I want but simply to test...) - it doesn't help. Even though it's uni-direction and @ManyToOne, extra queries are being generated and executed as if it were EAGER:

Code:

@Entity
@Table(name="pub.\"order\"")
public class CustomerOrder implements Serializable
{
  @Id
  @Column(name="`Order-no`")
  private Integer orderno;

  @ManyToOne(fetch=FetchType.LAZY) 
  @JoinColumn(name="`Order-no`", referencedColumnName="`order-no`", insertable=false, updatable=false)
  private OrderTrafficDetail orderTrafficDetail;
...................................
}

...customerOrder field was removed from OrderTrafficDetail, making this uni-directional.

...queried like so (exactly as entity configuration in original post):

Code:

    String query = "select distinct o from CustomerOrder o " +
        "left join fetch o.customer " +
        "left join fetch o.orderTrafficDetail " +
        "where o.orderTrafficDetail.loadNo = :loadNo " +
        "and o.orderdate > :orderdate"; 

...yields the join, plus extra queries for the same data after the fact:

Code:

16:50:01,796 INFO  [STDOUT] Hibernate: 
    /* select
        distinct o 
    from
        CustomerOrder o 
    left join
        fetch o.customer 
    left join
        fetch o.orderTrafficDetail 
    where
        o.orderTrafficDetail.loadNo = :loadNo 
        and o.orderdate > :orderdate */ select
            distinct customeror0_."Order-no" as Order1_363_0_,
            customer1_."Cust-no" as Cust1_362_1_,
            ordertraff2_."load-no" as load1_370_2_,
            customeror0_."Ar-entity" as Ar2_363_0_,
            customeror0_."In-entity" as In3_363_0_,
            customeror0_."Entity-code" as Entity4_363_0_,
            customeror0_."Cust-no" as Cust5_363_0_,
            customeror0_."Order-date" as Order6_363_0_,
            customeror0_."Ship-name" as Ship7_363_0_,
            customeror0_."Ship-city" as Ship8_363_0_,
            customeror0_."Ship-st" as Ship9_363_0_,
            customeror0_."Ship-country" as Ship10_363_0_,
            customeror0_."Ship-zip" as Ship11_363_0_,
            customeror0_."Invoice-no" as Invoice12_363_0_,
            customeror0_."Invoice-date" as Invoice13_363_0_,
            customeror0_."Qty-orig-ord" as Qty14_363_0_,
            customeror0_."Qty-shipped" as Qty15_363_0_,
            customeror0_."Qty-open-ord" as Qty16_363_0_,
            customeror0_."Ship-address" as Ship17_363_0_,
            customeror0_."ship-date" as ship18_363_0_,
            customer1_."Name" as Name2_362_1_,
            customer1_."City" as City3_362_1_,
            customer1_."St" as St4_362_1_,
            customer1_."Zip-code" as Zip5_362_1_,
            customer1_."Country" as Country6_362_1_,
            customer1_."Contact" as Contact7_362_1_,
            customer1_."Country-code" as Country8_362_1_,
            customer1_."Address" as Address9_362_1_,
            ordertraff2_."order-no" as order2_370_2_,
            ordertraff2_."stop-no" as stop3_370_2_,
            ordertraff2_."shipment-type" as shipment4_370_2_,
            ordertraff2_."delvy-date" as delvy5_370_2_,
            ordertraff2_."delvy-time" as delvy6_370_2_,
            ordertraff2_."load-at-whs" as load7_370_2_ 
        from
            pub."order" customeror0_ 
        left outer join
            pub."customer" customer1_ 
                on customeror0_."Cust-no"=customer1_."Cust-no" 
        left outer join
            pub."ord-traf-d" ordertraff2_ 
                on customeror0_."Order-no"=ordertraff2_."order-no" 
        where
            ordertraff2_."load-no"=? 
            and customeror0_."Order-date">?
16:50:03,629 INFO  [STDOUT] Hibernate: 
    /* load com.abfoods.model.OrderTrafficDetail */ select
        ordertraff0_."load-no" as load1_370_0_,
        ordertraff0_."order-no" as order2_370_0_,
        ordertraff0_."stop-no" as stop3_370_0_,
        ordertraff0_."shipment-type" as shipment4_370_0_,
        ordertraff0_."delvy-date" as delvy5_370_0_,
        ordertraff0_."delvy-time" as delvy6_370_0_,
        ordertraff0_."load-at-whs" as load7_370_0_ 
    from
        pub."ord-traf-d" ordertraff0_ 
    where
        ordertraff0_."order-no"=?
16:50:03,869 INFO  [STDOUT] Hibernate: 
    /* load com.abfoods.model.OrderTrafficDetail */ select
        ordertraff0_."load-no" as load1_370_0_,
        ordertraff0_."order-no" as order2_370_0_,
        ordertraff0_."stop-no" as stop3_370_0_,
        ordertraff0_."shipment-type" as shipment4_370_0_,
        ordertraff0_."delvy-date" as delvy5_370_0_,
        ordertraff0_."delvy-time" as delvy6_370_0_,
        ordertraff0_."load-at-whs" as load7_370_0_ 
    from
        pub."ord-traf-d" ordertraff0_ 
    where
        ordertraff0_."order-no"=?

tsar bomba · **Joined:** Mon Nov 14, 2005 7:33 pm **Posts:** 73

Sorry to bump but I can't make any sense of what's going on w/ this.

I looked through JIRA and I see several bug reports that were closed w/ comments directing the users to ask about it on the forums.

Is this a bug? Should I file another report? How can I stop the eager loading where I've specified lazy?

Obviously this can be bad....I have a large query that takes 4 sec. to run....then another 25 sec. after it fires a huge list of queries due to the unintentional eager-loading.

Please advise. Thanks!

meinolf · **Joined:** Thu Aug 24, 2006 11:10 am **Posts:** 16

Hi there,
I have exactly the same problem using EntityManager and Hibernate 3.3.1 Core. OneToOne relations are fetched eager but the are Annotated as Lazy.

Is there a solution/workarround for this?

Thanks
M

meinolf · **Joined:** Thu Aug 24, 2006 11:10 am **Posts:** 16

Hi there,
I have exactly the same problem using EntityManager and Hibernate 3.3.1 Core. OneToOne relations are fetched eager but they are Annotated as Lazy.

Is there a solution/workarround for this?

Thanks
M

emmanuel · **Posted:** Wed Feb 07, 2007 9:29 pm

read the wiki, this is explained