[
https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806792#action_12806792
]
Milosz Tylenda commented on OPENJPA-1483:
-----------------------------------------
Hi Fay, of course, my question was wrong. I forgot you stated it clearly even
in issue description that the problem is only with compound PKs. Sorry for the
confusion.
As for the reviewing the new patch - yes, I reviewed it but since this is you
who is the expert in that matter, I am learning from the patch rather than
judging :) I would only think these small issues:
1. I think that by "common table expression" people usually mean "WITH cte_name
... SELECT ... FROM cte_name ..." syntax where this cte can also be recursive.
What we need for counting compund PKs, is the database ability to execute
subqueries in FROM clause. Thus, I would name the new property
"supportsSubselectInFrom". What do you think?
2. We should document the new property in the user manual.
3. When the patch is applied, it would be good to open a new issue to
investigate the value of the new property for other databases. I believe quite
many of them support subqueries in FROM clause.
4. In the test case I would change
+ if (!(dict instanceof DB2Dictionary))
+ return;
to testing the value of the new property. That way, the test case will
automatically be executed also on other databases which have the support.
> count (Distinct e) in JPQL gives wrong result when the id field is a compound
> primary key
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-1483
> URL: https://issues.apache.org/jira/browse/OPENJPA-1483
> Project: OpenJPA
> Issue Type: Bug
> Affects Versions: 2.1.0
> Reporter: Fay Wang
> Fix For: 2.1.0
>
> Attachments: OPENJPA-1483-2.patch, OPENJPA-1483.patch
>
>
> This is a fundamental problem with count when compound primary key is
> involved.
> (1) If no relation navigation is involved:
> String jpql = "SELECT COUNT (DISTINCT e) FROM G2 e";
> With the property below:
> <property name="openjpa.jdbc.DBDictionary"
> value="db2(useWildCardForCount=true)" />
> Openjpa will generate the following sql and return the correct count:
> SELECT COUNT(*) FROM G2 t0 optimize for 1 row
> (2) If there is relation navigation invloved:
> String jpql = "SELECT COUNT (DISTINCT f1.g2) FROM F1 f1";
>
> The property of useWildCardForCount will not generate correct sql with
> right result. However, given the object-relational impedance mismatch, there
> is no corresponding SQL construct for count of multiple primary keys, and
> there is no clean and generic solution to solve this problem. The only
> workaround is to use native SQL with table expression:
> SELECT COUNT(*)
> FROM (SELECT DISTINCT G1.G1PK, G1.G2PK FROM F1 t0 INNER JOIN G2 t1 ON
> t0.G1PK = t1.G1PK AND t0.G2PK = t1.G2PK)) TX;
> Rather than giving a wrong answer, OpenJPA should give an Unsupported
> exception.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
