Nothing obvious jumps out at me, but a proper OpenJPA Junit would make
recreating this problem a much easier task.
Thanks,
Rick
On Thu, Dec 11, 2014 at 8:26 PM, jacksmith <leonliu...@gmail.com> wrote:
> 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<?>> selections = new
> ArrayList<Selection<?>>();
>
> 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.
>
--
*Rick Curtis*
