Hello,
Here is an interesting puzzle. I've got an aggregate root entity with two OneToMany relationships. One of the relationship entities can optionally have a OneToOne relationship with the other relationship entity. It looks something like this:
Code:
@Entity class Root{
@Id Integer id;
@OneToMany Collection<Foo> foos;
@OneToMany Collection<Bar> bars;
}
@Entity class Foo{
@Id Integer id;
@ManyToOne Root root;
@OneToOne Bar bar;
@Basic String maybeNull;
}
@Entity class Bar{
@Id Integer id;
@ManyToOne Root root;
}
And a JPQL query that looks something like:
Code:
SELECT bar.id, COUNT( DISTINCT foo), COUNT( DISTINCT foo.maybeNull) FROM Bar bar, IN( bar.root.foos) foo WHERE bar.root.id=:rootId AND (foo IS NULL OR foo.bar=bar) GROUP BY bar
This generates some SQL. The SQL returns correct results for a given set of data. However, the JPQL returns incorrect results for the counts. It's almost like Hibernate is adding to the count values it receives from the database. The simple test case I've got is supposed to return five rows with a count of one each. Executing the generated SQL directly against the database returns this:
Code:
+----------+----------+----------+
| col_0_0_ | col_2_0_ | col_3_0_ |
+----------+----------+----------+
| 21 | 1 | 1 |
| 22 | 1 | 1 |
| 23 | 1 | 1 |
| 24 | 1 | 0 |
| 25 | 1 | 1 |
+----------+----------+----------+
This is correct. However the JPQL returns a value of 2 for each value of one returned by the SQL, and a value of 1 for the zero value returned by SQL.
Please advise.
--
Tim
Login
FAQ
Search