Bug 115102

Description of problem:
Whenever a path is added to a query that crosses an 0..1 or 0..n
association, persistence changes to using an outer join instead or a
regular join. In a great many cases this is uneccessary because there
is typically a condition in the where clause that makes the outer join
behave in the same way as a regular join. The presense of the outer
join is making PostgreSQL choose a horrifically worse query plan to
the effect that a simple query changes from 22ms to 350ms.

For example, if I have have a model:

object type Term extends ACSObject {
    Integer[1..1] uniqueID = trm_terms.unique_id INTEGER;
    Boolean[1..1] inAtoZ = trm_terms.is_atoz BIT;

    component Category[1..1] \model = join trm_terms.model_category_id 
                                       to cat_categories.category_id;
}

If I now do:


   ACSObject object = getObject(state);
   DataCollection terms = SessionManager.getSession()
     .retrieve(Term.BASE_DATA_OBJECT_TYPE);
        
   terms.addEqualsFilter("model.childObjects.id", object.getID());

ie, get all terms corresponding to assigned categories on the object

It generates a query:

select tt.term_id as c_1,
       ao.object_type as c_2,
       ao.display_name as c_3,
       ao.default_domain_class as c_4,
       tt.unique_id as c_5,
       tt.is_atoz as c_6,
       tt.shortcut as c_7,
       tt.is_preferred as c_8,
       model__cc.name as c_9,
       tt.model_category_id as c_10,
       tt.domain as c_11,
       domain__td.url as c_12
from trm_terms tt
     join acs_objects ao on ao.object_id = tt.term_id
     join cat_categories model__cc on model__cc.category_id =
tt.model_category_id
     join trm_domains domain__td on domain__td.key = tt.domain
     left join cat_object_category_map model__cocm on
model__cocm.category_id = tt.model_category_id
where (model__cocm.object_id = '9004');


This left join against the cat_object_category_map is redundant becuse
the of the constraining where clause means that no rows will be
returned at all if there wasn't a matching row in the
cat_object_catgory_map. I'll attach an SQL file with the explain plans
indicating the problem. 

Version-Release number of selected component (if applicable):


How reproducible:


Steps to Reproduce:
1.
2.
3.
  
Actual results:


Expected results:


Additional info: