problem with ResourceTypeCriteria

Joseph Marques jmarques at redhat.com
Tue Oct 26 16:05:46 UTC 2010


  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 at lists.fedorahosted.org
>>>>> https://fedorahosted.org/mailman/listinfo/rhq-devel
>>>> _______________________________________________
>>>> rhq-devel mailing list
>>>> rhq-devel at lists.fedorahosted.org
>>>> https://fedorahosted.org/mailman/listinfo/rhq-devel
>>> _______________________________________________
>>> rhq-devel mailing list
>>> rhq-devel at lists.fedorahosted.org
>>> https://fedorahosted.org/mailman/listinfo/rhq-devel
>> _______________________________________________
>> rhq-devel mailing list
>> rhq-devel at lists.fedorahosted.org
>> https://fedorahosted.org/mailman/listinfo/rhq-devel
> _______________________________________________
> rhq-devel mailing list
> rhq-devel at lists.fedorahosted.org
> https://fedorahosted.org/mailman/listinfo/rhq-devel



More information about the rhq-devel mailing list