I seem to have discovered a very bad problem with ResourceTypeCriteria and/or the Criteria Query engine. I may need Joe to help with this one.
I discovered this while stepping through ResourceTypeRepository, specifically:
ResourceTypeRepository.getResourceTypes(Integer[], EnumSet<MetadataType>, TypesLoadedCallback)
where the Integer[] array has a single ID (my Linux Platform type ID) and the EnumSet contains MetadataType.operations, measurements, events, and resourceConfigurationDefinition.
This boils down to a call to:
resourceTypeService.findResourceTypesByCriteria(criteria)
where my criteria has filterId null but filterIds is a one-element List (I suspect the problem might be caused by using the ResourceTypeCriteria.addFilterIds (plural) rather than the singlular addFilterId form).
When I get back the results, the PageList has *48* resource types in the list - ALL are the Linux Platform type. I should only have received *1* back (remember, I only asked for 1 resource type, and the criteria's filterIds had only a single item in it). There must be something wrong with the dynamically built query that caused it to return 48 rows, as opposed to just 1.
Note that if I do NOT ask for any MetadataType (i.e. the EnumSet is empty, thus I just ask for the base ResourceType without any relationship collections), things work fine and the fetch by criteria returns a single item in the PageList as expected.
Debugging in the CriteriaQueryRunner, I see this as the query:
SELECT resourcetype FROM ResourceType resourcetype LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition LEFT JOIN FETCH resourcetype.metricDefinitions LEFT JOIN FETCH resourcetype.eventDefinitions LEFT JOIN FETCH resourcetype.operationDefinitions WHERE ( resourcetype.id in ( :ids ) )
and this is the countQuery:
SELECT COUNT(resourcetype) FROM ResourceType resourcetype WHERE ( resourcetype.id in ( :ids ) )
the named parameter list looked correct, the "ids" appeared to only have a single Id in it. But stepping in:
org.rhq.enterprise.server.util.CriteriaQueryRunner.execute()
and I see the query results come back with 48 rows but the countQuery returns back with 1 (and 1 is what should be correct).
So, the countQuery returns the correct answer - 1 - but the query itself returns 48 items!
Very odd. For some reason, hibernate returned us 48 times the data than we expected. Unsure what we did wrong in building the query.
I don't get it. I run this code, and each System.out.println shows me that the results have 48 rows - but each query should only produce 1 row.
Note: if I go to admin/test/hibernate.jsp, and try this JPQL string, it actually only produces 1 row (the correct results!). I don't know what its doing differently, but here's the code that I tested with - there are actually 4 different ways I try it (two using JPA API, two using Hibernate API - for each I try using a collection of 1 ID integer, and I try using a single parameter value of Integer type).
String sqlString = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
ArrayList<Integer> idList = new ArrayList<Integer>(); idList.add(new Integer(10014));
Query q; List<?> results;
// JPA - parameter is an ArrayList of 1 ID q = entityManager.createQuery(sqlString); q.setParameter("ids", idList); results = q.getResultList(); System.out.println("1~~~~~~~~~size=" + results.size());
// JPA - parameter is a single Integer value q = entityManager.createQuery(sqlString); q.setParameter("ids", new Integer(10014)); results = q.getResultList(); System.out.println("2~~~~~~~~~size=" + results.size());
Session session; org.hibernate.Query hq; session = (Session) entityManager.getDelegate();
// Hibernate - parameter is an ArrayList of 1 ID hq = session.createQuery(sqlString); hq.setParameterList("ids", idList); results = hq.list(); System.out.println("3~~~~~~~~~size=" + results.size());
// Hibernate - parameter is a single Integer value hq = session.createQuery(sqlString); hq.setParameter("ids", new Integer(10014)); results = hq.list(); System.out.println("4~~~~~~~~~size=" + results.size());
Yes, I've seen Hibernate do this from time to time when left join fetching.
Do a little investigation for me - how many rows do you get back if you only fetch eventDefinitions versus if you only fetch operationDefinitions or resourceConfigurationDefinitions? Also, what is the generated SQL for the query (if there is a join to a collection [xxxToMany mapping] anywhere, that would account for the duplicate rows of the projected data.)
On 10/23/2010 03:08 PM, John Mazzitelli wrote:
I don't get it. I run this code, and each System.out.println shows me that the results have 48 rows - but each query should only produce 1 row.
Note: if I go to admin/test/hibernate.jsp, and try this JPQL string, it actually only produces 1 row (the correct results!). I don't know what its doing differently, but here's the code that I tested with - there are actually 4 different ways I try it (two using JPA API, two using Hibernate API - for each I try using a collection of 1 ID integer, and I try using a single parameter value of Integer type).
String sqlString = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
ArrayList<Integer> idList = new ArrayList<Integer>(); idList.add(new Integer(10014));
Query q; List<?> results;
// JPA - parameter is an ArrayList of 1 ID q = entityManager.createQuery(sqlString); q.setParameter("ids", idList); results = q.getResultList(); System.out.println("1~~~~~~~~~size=" + results.size());
// JPA - parameter is a single Integer value q = entityManager.createQuery(sqlString); q.setParameter("ids", new Integer(10014)); results = q.getResultList(); System.out.println("2~~~~~~~~~size=" + results.size());
Session session; org.hibernate.Query hq; session = (Session) entityManager.getDelegate();
// Hibernate - parameter is an ArrayList of 1 ID hq = session.createQuery(sqlString); hq.setParameterList("ids", idList); results = hq.list(); System.out.println("3~~~~~~~~~size=" + results.size());
// Hibernate - parameter is a single Integer value hq = session.createQuery(sqlString); hq.setParameter("ids", new Integer(10014)); results = hq.list(); System.out.println("4~~~~~~~~~size=" + results.size()); _______________________________________________ rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
Just did your quick tests - its interesting. The first query (the original with all the joins) returned 48 rows as I said. If I just do a single LEFT JOIN FETCH it depends what I'm fetching that determines the rows it returns:
If I join resourceConfigurationDefinition, I get 1 row (good) If I join metricDefinitions, I get 16 rows (bad) If I join evenDefinition, I get 1 row (good) If I join operationDefinitions, I get 3 rows (bad)
Here's the 5 queries I tested with:
sqlStrings[0] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[1] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[2] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[3] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[4] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
On 10/25/2010 03:09 PM, Joseph Marques wrote:
Yes, I've seen Hibernate do this from time to time when left join fetching.
Do a little investigation for me - how many rows do you get back if you only fetch eventDefinitions versus if you only fetch operationDefinitions or resourceConfigurationDefinitions? Also, what is the generated SQL for the query (if there is a join to a collection [xxxToMany mapping] anywhere, that would account for the duplicate rows of the projected data.)
On 10/23/2010 03:08 PM, John Mazzitelli wrote:
I don't get it. I run this code, and each System.out.println shows me that the results have 48 rows - but each query should only produce 1 row.
Note: if I go to admin/test/hibernate.jsp, and try this JPQL string, it actually only produces 1 row (the correct results!). I don't know what its doing differently, but here's the code that I tested with - there are actually 4 different ways I try it (two using JPA API, two using Hibernate API - for each I try using a collection of 1 ID integer, and I try using a single parameter value of Integer type).
String sqlString = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
ArrayList<Integer> idList = new ArrayList<Integer>(); idList.add(new Integer(10014));
Query q; List<?> results;
// JPA - parameter is an ArrayList of 1 ID q = entityManager.createQuery(sqlString); q.setParameter("ids", idList); results = q.getResultList(); System.out.println("1~~~~~~~~~size=" + results.size());
// JPA - parameter is a single Integer value q = entityManager.createQuery(sqlString); q.setParameter("ids", new Integer(10014)); results = q.getResultList(); System.out.println("2~~~~~~~~~size=" + results.size());
Session session; org.hibernate.Query hq; session = (Session) entityManager.getDelegate();
// Hibernate - parameter is an ArrayList of 1 ID hq = session.createQuery(sqlString); hq.setParameterList("ids", idList); results = hq.list(); System.out.println("3~~~~~~~~~size=" + results.size());
// Hibernate - parameter is a single Integer value hq = session.createQuery(sqlString); hq.setParameter("ids", new Integer(10014)); results = hq.list(); System.out.println("4~~~~~~~~~size=" + results.size()); _______________________________________________ rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
On 10/26/2010 10:07 AM, John Mazzitelli wrote:
Just did your quick tests - its interesting. The first query (the original with all the joins) returned 48 rows as I said.
48 is the cross of the related rows your fetching: 1 x 16 x 1 x 3
If I just do a single LEFT JOIN FETCH it depends what I'm fetching that determines the rows it returns:
If I join resourceConfigurationDefinition, I get 1 row (good)
Yup, one resource configuration definition for every type in the system.
If I join metricDefinitions, I get 16 rows (bad)
There are 16 metrics for the Linux platform type.
If I join evenDefinition, I get 1 row (good)
There is, by convention, either zero or one event definition for a type.
If I join operationDefinitions, I get 3 rows (bad)
The Linux platform type has 3 operations: discovery, viewProcessList, clearYumMetadataCache
Here's the 5 queries I tested with:
sqlStrings[0] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[1] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[2] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[3] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[4] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
On 10/25/2010 03:09 PM, Joseph Marques wrote:
Yes, I've seen Hibernate do this from time to time when left join
fetching.
Do a little investigation for me - how many rows do you get back if you only fetch eventDefinitions versus if you only fetch operationDefinitions or resourceConfigurationDefinitions? Also, what is the generated SQL for the query (if there is a join to a collection [xxxToMany mapping] anywhere, that would account for the duplicate rows of the projected data.)
On 10/23/2010 03:08 PM, John Mazzitelli wrote:
I don't get it. I run this code, and each System.out.println shows me that the results have 48 rows - but each query should only produce 1 row.
Note: if I go to admin/test/hibernate.jsp, and try this JPQL string, it actually only produces 1 row (the correct results!). I don't know what its doing differently, but here's the code that I tested with - there are actually 4 different ways I try it (two using JPA API, two using Hibernate API - for each I try using a collection of 1 ID integer, and I try using a single parameter value of Integer type).
String sqlString = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
ArrayList<Integer> idList = new ArrayList<Integer>(); idList.add(new Integer(10014));
Query q; List<?> results;
// JPA - parameter is an ArrayList of 1 ID q = entityManager.createQuery(sqlString); q.setParameter("ids", idList); results = q.getResultList(); System.out.println("1~~~~~~~~~size=" + results.size());
// JPA - parameter is a single Integer value q = entityManager.createQuery(sqlString); q.setParameter("ids", new Integer(10014)); results = q.getResultList(); System.out.println("2~~~~~~~~~size=" + results.size());
Session session; org.hibernate.Query hq; session = (Session) entityManager.getDelegate();
// Hibernate - parameter is an ArrayList of 1 ID hq = session.createQuery(sqlString); hq.setParameterList("ids", idList); results = hq.list(); System.out.println("3~~~~~~~~~size=" + results.size());
// Hibernate - parameter is a single Integer value hq = session.createQuery(sqlString); hq.setParameter("ids", new Integer(10014)); results = hq.list(); System.out.println("4~~~~~~~~~size=" + results.size()); _______________________________________________ rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
I just noticed that if I add
q.setMaxResults(100)
on my JPA queries, I get 1 row back always (which is good). Using Hibernate API, that still doesn't work, but I think this talks about that issue:
https://forums.hibernate.org/viewtopic.php?f=1&t=958745&start=0
On 10/26/2010 11:16 AM, Joseph Marques wrote:
On 10/26/2010 10:07 AM, John Mazzitelli wrote:
Just did your quick tests - its interesting. The first query (the original with all the joins) returned 48 rows as I said.
48 is the cross of the related rows your fetching: 1 x 16 x 1 x 3
If I just do a single LEFT JOIN FETCH it depends what I'm fetching that determines the rows it returns:
If I join resourceConfigurationDefinition, I get 1 row (good)
Yup, one resource configuration definition for every type in the system.
If I join metricDefinitions, I get 16 rows (bad)
There are 16 metrics for the Linux platform type.
If I join evenDefinition, I get 1 row (good)
There is, by convention, either zero or one event definition for a type.
If I join operationDefinitions, I get 3 rows (bad)
The Linux platform type has 3 operations: discovery, viewProcessList, clearYumMetadataCache
Here's the 5 queries I tested with:
sqlStrings[0] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[1] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[2] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[3] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[4] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
On 10/25/2010 03:09 PM, Joseph Marques wrote:
Yes, I've seen Hibernate do this from time to time when left join
fetching.
Do a little investigation for me - how many rows do you get back if you only fetch eventDefinitions versus if you only fetch operationDefinitions or resourceConfigurationDefinitions? Also, what is the generated SQL for the query (if there is a join to a collection [xxxToMany mapping] anywhere, that would account for the duplicate rows of the projected data.)
On 10/23/2010 03:08 PM, John Mazzitelli wrote:
I don't get it. I run this code, and each System.out.println shows me that the results have 48 rows - but each query should only produce 1 row.
Note: if I go to admin/test/hibernate.jsp, and try this JPQL string, it actually only produces 1 row (the correct results!). I don't know what its doing differently, but here's the code that I tested with - there are actually 4 different ways I try it (two using JPA API, two using Hibernate API - for each I try using a collection of 1 ID integer, and I try using a single parameter value of Integer type).
String sqlString = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
ArrayList<Integer> idList = new ArrayList<Integer>(); idList.add(new Integer(10014));
Query q; List<?> results;
// JPA - parameter is an ArrayList of 1 ID q = entityManager.createQuery(sqlString); q.setParameter("ids", idList); results = q.getResultList(); System.out.println("1~~~~~~~~~size=" + results.size());
// JPA - parameter is a single Integer value q = entityManager.createQuery(sqlString); q.setParameter("ids", new Integer(10014)); results = q.getResultList(); System.out.println("2~~~~~~~~~size=" + results.size());
Session session; org.hibernate.Query hq; session = (Session) entityManager.getDelegate();
// Hibernate - parameter is an ArrayList of 1 ID hq = session.createQuery(sqlString); hq.setParameterList("ids", idList); results = hq.list(); System.out.println("3~~~~~~~~~size=" + results.size());
// Hibernate - parameter is a single Integer value hq = session.createQuery(sqlString); hq.setParameter("ids", new Integer(10014)); results = hq.list(); System.out.println("4~~~~~~~~~size=" + results.size()); _______________________________________________ rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
OK, I think the root is this issue is that we're not generating the appropriate SQL. We know that if we're left fetch joining to objects whose entity relationship is xxxToOne, then the join is safe because we'll get the same number of rows with the join than without. However, for xxxToMany relationships, the idea was that we would:
1) get the List<Entity> back 2) iterate over the list and eager fetch data while the xaction was still open -- for (next : List<Entity>) { next.getSomeCollection().size() }
You can see this logic in the CriteriaQueryGenerator:
for (String fetchJoin : getFetchFields(criteria)) { if (isPersistentBag(fetchJoin)) { addPersistentBag(fetchJoin); } else { results.append("LEFT JOIN FETCH"); results.append(alias).append('.').append(fetchJoin); results.append(NL); } }
So, it appears that sticking to the strict definition of isPersistentBag is not sufficient here, because there are still other cases that can return duplicates that aren't persistent bags. I would say that changing the if-condition check to look for either the OneToMany or ManyToMany annotation on the fetched relationship would be a good catch-all, but I'm pretty sure there was a reason we used isPersistentBag in the first place. Looking at SCM history, it shows that jsanda was responsible for this - I'm going to ping him, to see what he can add to this thread.
On 10/26/2010 11:46 AM, John Mazzitelli wrote:
I just noticed that if I add
q.setMaxResults(100)
on my JPA queries, I get 1 row back always (which is good). Using Hibernate API, that still doesn't work, but I think this talks about that issue:
https://forums.hibernate.org/viewtopic.php?f=1&t=958745&start=0
On 10/26/2010 11:16 AM, Joseph Marques wrote:
On 10/26/2010 10:07 AM, John Mazzitelli wrote:
Just did your quick tests - its interesting. The first query (the original with all the joins) returned 48 rows as I said.
48 is the cross of the related rows your fetching: 1 x 16 x 1 x 3
If I just do a single LEFT JOIN FETCH it depends what I'm fetching that determines the rows it returns:
If I join resourceConfigurationDefinition, I get 1 row (good)
Yup, one resource configuration definition for every type in the system.
If I join metricDefinitions, I get 16 rows (bad)
There are 16 metrics for the Linux platform type.
If I join evenDefinition, I get 1 row (good)
There is, by convention, either zero or one event definition for a type.
If I join operationDefinitions, I get 3 rows (bad)
The Linux platform type has 3 operations: discovery, viewProcessList, clearYumMetadataCache
Here's the 5 queries I tested with:
sqlStrings[0] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[1] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[2] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[3] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[4] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
On 10/25/2010 03:09 PM, Joseph Marques wrote:
Yes, I've seen Hibernate do this from time to time when left join
fetching.
Do a little investigation for me - how many rows do you get back if you only fetch eventDefinitions versus if you only fetch operationDefinitions or resourceConfigurationDefinitions? Also, what is the generated SQL for the query (if there is a join to a collection [xxxToMany mapping] anywhere, that would account for the duplicate rows of the projected data.)
On 10/23/2010 03:08 PM, John Mazzitelli wrote:
I don't get it. I run this code, and each System.out.println shows me that the results have 48 rows - but each query should only produce 1 row.
Note: if I go to admin/test/hibernate.jsp, and try this JPQL string, it actually only produces 1 row (the correct results!). I don't know what its doing differently, but here's the code that I tested with - there are actually 4 different ways I try it (two using JPA API, two using Hibernate API - for each I try using a collection of 1 ID integer, and I try using a single parameter value of Integer type).
String sqlString = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
ArrayList<Integer> idList = new ArrayList<Integer>(); idList.add(new Integer(10014));
Query q; List<?> results;
// JPA - parameter is an ArrayList of 1 ID q = entityManager.createQuery(sqlString); q.setParameter("ids", idList); results = q.getResultList(); System.out.println("1~~~~~~~~~size=" + results.size());
// JPA - parameter is a single Integer value q = entityManager.createQuery(sqlString); q.setParameter("ids", new Integer(10014)); results = q.getResultList(); System.out.println("2~~~~~~~~~size=" + results.size());
Session session; org.hibernate.Query hq; session = (Session) entityManager.getDelegate();
// Hibernate - parameter is an ArrayList of 1 ID hq = session.createQuery(sqlString); hq.setParameterList("ids", idList); results = hq.list(); System.out.println("3~~~~~~~~~size=" + results.size());
// Hibernate - parameter is a single Integer value hq = session.createQuery(sqlString); hq.setParameter("ids", new Integer(10014)); results = hq.list(); System.out.println("4~~~~~~~~~size=" + results.size()); _______________________________________________ rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
The reason I added the isPersistentBag logic is because hibernate does not allow you to simultaneously fetch multiple bags. See http://opensource.atlassian.com/projects/hibernate/browse/HHH-1413. And if I recall from when I encountered this, it seemed to be the case that we have a number of collections that are stored as lists which results in the bag semantics where we should be using sets.
- John
On 10/26/10 12:05 PM, Joseph Marques wrote:
OK, I think the root is this issue is that we're not generating the appropriate SQL. We know that if we're left fetch joining to objects whose entity relationship is xxxToOne, then the join is safe because we'll get the same number of rows with the join than without. However, for xxxToMany relationships, the idea was that we would:
- get the List<Entity> back
- iterate over the list and eager fetch data while the xaction was
still open -- for (next : List<Entity>) { next.getSomeCollection().size() }
You can see this logic in the CriteriaQueryGenerator:
for (String fetchJoin : getFetchFields(criteria)) { if (isPersistentBag(fetchJoin)) { addPersistentBag(fetchJoin); } else { results.append("LEFT JOIN FETCH"); results.append(alias).append('.').append(fetchJoin); results.append(NL); } }
So, it appears that sticking to the strict definition of isPersistentBag is not sufficient here, because there are still other cases that can return duplicates that aren't persistent bags. I would say that changing the if-condition check to look for either the OneToMany or ManyToMany annotation on the fetched relationship would be a good catch-all, but I'm pretty sure there was a reason we used isPersistentBag in the first place. Looking at SCM history, it shows that jsanda was responsible for this - I'm going to ping him, to see what he can add to this thread.
On 10/26/2010 11:46 AM, John Mazzitelli wrote:
I just noticed that if I add
q.setMaxResults(100)
on my JPA queries, I get 1 row back always (which is good). Using Hibernate API, that still doesn't work, but I think this talks about that issue:
https://forums.hibernate.org/viewtopic.php?f=1&t=958745&start=0
On 10/26/2010 11:16 AM, Joseph Marques wrote:
On 10/26/2010 10:07 AM, John Mazzitelli wrote:
Just did your quick tests - its interesting. The first query (the original with all the joins) returned 48 rows as I said.
48 is the cross of the related rows your fetching: 1 x 16 x 1 x 3
If I just do a single LEFT JOIN FETCH it depends what I'm fetching that determines the rows it returns:
If I join resourceConfigurationDefinition, I get 1 row (good)
Yup, one resource configuration definition for every type in the system.
If I join metricDefinitions, I get 16 rows (bad)
There are 16 metrics for the Linux platform type.
If I join evenDefinition, I get 1 row (good)
There is, by convention, either zero or one event definition for a type.
If I join operationDefinitions, I get 3 rows (bad)
The Linux platform type has 3 operations: discovery, viewProcessList, clearYumMetadataCache
Here's the 5 queries I tested with:
sqlStrings[0] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[1] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[2] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[3] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[4] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
On 10/25/2010 03:09 PM, Joseph Marques wrote:
Yes, I've seen Hibernate do this from time to time when left join
fetching.
Do a little investigation for me - how many rows do you get back if you only fetch eventDefinitions versus if you only fetch operationDefinitions or resourceConfigurationDefinitions? Also, what is the generated SQL for the query (if there is a join to a collection [xxxToMany mapping] anywhere, that would account for the duplicate rows of the projected data.)
On 10/23/2010 03:08 PM, John Mazzitelli wrote:
I don't get it. I run this code, and each System.out.println shows me that the results have 48 rows - but each query should only produce 1 row.
Note: if I go to admin/test/hibernate.jsp, and try this JPQL string, it actually only produces 1 row (the correct results!). I don't know what its doing differently, but here's the code that I tested with - there are actually 4 different ways I try it (two using JPA API, two using Hibernate API - for each I try using a collection of 1 ID integer, and I try using a single parameter value of Integer type).
String sqlString = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
ArrayList<Integer> idList = new ArrayList<Integer>(); idList.add(new Integer(10014));
Query q; List<?> results;
// JPA - parameter is an ArrayList of 1 ID q = entityManager.createQuery(sqlString); q.setParameter("ids", idList); results = q.getResultList(); System.out.println("1~~~~~~~~~size=" + results.size());
// JPA - parameter is a single Integer value q = entityManager.createQuery(sqlString); q.setParameter("ids", new Integer(10014)); results = q.getResultList(); System.out.println("2~~~~~~~~~size=" + results.size());
Session session; org.hibernate.Query hq; session = (Session) entityManager.getDelegate();
// Hibernate - parameter is an ArrayList of 1 ID hq = session.createQuery(sqlString); hq.setParameterList("ids", idList); results = hq.list(); System.out.println("3~~~~~~~~~size=" + results.size());
// Hibernate - parameter is a single Integer value hq = session.createQuery(sqlString); hq.setParameter("ids", new Integer(10014)); results = hq.list(); System.out.println("4~~~~~~~~~size=" + results.size()); _______________________________________________ rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
On 10/26/10 12:47 PM, John Sanda wrote:
The reason I added the isPersistentBag logic is because hibernate does not allow you to simultaneously fetch multiple bags. See http://opensource.atlassian.com/projects/hibernate/browse/HHH-1413. And if I recall from when I encountered this, it seemed to be the case that we have a number of collections that are stored as lists which results in the bag semantics where we should be using sets.
- John
On 10/26/10 12:05 PM, Joseph Marques wrote:
OK, I think the root is this issue is that we're not generating the appropriate SQL. We know that if we're left fetch joining to objects whose entity relationship is xxxToOne, then the join is safe because we'll get the same number of rows with the join than without. However, for xxxToMany relationships, the idea was that we would:
- get the List<Entity> back
- iterate over the list and eager fetch data while the xaction was
still open -- for (next : List<Entity>) { next.getSomeCollection().size() }
You can see this logic in the CriteriaQueryGenerator:
for (String fetchJoin : getFetchFields(criteria)) { if (isPersistentBag(fetchJoin)) { addPersistentBag(fetchJoin); } else { results.append("LEFT JOIN FETCH");
results.append(alias).append('.').append(fetchJoin); results.append(NL); } }
So, it appears that sticking to the strict definition of isPersistentBag is not sufficient here, because there are still other cases that can return duplicates that aren't persistent bags. I would say that changing the if-condition check to look for either the OneToMany or ManyToMany annotation on the fetched relationship would be a good catch-all, but I'm pretty sure there was a reason we used isPersistentBag in the first place. Looking at SCM history, it shows that jsanda was responsible for this - I'm going to ping him, to see what he can add to this thread.
On 10/26/2010 11:46 AM, John Mazzitelli wrote:
I just noticed that if I add
q.setMaxResults(100)
on my JPA queries, I get 1 row back always (which is good). Using Hibernate API, that still doesn't work, but I think this talks about that issue:
https://forums.hibernate.org/viewtopic.php?f=1&t=958745&start=0
On 10/26/2010 11:16 AM, Joseph Marques wrote:
On 10/26/2010 10:07 AM, John Mazzitelli wrote:
Just did your quick tests - its interesting. The first query (the original with all the joins) returned 48 rows as I said.
48 is the cross of the related rows your fetching: 1 x 16 x 1 x 3
If I just do a single LEFT JOIN FETCH it depends what I'm fetching that determines the rows it returns:
If I join resourceConfigurationDefinition, I get 1 row (good)
Yup, one resource configuration definition for every type in the system.
If I join metricDefinitions, I get 16 rows (bad)
There are 16 metrics for the Linux platform type.
If I join evenDefinition, I get 1 row (good)
There is, by convention, either zero or one event definition for a type.
If I join operationDefinitions, I get 3 rows (bad)
The Linux platform type has 3 operations: discovery, viewProcessList, clearYumMetadataCache
Here's the 5 queries I tested with:
sqlStrings[0] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[1] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[2] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[3] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[4] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
On 10/25/2010 03:09 PM, Joseph Marques wrote:
Yes, I've seen Hibernate do this from time to time when
left join fetching.
Do a little investigation for me - how many rows do you get back if you only fetch eventDefinitions versus if you only fetch operationDefinitions or resourceConfigurationDefinitions? Also, what is the generated SQL for the query (if there is a join to a collection [xxxToMany mapping] anywhere, that would account for the duplicate rows of the projected data.)
On 10/23/2010 03:08 PM, John Mazzitelli wrote: > I don't get it. I run this code, and each System.out.println > shows me > that the results have 48 rows - but each query should only > produce 1 row. > > Note: if I go to admin/test/hibernate.jsp, and try this JPQL > string, it > actually only produces 1 row (the correct results!). I don't > know what > its doing differently, but here's the code that I tested with - > there > are actually 4 different ways I try it (two using JPA API, two > using > Hibernate API - for each I try using a collection of 1 ID > integer, and I > try using a single parameter value of Integer type). > > String sqlString = "SELECT resourcetype " + // > "FROM ResourceType resourcetype " + // > "LEFT JOIN FETCH > resourcetype.resourceConfigurationDefinition " + // > "LEFT JOIN FETCH resourcetype.metricDefinitions " + // > "LEFT JOIN FETCH resourcetype.eventDefinitions " + // > "LEFT JOIN FETCH resourcetype.operationDefinitions " > + // > "WHERE ( resourcetype.id in ( :ids ) ) "; > > ArrayList<Integer> idList = new ArrayList<Integer>(); > idList.add(new Integer(10014)); > > Query q; > List<?> results; > > // JPA - parameter is an ArrayList of 1 ID > q = entityManager.createQuery(sqlString); > q.setParameter("ids", idList); > results = q.getResultList(); > System.out.println("1~~~~~~~~~size=" + results.size()); > > // JPA - parameter is a single Integer value > q = entityManager.createQuery(sqlString); > q.setParameter("ids", new Integer(10014)); > results = q.getResultList(); > System.out.println("2~~~~~~~~~size=" + results.size()); > > Session session; > org.hibernate.Query hq; > session = (Session) entityManager.getDelegate(); > > // Hibernate - parameter is an ArrayList of 1 ID > hq = session.createQuery(sqlString); > hq.setParameterList("ids", idList); > results = hq.list(); > System.out.println("3~~~~~~~~~size=" + results.size()); > > // Hibernate - parameter is a single Integer value > hq = session.createQuery(sqlString); > hq.setParameter("ids", new Integer(10014)); > results = hq.list(); > System.out.println("4~~~~~~~~~size=" + results.size()); > _______________________________________________ > rhq-devel mailing list > rhq-devel@lists.fedorahosted.org > https://fedorahosted.org/mailman/listinfo/rhq-devel _______________________________________________ rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
Fair enough, so the fix you did (https://bugzilla.redhat.com/show_bug.cgi?id=RHQ-2403 git commit 932744b and 74c0392) resolved the "can not fetch multiple bags" issue, but I think we still have an issue when eager fetching any type of entity-related collection (xxxToMany mapping), right?
Granted, doing "for (next : List<Entity>) { next.getSomeCollection().size() }" suffers the N+1 query issue (actually, 'm*N+1' where 'm' is the number of collections being fetched), but at least it would return the correct results (1 row with all collections properly fetched, instead of the 48 mazz is seeing).
In the future, I think we could implement a more sophisticated mechanism that removes the performance penalty:
entity ObjectA { @OneToMany Set<ObjectB> myObjectBs; } entity ObjectB { @ManyToOne ObjectA theObjectA; }
List<ObjectA> resultObjects = criteriaBasedFetch(ObjectA.class, ...); List<Integer> resultPKs = getPrimaryKeys(resultObjects); List<ObjectB> relatedObjects = criteriaBasedFetch(ObjectB.class, "theObjectA", resultPKs); wireTogether(resultObjects, relatedObjects, "myObjectBs");
Using this bulk loading mechanism, and then reflection to wire the objects together after the query returns, the performance impact reduces to 'm + 1' (again, where 'm' is the number of collections being fetched).
-joseph
On 10/26/2010 12:47 PM, John Sanda wrote:
The reason I added the isPersistentBag logic is because hibernate does not allow you to simultaneously fetch multiple bags. See http://opensource.atlassian.com/projects/hibernate/browse/HHH-1413. And if I recall from when I encountered this, it seemed to be the case that we have a number of collections that are stored as lists which results in the bag semantics where we should be using sets.
- John
On 10/26/10 12:05 PM, Joseph Marques wrote:
OK, I think the root is this issue is that we're not generating the
appropriate SQL. We know that if we're left fetch joining to objects whose entity relationship is xxxToOne, then the join is safe because we'll get the same number of rows with the join than without. However, for xxxToMany relationships, the idea was that we would:
- get the List<Entity> back
- iterate over the list and eager fetch data while the xaction was
still open -- for (next : List<Entity>) { next.getSomeCollection().size() }
You can see this logic in the CriteriaQueryGenerator:
for (String fetchJoin : getFetchFields(criteria)) { if (isPersistentBag(fetchJoin)) { addPersistentBag(fetchJoin); } else { results.append("LEFT JOIN FETCH"); results.append(alias).append('.').append(fetchJoin); results.append(NL); } }
So, it appears that sticking to the strict definition of isPersistentBag is not sufficient here, because there are still other cases that can return duplicates that aren't persistent bags. I would say that changing the if-condition check to look for either the OneToMany or ManyToMany annotation on the fetched relationship would be a good catch-all, but I'm pretty sure there was a reason we used isPersistentBag in the first place. Looking at SCM history, it shows that jsanda was responsible for this - I'm going to ping him, to see what he can add to this thread.
On 10/26/2010 11:46 AM, John Mazzitelli wrote:
I just noticed that if I add
q.setMaxResults(100)
on my JPA queries, I get 1 row back always (which is good). Using Hibernate API, that still doesn't work, but I think this talks about that issue:
https://forums.hibernate.org/viewtopic.php?f=1&t=958745&start=0
On 10/26/2010 11:16 AM, Joseph Marques wrote:
On 10/26/2010 10:07 AM, John Mazzitelli wrote:
Just did your quick tests - its interesting. The first query (the original with all the joins) returned 48 rows as I said.
48 is the cross of the related rows your fetching: 1 x 16 x 1 x 3
If I just do a single LEFT JOIN FETCH it depends what I'm fetching that determines the rows it returns:
If I join resourceConfigurationDefinition, I get 1 row (good)
Yup, one resource configuration definition for every type in the system.
If I join metricDefinitions, I get 16 rows (bad)
There are 16 metrics for the Linux platform type.
If I join evenDefinition, I get 1 row (good)
There is, by convention, either zero or one event definition for a type.
If I join operationDefinitions, I get 3 rows (bad)
The Linux platform type has 3 operations: discovery, viewProcessList, clearYumMetadataCache
Here's the 5 queries I tested with:
sqlStrings[0] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[1] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[2] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[3] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[4] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
On 10/25/2010 03:09 PM, Joseph Marques wrote:
Yes, I've seen Hibernate do this from time to time when left join
fetching.
Do a little investigation for me - how many rows do you get back if you only fetch eventDefinitions versus if you only fetch operationDefinitions or resourceConfigurationDefinitions? Also, what is the generated SQL for the query (if there is a join to a collection [xxxToMany mapping] anywhere, that would account for the duplicate rows of the projected data.)
On 10/23/2010 03:08 PM, John Mazzitelli wrote: > I don't get it. I run this code, and each System.out.println shows me > that the results have 48 rows - but each query should only produce 1 row. > > Note: if I go to admin/test/hibernate.jsp, and try this JPQL string, it > actually only produces 1 row (the correct results!). I don't know what > its doing differently, but here's the code that I tested with - there > are actually 4 different ways I try it (two using JPA API, two using > Hibernate API - for each I try using a collection of 1 ID integer, and I > try using a single parameter value of Integer type). > > String sqlString = "SELECT resourcetype " + // > "FROM ResourceType resourcetype " + // > "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // > "LEFT JOIN FETCH resourcetype.metricDefinitions " + // > "LEFT JOIN FETCH resourcetype.eventDefinitions " + // > "LEFT JOIN FETCH resourcetype.operationDefinitions " + // > "WHERE ( resourcetype.id in ( :ids ) ) "; > > ArrayList<Integer> idList = new ArrayList<Integer>(); > idList.add(new Integer(10014)); > > Query q; > List<?> results; > > // JPA - parameter is an ArrayList of 1 ID > q = entityManager.createQuery(sqlString); > q.setParameter("ids", idList); > results = q.getResultList(); > System.out.println("1~~~~~~~~~size=" + results.size()); > > // JPA - parameter is a single Integer value > q = entityManager.createQuery(sqlString); > q.setParameter("ids", new Integer(10014)); > results = q.getResultList(); > System.out.println("2~~~~~~~~~size=" + results.size()); > > Session session; > org.hibernate.Query hq; > session = (Session) entityManager.getDelegate(); > > // Hibernate - parameter is an ArrayList of 1 ID > hq = session.createQuery(sqlString); > hq.setParameterList("ids", idList); > results = hq.list(); > System.out.println("3~~~~~~~~~size=" + results.size()); > > // Hibernate - parameter is a single Integer value > hq = session.createQuery(sqlString); > hq.setParameter("ids", new Integer(10014)); > results = hq.list(); > System.out.println("4~~~~~~~~~size=" + results.size()); > _______________________________________________ > rhq-devel mailing list > rhq-devel@lists.fedorahosted.org > https://fedorahosted.org/mailman/listinfo/rhq-devel _______________________________________________ rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel@lists.fedorahosted.org