openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jacksmith <leonliu...@gmail.com>
Subject Re: OpenJPA generate wrong query when using postgres as DBDictionary
Date Tue, 23 Dec 2014 13:41:41 GMT
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.

Mime
View raw message