issue-jpa.jar <http://openjpa.208410.n2.nabble.com/file/n7587513/issue-jpa.jar>
Hi Rick, Thanks for looking into the issues. I was out of state last week, sorry for the late response. I modified the code to better reproduce the issue. Basically in the source code you will see TestCase1.java and TestCase2.java, they are all standalone java code, you can run them directly (I didn't include OpenJPA.jar to reduce the size) For Test Case 1: the generated query for DB2, MySQL or Postgres is like SELECT SUM(t0.empl_cnt) FROM T_FACT_WORK_ASGNMT t0 WHERE (t0.CLNT_OBJ_ID = ? AND NOT (EXISTS (SELECT t3.PERS_OBJ_ID FROM T_FACT_WORK_ASGNMT t1 JOIN T_FACT_WORK_ASGNMT t2 ON (1 = 1), T_DIM_PERS t3 WHERE (t2.pers_ky = t3.PERS_KY) ))) optimize for 1 row As you can see the subquery is not right, it should not have T_FACT_WORK_ASGNMT twice, and in the java code, it was referred only once. For Test Case 2: the generated query for DB2, MySQL or Postgres is like: SELECT t1.ORGN_ID FROM V_LOCATION_HIERARCHY t0 JOIN T_DIM_ORGN t1 ON (1 = 1) WHERE (t1.CLNT_OBJ_ID = ?) [params=(String) dummy] And the interesting thing is if I swap line 95 and 97 in QueryIssues.java, the query will change to the following, which is sign to me there is a bug in OpenJPA. SELECT t1.ORGN_ID FROM T_DIM_PERS t0 JOIN T_DIM_ORGN t1 ON (1 = 1) WHERE (t1.CLNT_OBJ_ID = ?) In both of the scenarios, the generated queries are correct if I set DBDictionary to oracle. Thanks again for the help, -- View this message in context: http://openjpa.208410.n2.nabble.com/OpenJPA-generate-wrong-query-when-using-postgres-as-DBDictionary-tp7587490p7587513.html Sent from the OpenJPA Users mailing list archive at Nabble.com.