Description Rohit Banga 2010-12-21 00:35:50 EST

Build Identifier: 

I have a phone table and an employee table. The phone table has a foreign key constraint on the employee id. An employee can have multiple phones.

I am using the following code to query the employee table:




        Class<?> emptype = dcl.createDynamicClass("jpatest.empinfo");
        Class<?> phonetype = dcl.createDynamicClass("jpatest.phone");

        DynamicTypeBuilder empBuilder = new JPADynamicTypeBuilder(primaryType, null, "empinfo");
        DynamicTypeBuilder phoneBuilder = new JPADynamicTypeBuilder(secondaryType, null, "phone");

        /**
          ... add the direct mappings here
         */

        empBuilder.addOneToManyMapping("empinfo.phone.empid", phoneBuilder.getType(), "phone.empid");
        phoneBuilder.addOneToOneMapping("empinfo.phone.empid", empBuilder.getType(), "phone.empid");

        helper.addTypes(false, true, empBuilder.getType(), phoneBuilder.getType());


        ReadAllQuery query = new DynamicHelper(session).newReadAllQuery(empBuilder.getType().getDescriptor().getAlias());

        // if I reverse the order of the two, then the query is generated properly
        query.setSelectionCriteria(query.getExpressionBuilder().anyOf("empinfo.phone.empid").get("phone").equal("11"));
        query.addJoinedAttribute(query.getExpressionBuilder().anyOf("empinfo.phone.empid"));

        ReadAllQuery query1 = new DynamicHelper(session).newReadAllQuery(empBuilder.getType().getDescriptor().getAlias());

		query1.setSelectionCriteria(query1.getExpressionBuilder().anyOf("empinfo.phone.empid").get("phone").equal("11"));

		query1.addJoinedAttribute(query1.getExpressionBuilder().anyOf("empinfo.phone.empid"));
        

If the join attribute is added before the selection criteria is set, the SQL generated is:


select * from empinfo t0, phone t1, phone t2, where t0.empid=t1.empid and t1.phone='11' and t2.empid=t0.empid

which returns the correct results. However if I add the selection criteria before setting the join attributes the following query is generated:

select * from empinfo t0, phone t1 where t0.empid=t1.empid and t1.phone='11' 

which does not return all the phone numbers of an employee having phone number '11'. In this case, if I generate the query for the second time in the same session (query1 in the code above), the correct query is generated, however again not all phone numbers of the employee having phone number '11' are generated (*perhaps due to caching???*) 


If I do not set any selection criteria the first time I query, then all the information is returned the first time as expected. If for the second time I query by setting the selection criteria first and the join attribtues later, then the correct result is obtained which again suggests some caching coming into play.

Using the following does not help:
query.addJoinedAttribute((new ExpressionBuilder()).anyOf("empinfo.phone.empid"));
query.setSelectionCriteria((new ExpressionBuilder().anyOf("empinfo.phone.empid").get("phone").equal("11"));

using the new ExpressionBuilder(empType) instead returns all phone numbers for each employee which is again wrong.

It seems the expression builder object is not clean across multiple invocations.

A complete discussion can be found here:
http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg05712.html

Reproducible: Always

Steps to Reproduce:
1.Use the code given in the bug details to query the a table having a one-to-many relation with another table.
2. Set the selection criteria before adding the join attributes.
3. Generate a query in the same session again. Set the selection criteria before adding the join attribute. The query should be generated correctly but the results returned on querying the dynamic entity would be incorrect.