You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Fay Wang (JIRA)" <ji...@apache.org> on 2010/01/26 19:36:34 UTC
[jira] Created: (OPENJPA-1483) count (Distinct e) in JPQL will give
wrong result is the id fieldis a compound primary key
count (Distinct e) in JPQL will give wrong result is the id fieldis 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
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.
[jira] Commented: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805976#action_12805976 ]
Fay Wang commented on OPENJPA-1483:
-----------------------------------
Limited support for count(distinct compound key) will be supported when count(compound primary key) appears in the projection list. This projection list will have only one projection item, ie, count, in it. OpenJPA will use generate the following SQL:
(1)
String jpql = "SELECT COUNT (DISTINCT e) FROM G2 e";
generated SQL:
SELECT COUNT(*) FROM G2 t0
(2)
String jpql = "SELECT COUNT (DISTINCT f1.g2) FROM F1 f1";
generated SQL:
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)
For count(compound key), OpenJPA will generate count(*) in the SQL.
An unsupported exception will be thrown in all other situations.
> 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
>
>
> 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.
[jira] Updated: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-1483:
------------------------------
Attachment: OPENJPA-1483.patch
The patch will produce the following sql:
(1) "select count (distinct f1) from F1 f1",
SELECT COUNT(*) FROM (SELECT DISTINCT t0.F1PK, t0.F2PK FROM F1 t0 ) s optimize for 1 row
(2) "select count (distinct f1.pk) from F1 f1",
SELECT COUNT(*) FROM (SELECT DISTINCT t0.F1PK, t0.F2PK FROM F1 t0 ) s optimize for 1 row
(3)"SELECT COUNT (DISTINCT f1.g2) FROM F1 f1",
SELECT COUNT(*) FROM (SELECT DISTINCT t1.G1PK, t1.G2PK FROM F1 t0 INNER JOIN G2 t1 ON t0.G1PK = t1.G1PK AND t0.G2PK = t1.G2PK ) s optimize for 1 row
(4) "SELECT COUNT (DISTINCT f1.g2.pk) FROM F1 f1",
SELECT COUNT(*) FROM (SELECT DISTINCT t1.G1PK, t1.G2PK FROM F1 t0 INNER JOIN G2 t1 ON t0.G1PK = t1.G1PK AND t0.G2PK = t1.G2PK ) s optimize for 1 row
(5)"select count (f1) from F1 f1",
SELECT COUNT(*) FROM F1 t0 optimize for 1 row
(6)"select count (f1.pk) from F1 f1",
SELECT COUNT(*) FROM F1 t0 optimize for 1 row
(7) "SELECT COUNT (f1.g2) FROM F1 f1",
SELECT COUNT(*) FROM F1 t0 INNER JOIN G2 t1 ON t0.G1PK = t1.G1PK AND t0.G2PK = t1.G2PK optimize for 1 row
(8)"SELECT COUNT (f1.g2.pk) FROM F1 f1",
SELECT COUNT(*) FROM F1 t0 INNER JOIN G2 t1 ON t0.G1PK = t1.G1PK AND t0.G2PK = t1.G2PK optimize for 1 row
> 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.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.
[jira] Updated: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Dick updated OPENJPA-1483:
----------------------------------
Fix Version/s: 1.2.3
1.3.0
Patch Info: [Patch Available]
> 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.0.0-beta
> Reporter: Fay Wang
> Fix For: 1.2.3, 1.3.0, 2.0.0-beta2
>
> Attachments: OPENJPA-1483-1.2.x.patch.txt, OPENJPA-1483-1.3.x.patch.txt, 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.
[jira] Commented: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12828262#action_12828262 ]
Catalina Wei commented on OPENJPA-1483:
---------------------------------------
Hi Fay,
1. for newly added unsupported messages, could you add message id to localizer.properties ?
2. could you move the block of the code in JDBCStoreQuery that handles the isCountDistinctMultiCols to SelectConstructor.evaluate() so that kernel.exp.Count visibility remains private at package level ?
> 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.
[jira] Updated: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-1483:
------------------------------
Attachment: OPENJPA-1483-2.patch
Since not all databases support common table expression, a DBDictioinary attribute "supportsCommonTableExpression" is introduced. A test case is also included to run on DB2. For those databases that do not support common table expression, openjpa will throw UnsupportedException instead of giving a misleading result for Count (Distinct compound PK).
> 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.
[jira] Closed: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang closed OPENJPA-1483.
-----------------------------
> 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.
[jira] Resolved: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang resolved OPENJPA-1483.
-------------------------------
Resolution: Fixed
> 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.
[jira] Commented: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806724#action_12806724 ]
Milosz Tylenda commented on OPENJPA-1483:
-----------------------------------------
Hi Fay, does your patch make useWildCardForCount property obsolete? I understand your description that with the patch we would always use wild card in SELECT COUNT. If so, we could remove the useWildCardForCount property in 2.1.0.
> 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.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.
[jira] Updated: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Heath Thomann (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Heath Thomann updated OPENJPA-1483:
-----------------------------------
Attachment: OPENJPA-1483-1.2.x.patch.txt
OPENJPA-1483-1.3.x.patch.txt
Added OPENJPA-1483-1.2.x.patch.txt for 1.2.x code and OPENJPA-1483-1.3.x.patch.txt for 1.3.x code, as their names imply.
> 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.0.0-beta
> Reporter: Fay Wang
> Fix For: 2.0.0-beta2
>
> Attachments: OPENJPA-1483-1.2.x.patch.txt, OPENJPA-1483-1.3.x.patch.txt, 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.
[jira] Updated: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Donald Woods (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Donald Woods updated OPENJPA-1483:
----------------------------------
Affects Version/s: (was: 2.0.0)
2.0.0-beta
Fix Version/s: (was: 2.0.0)
2.0.0-beta2
> 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.0.0-beta
> Reporter: Fay Wang
> Fix For: 2.0.0-beta2
>
> 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.
[jira] Commented: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12828186#action_12828186 ]
Fay Wang commented on OPENJPA-1483:
-----------------------------------
Hi Milosz, thank you for pointing out this. Common table expression and table expression semantically are the same, but syntactically, they are quite different. For cte, you can define once and use it multiple times. it is like a function definition of a program. In this JIRA, it is table expression, but not common table expression that is required:
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;
> 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.
[jira] Updated: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-1483:
------------------------------
Summary: count (Distinct e) in JPQL gives wrong result when the id field is a compound primary key (was: count (Distinct e) in JPQL will give wrong result is the id fieldis a compound primary key)
> 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
>
>
> 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.
[jira] Commented: (OPENJPA-1483) count (Distinct e) in JPQL gives
wrong result when the id field is a compound primary key
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806778#action_12806778 ]
Fay Wang commented on OPENJPA-1483:
-----------------------------------
Hi Milosz, no, we will not *always* use Count(*) for select count in all scenarios. The Count(*) will be used only when SELECT COUNT(compound PK). Could you please review OPENJPA-1483-2.patch. Thanks!
> 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.