Bug 143 : Uses SQL limiting via limit offset (or rownum etc) when it should not
Reported Version 
Logged By 
Fixed Version 
Assigned To 
Ebean - core
Duplicate Of 
No attachments

When query joins to a many property and has a firstRow or maxRows setting then:

- the SQL to limit the result should not be added
- the join to the Many property should be removed from the query

In v2.0. of Ebean the check for this is broken (incorrect boolean expression in CQueryBuilder) and you end up with the join to the many property AND the limit clause.

This means the returned result can often not have the correct number of root level objects.

Rob 19 Aug 12:00
for example
// joins to details which is a OneToMany
// ... and also has a maxRows limit of 10
// this query will very often (mostly) not return 
// the correct object graph 

Query<Order> query = Ebean.find(Order.class)

Rob 19 Aug 12:10
The Fix

There are 2 possible fixes
1) Remove the Many joins if there is a firstRow or maxRows set
2) Don't add the limit offset clause etc when a join to a Many exists

I am going with option 1 which is to remove the join to the Many (and hence let the many lazy load). The reason being is that the maxRows limit is often a big performance improvement.

Aka having the lazy load the many's is IMO not as bad as losing the performance effect of the limit offset clause (or rownum for Oracle or row_number function for some other DBs).

Rob 19 Aug 12:12
Note: where clause expressions involving a Many

Note that if a many property is involved in an expression in the where clause then the join to the many is required.

In this case Ebean will keep the join to the many but not select/fetch the many properties and use a SQL DISTINCT to ensure we get distinct rows for the root/top level. (This is the same as the approach to handle this for the row count query).

Rob 19 Aug 21:48
Fixed in HEAD

This is fixed in head by removing the Many joins if there is a firstRow or maxRows set


Upload a file