OpenJPA generate wrong query when using postgres as DBDictionary

[jacksmith]

Our application is using OpenJPA 2.2 with Oracle, everything is working fine.

Now we need to move to postgres database, but we run into a basic issue, the
generated query is wrong, it has extra table in the FROM statement which
causes cartesian join.

any help will be deeply appreciated. if you need anything else, please let
me know.

It's also very easy to reproduce the issue, following is the method to
reproduce it:
/**
* with postgres as dbdictionay, this method produces wrong query like (with
oracle it works fine):
* SELECT t1.ORGN_ID FROM V_LOCATION t0 , T_DIM_ORGN t1 WHERE (t1.CLNT_OBJ_ID
= ?) 
* 
* the correct query should be like:
* SELECT t1.ORGN_ID FROM T_DIM_ORGN t1 WHERE (t1.CLNT_OBJ_ID = ?) 
*/
public void getDeptOptionsByUser(String filterId) {

        List<Predicate> predicates = new ArrayList<Predicate>();
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Tuple> cq = cb.createTupleQuery();

        /* the following line is purposely added to reproduce the issue. our 
real
application code is
        * much more complex and we don't have a way to bypass the issue
        */
        Root<LocationHierarchy> loc = cq.from(LocationHierarchy.class);

        Root<DimOrganization> org = cq.from(DimOrganization.class);

        predicates.add(cb.equal(org.get(DimOrganization_.clntObjId), "dummy"));

        List<Selection&lt;?>> selections = new ArrayList<Selection&lt;?>>();

        selections.add(org.get(DimOrganization_.orgnId).alias("filterId"));

        cq.multiselect(selections).where(predicates.toArray(new Predicate[] 
{}));

        System.out.println("Query String:" + cq.toString());
        em.createQuery(cq).getResultList();
}

following is a simplified version of our persistence.xml.

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
        xmlns="http://java.sun.com/xml/ns/persistence";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd";>

        <persistence-unit name="dw-persistence" transaction-type="JTA">
                <jta-data-source>jdbc/dw</jta-data-source>
                
                <class>com.entities.dm.DimOrganization</class>
                <class>com.entities.facts.FactWorkAssignment</class>
                <class>com.entities.dm.DimWorkLocation</class>
                <class>com.entities.dm.LocationHierarchy</class>
                <class>com.entities.dm.LocationHierarchyId</class>
                <exclude-unlisted-classes>true</exclude-unlisted-classes>

                <properties>
                        <property name="openjpa.TransactionMode" 
value="managed" />
                        <property name="openjpa.ConnectionFactoryMode" 
value="managed" />
                        <property name="openjpa.jdbc.DBDictionary"
value="postgres(CrossJoinClause=',')" />        
                        
                        <property name="openjpa.Log" value="SQL=TRACE"/>
                        <property name="openjpa.ConnectionFactoryProperties"
value="PrettyPrint=true, PrettyPrintLineLength=72,PrintParameters=true"/>
                        <property name="openjpa.Compatibility"
value="QuotedNumbersInQueries=true" />
                </properties>
        </persistence-unit>
        
                
</persistence>



--
View this message in context: 
http://openjpa.208410.n2.nabble.com/OpenJPA-generate-wrong-query-when-using-postgres-as-DBDictionary-tp7587490.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.