Hi all,
I have so strange issues with a query I am trying to execute. First the interesting parts of tzhe classes the query uses:
Code:
@Entity(name = "EnvironmentOrder")
@Table(name = "EnvironmentOrder")
public class Order extends AbstractOrder {
[nothing interesting here]
}
@Entity
@Inheritance(strategy=InheritanceType.JOINED)
public abstract class AbstractOrder implements TransactionalObject {
@ManyToOne
@JoinColumn(name="environmentId")
protected Environment environment;
protected boolean imported = false;
protected OrderStatus status = OrderStatus.CREATED; //Enumeration
}
@Entity
public class Environment implements TransactionalObject {
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Order activeOrder;
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Order incomingOrder;
@ManyToOne(targetEntity = Zone.class)
private ZoneValue customer;
}
I think this would be the relevant parts of the classes.
The hql-query goes here:
Code:
select count(o) from EnvironmentOrder o
where o.status in (:allowedStatusList)
and o.environment.customer.id = :customerid
and (
(o.environment.activeOrder is null and o.environment.incomingOrder.imported = 0 )
or o.environment.activeOrder.id = o.id
)
First: Are there any obvious errors in this query?
this hql-query results in the following SQL:
Code:
select count(order0_.id) as col_0_0_ from EnvironmentOrder order0_
inner join AbstractOrder order0_1_ on order0_.id=order0_1_.id,
Environment environmen1_,
EnvironmentOrder order4_
inner join AbstractOrder order4_1_ on order4_.id=order4_1_.id
where environmen1_.incomingOrder_id=order4_.id
and order0_1_.environmentId=environmen1_.id
and (order0_1_.status in (7 , 9 , 10))
and environmen1_.customer_zone_id=1
and ((environmen1_.activeOrder_id is null)
and order4_1_.imported=0 or environmen1_.activeOrder_id=order0_.id)
Which in my case gives the result 0.
In my opinion this generated SQL is wrong and should read:
Code:
select count(order0_.id) as col_0_0_ from EnvironmentOrder order0_
inner join AbstractOrder order0_1_ on order0_.id=order0_1_.id,
Environment environmen1_,
EnvironmentOrder order4_
inner join AbstractOrder order4_1_ on order4_.id=order4_1_.id
where ( environmen1_.incomingOrder_id=order4_.id OR environmen1_.activeOrder_id=order4_.id )
and order0_1_.environmentId=environmen1_.id
and (order0_1_.status in (7 , 9 , 10))
and environmen1_.customer_zone_id=1
and ((environmen1_.activeOrder_id is null)
and order4_1_.imported=0 or environmen1_.activeOrder_id=order0_.id)
The last query gives the correct count in my case.
For comparison, I tried the following hql-query which should give less or equal results:
Code:
select count(o) from EnvironmentOrder o
where o.status in (:allowedStatusList)
and o.environment.customer.id = :customerid
and o.environment.activeOrder.id = o.id
which evaluated to:
Code:
select count(order0_.id) as col_0_0_ from EnvironmentOrder order0_
inner join AbstractOrder order0_1_ on order0_.id=order0_1_.id,
Environment environmen1_
where order0_1_.environmentId=environmen1_.id
and (order0_1_.status in (7 , 9 , 10))
and environmen1_.customer_zone_id=1
and environmen1_.activeOrder_id=order0_.id
Could anybody explain this behaviour to me? Is there anything wrong with my query or did i discover a bug in hibernate?
This error is reproducible with hibernate 3.1.1 and 3.1.3.
Are there any workarounds for this problem, or would I have to go for two seperate queries for a combined result? I should not, that upgrading to hibernate 3.2.x is no option currently, since it is used in a large project which would require many changes to work with 3.2.x
Would be great if I would receive any help on this.
Thanks in advance,
Philipp
Hibernate version:
3.1.3
Mapping documents:
annotations
Name and version of the database you are using:
Mysql 5
Login
FAQ
Search