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/09/24 20:16:32 UTC
[jira] Created: (OPENJPA-1814) JPQL fails with Group By and Having
aggregate_expression IN (subquery)
JPQL fails with Group By and Having aggregate_expression IN (subquery)
----------------------------------------------------------------------
Key: OPENJPA-1814
URL: https://issues.apache.org/jira/browse/OPENJPA-1814
Project: OpenJPA
Issue Type: Bug
Components: kernel
Affects Versions: 2.1.0
Reporter: Fay Wang
Assignee: Fay Wang
The following JPQL fail:
String jpql = "SELECT a.uuid from EntityA a WHERE a.name = 'test' " +
"GROUP BY a.date1, a.uuid " +
(1) "HAVING MAX(a.date1) IN (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
(2) "HAVING MAX(a.date1) = (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
org.apache.openjpa.persistence.ArgumentException: Encountered "MAX ( a .date1) IN" at character 168, but expected: ["(", ")", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT","COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT","LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD","NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT",
"SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM","UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>,<IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9566)
at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9443)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
The fix involves two changes:
(1) jjt grammar change
(2) OpenJPA performs a preliminary validation to ensure the expression in the having clause is included in the group-by list. However, this validation checking should be done only on the LHS of the having clause (see expr1 below), not on the RHS (see expr2 below). For example:
Having expr1 = expr2
Only expr1 should be in the group-by list
The current visitor pattern can not tell which node to visit without massive change. The alternative is to disable the checking by OpenJPA and let the backend to determine whether the generated SQL is valid or not.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-1814) JPQL fails with Group By and Having
aggregate_expression IN (subquery)
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1814?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Catalina Wei updated OPENJPA-1814:
----------------------------------
Assignee: Catalina Wei (was: Fay Wang)
Fix Version/s: 2.1.0
Environment: Fix committed to trunk at revision #1002419.
OPENJPA-1814-2.patch committed to trunk at revision #1002419.
> JPQL fails with Group By and Having aggregate_expression IN (subquery)
> ----------------------------------------------------------------------
>
> Key: OPENJPA-1814
> URL: https://issues.apache.org/jira/browse/OPENJPA-1814
> Project: OpenJPA
> Issue Type: Bug
> Components: kernel
> Affects Versions: 2.1.0
> Environment: Fix committed to trunk at revision #1002419.
> Reporter: Fay Wang
> Assignee: Catalina Wei
> Fix For: 2.1.0
>
> Attachments: OPENJPA-1814-1.patch, OPENJPA-1814-2.patch, OPENJPA-1814.patch
>
>
> The following JPQL fail:
> String jpql = "SELECT a.uuid from EntityA a WHERE a.name = 'test' " +
> "GROUP BY a.date1, a.uuid " +
> (1) "HAVING MAX(a.date1) IN (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> (2) "HAVING MAX(a.date1) = (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> org.apache.openjpa.persistence.ArgumentException: Encountered "MAX ( a .date1) IN" at character 168, but expected: ["(", ")", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT","COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT","LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD","NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT",
> "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM","UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>,<IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
> at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9566)
> at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9443)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
> The fix involves two changes:
> (1) jjt grammar change
> (2) OpenJPA performs a preliminary validation to ensure the expression in the having clause is included in the group-by list. However, this validation checking should be done only on the LHS of the having clause (see expr1 below), not on the RHS (see expr2 below). For example:
> Having expr1 = expr2
> Only expr1 should be in the group-by list
> The current visitor pattern can not tell which node to visit without massive change. The alternative is to disable the checking by OpenJPA and let the backend to determine whether the generated SQL is valid or not.
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-1814) JPQL fails with Group By and Having
aggregate_expression IN (subquery)
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1814?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Dick updated OPENJPA-1814:
----------------------------------
Affects Version/s: 1.2.2
> JPQL fails with Group By and Having aggregate_expression IN (subquery)
> ----------------------------------------------------------------------
>
> Key: OPENJPA-1814
> URL: https://issues.apache.org/jira/browse/OPENJPA-1814
> Project: OpenJPA
> Issue Type: Bug
> Components: kernel
> Affects Versions: 1.2.2, 2.1.0
> Environment: Fix committed to trunk at revision #1002419.
> Reporter: Fay Wang
> Assignee: Catalina Wei
> Fix For: 1.2.3, 2.1.0
>
> Attachments: OPENJPA-1814-1.2.x.patch, OPENJPA-1814-1.patch, OPENJPA-1814-2.patch, OPENJPA-1814.patch
>
>
> The following JPQL fail:
> String jpql = "SELECT a.uuid from EntityA a WHERE a.name = 'test' " +
> "GROUP BY a.date1, a.uuid " +
> (1) "HAVING MAX(a.date1) IN (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> (2) "HAVING MAX(a.date1) = (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> org.apache.openjpa.persistence.ArgumentException: Encountered "MAX ( a .date1) IN" at character 168, but expected: ["(", ")", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT","COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT","LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD","NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT",
> "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM","UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>,<IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
> at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9566)
> at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9443)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
> The fix involves two changes:
> (1) jjt grammar change
> (2) OpenJPA performs a preliminary validation to ensure the expression in the having clause is included in the group-by list. However, this validation checking should be done only on the LHS of the having clause (see expr1 below), not on the RHS (see expr2 below). For example:
> Having expr1 = expr2
> Only expr1 should be in the group-by list
> The current visitor pattern can not tell which node to visit without massive change. The alternative is to disable the checking by OpenJPA and let the backend to determine whether the generated SQL is valid or not.
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-1814) JPQL fails with Group By and Having
aggregate_expression IN (subquery)
Posted by "Heath Thomann (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1814?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Heath Thomann updated OPENJPA-1814:
-----------------------------------
Attachment: OPENJPA-1814-1.2.x.patch
I'm providing a patch (OPENJPA-1814-1.2.x.patch) which is for 1.2.x and it a "backport" of OPENJPA-1814-2.patch.
Thanks,
Heath
> JPQL fails with Group By and Having aggregate_expression IN (subquery)
> ----------------------------------------------------------------------
>
> Key: OPENJPA-1814
> URL: https://issues.apache.org/jira/browse/OPENJPA-1814
> Project: OpenJPA
> Issue Type: Bug
> Components: kernel
> Affects Versions: 2.1.0
> Environment: Fix committed to trunk at revision #1002419.
> Reporter: Fay Wang
> Assignee: Catalina Wei
> Fix For: 2.1.0
>
> Attachments: OPENJPA-1814-1.2.x.patch, OPENJPA-1814-1.patch, OPENJPA-1814-2.patch, OPENJPA-1814.patch
>
>
> The following JPQL fail:
> String jpql = "SELECT a.uuid from EntityA a WHERE a.name = 'test' " +
> "GROUP BY a.date1, a.uuid " +
> (1) "HAVING MAX(a.date1) IN (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> (2) "HAVING MAX(a.date1) = (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> org.apache.openjpa.persistence.ArgumentException: Encountered "MAX ( a .date1) IN" at character 168, but expected: ["(", ")", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT","COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT","LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD","NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT",
> "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM","UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>,<IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
> at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9566)
> at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9443)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
> The fix involves two changes:
> (1) jjt grammar change
> (2) OpenJPA performs a preliminary validation to ensure the expression in the having clause is included in the group-by list. However, this validation checking should be done only on the LHS of the having clause (see expr1 below), not on the RHS (see expr2 below). For example:
> Having expr1 = expr2
> Only expr1 should be in the group-by list
> The current visitor pattern can not tell which node to visit without massive change. The alternative is to disable the checking by OpenJPA and let the backend to determine whether the generated SQL is valid or not.
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-1814) JPQL fails with Group By and Having
aggregate_expression IN (subquery)
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1814?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Dick updated OPENJPA-1814:
----------------------------------
Fix Version/s: 1.3.0
2.0.2
> JPQL fails with Group By and Having aggregate_expression IN (subquery)
> ----------------------------------------------------------------------
>
> Key: OPENJPA-1814
> URL: https://issues.apache.org/jira/browse/OPENJPA-1814
> Project: OpenJPA
> Issue Type: Bug
> Components: kernel
> Affects Versions: 1.2.2, 2.1.0
> Environment: Fix committed to trunk at revision #1002419.
> Reporter: Fay Wang
> Assignee: Catalina Wei
> Fix For: 1.2.3, 1.3.0, 2.0.2, 2.1.0
>
> Attachments: OPENJPA-1814-1.2.x.patch, OPENJPA-1814-1.patch, OPENJPA-1814-2.patch, OPENJPA-1814.patch
>
>
> The following JPQL fail:
> String jpql = "SELECT a.uuid from EntityA a WHERE a.name = 'test' " +
> "GROUP BY a.date1, a.uuid " +
> (1) "HAVING MAX(a.date1) IN (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> (2) "HAVING MAX(a.date1) = (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> org.apache.openjpa.persistence.ArgumentException: Encountered "MAX ( a .date1) IN" at character 168, but expected: ["(", ")", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT","COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT","LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD","NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT",
> "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM","UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>,<IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
> at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9566)
> at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9443)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
> The fix involves two changes:
> (1) jjt grammar change
> (2) OpenJPA performs a preliminary validation to ensure the expression in the having clause is included in the group-by list. However, this validation checking should be done only on the LHS of the having clause (see expr1 below), not on the RHS (see expr2 below). For example:
> Having expr1 = expr2
> Only expr1 should be in the group-by list
> The current visitor pattern can not tell which node to visit without massive change. The alternative is to disable the checking by OpenJPA and let the backend to determine whether the generated SQL is valid or not.
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-1814) JPQL fails with Group By and Having
aggregate_expression IN (subquery)
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1814?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-1814:
------------------------------
Attachment: OPENJPA-1814.patch
> JPQL fails with Group By and Having aggregate_expression IN (subquery)
> ----------------------------------------------------------------------
>
> Key: OPENJPA-1814
> URL: https://issues.apache.org/jira/browse/OPENJPA-1814
> Project: OpenJPA
> Issue Type: Bug
> Components: kernel
> Affects Versions: 2.1.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Attachments: OPENJPA-1814.patch
>
>
> The following JPQL fail:
> String jpql = "SELECT a.uuid from EntityA a WHERE a.name = 'test' " +
> "GROUP BY a.date1, a.uuid " +
> (1) "HAVING MAX(a.date1) IN (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> (2) "HAVING MAX(a.date1) = (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> org.apache.openjpa.persistence.ArgumentException: Encountered "MAX ( a .date1) IN" at character 168, but expected: ["(", ")", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT","COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT","LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD","NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT",
> "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM","UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>,<IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
> at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9566)
> at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9443)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
> The fix involves two changes:
> (1) jjt grammar change
> (2) OpenJPA performs a preliminary validation to ensure the expression in the having clause is included in the group-by list. However, this validation checking should be done only on the LHS of the having clause (see expr1 below), not on the RHS (see expr2 below). For example:
> Having expr1 = expr2
> Only expr1 should be in the group-by list
> The current visitor pattern can not tell which node to visit without massive change. The alternative is to disable the checking by OpenJPA and let the backend to determine whether the generated SQL is valid or not.
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-1814) JPQL fails with Group By and Having
aggregate_expression IN (subquery)
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1814?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Dick updated OPENJPA-1814:
----------------------------------
Fix Version/s: 1.2.3
> JPQL fails with Group By and Having aggregate_expression IN (subquery)
> ----------------------------------------------------------------------
>
> Key: OPENJPA-1814
> URL: https://issues.apache.org/jira/browse/OPENJPA-1814
> Project: OpenJPA
> Issue Type: Bug
> Components: kernel
> Affects Versions: 2.1.0
> Environment: Fix committed to trunk at revision #1002419.
> Reporter: Fay Wang
> Assignee: Catalina Wei
> Fix For: 1.2.3, 2.1.0
>
> Attachments: OPENJPA-1814-1.2.x.patch, OPENJPA-1814-1.patch, OPENJPA-1814-2.patch, OPENJPA-1814.patch
>
>
> The following JPQL fail:
> String jpql = "SELECT a.uuid from EntityA a WHERE a.name = 'test' " +
> "GROUP BY a.date1, a.uuid " +
> (1) "HAVING MAX(a.date1) IN (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> (2) "HAVING MAX(a.date1) = (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> org.apache.openjpa.persistence.ArgumentException: Encountered "MAX ( a .date1) IN" at character 168, but expected: ["(", ")", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT","COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT","LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD","NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT",
> "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM","UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>,<IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
> at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9566)
> at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9443)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
> The fix involves two changes:
> (1) jjt grammar change
> (2) OpenJPA performs a preliminary validation to ensure the expression in the having clause is included in the group-by list. However, this validation checking should be done only on the LHS of the having clause (see expr1 below), not on the RHS (see expr2 below). For example:
> Having expr1 = expr2
> Only expr1 should be in the group-by list
> The current visitor pattern can not tell which node to visit without massive change. The alternative is to disable the checking by OpenJPA and let the backend to determine whether the generated SQL is valid or not.
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-1814) JPQL fails with Group By and Having
aggregate_expression IN (subquery)
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1814?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-1814:
------------------------------
Attachment: OPENJPA-1814-1.patch
clean up the patch
> JPQL fails with Group By and Having aggregate_expression IN (subquery)
> ----------------------------------------------------------------------
>
> Key: OPENJPA-1814
> URL: https://issues.apache.org/jira/browse/OPENJPA-1814
> Project: OpenJPA
> Issue Type: Bug
> Components: kernel
> Affects Versions: 2.1.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Attachments: OPENJPA-1814-1.patch, OPENJPA-1814.patch
>
>
> The following JPQL fail:
> String jpql = "SELECT a.uuid from EntityA a WHERE a.name = 'test' " +
> "GROUP BY a.date1, a.uuid " +
> (1) "HAVING MAX(a.date1) IN (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> (2) "HAVING MAX(a.date1) = (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> org.apache.openjpa.persistence.ArgumentException: Encountered "MAX ( a .date1) IN" at character 168, but expected: ["(", ")", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT","COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT","LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD","NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT",
> "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM","UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>,<IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
> at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9566)
> at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9443)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
> The fix involves two changes:
> (1) jjt grammar change
> (2) OpenJPA performs a preliminary validation to ensure the expression in the having clause is included in the group-by list. However, this validation checking should be done only on the LHS of the having clause (see expr1 below), not on the RHS (see expr2 below). For example:
> Having expr1 = expr2
> Only expr1 should be in the group-by list
> The current visitor pattern can not tell which node to visit without massive change. The alternative is to disable the checking by OpenJPA and let the backend to determine whether the generated SQL is valid or not.
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-1814) JPQL fails with Group By and Having
aggregate_expression IN (subquery)
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1814?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Catalina Wei updated OPENJPA-1814:
----------------------------------
Attachment: OPENJPA-1814-2.patch
In JPA Spec, aggregate expression is not allowed in the IN expression. The EBNF In Section 2.4.9 of JPA spec:
in_expression ::=
{state_field_path_expression | type_discriminator} [NOT] IN
{ ( in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter }
Therefore, the following JPQL results in syntax error:
SELECT c5.uuid
FROM pcs.common.fleet.CarLocationMessage c5
WHERE c5.railCarNumber = UPPER(:carNumber) and c5.clmArchived = false
GROUP BY c5.sightDate, c5.uuid
HAVING MAX(c5.sightDate) IN (SELECT MAX(c6.sightDate) from pcs.common.fleet.CarLocationMessage c6 WHERE c6.railCarNumber = UPPER(:carNumber) and c6.clmArchived = false)
The above JPQL can be rewritten to use EQUAL comparison as below:
SELECT c5.uuid
FROM pcs.common.fleet.CarLocationMessage c5
WHERE c5.railCarNumber = UPPER(:carNumber) and c5.clmArchived = false
GROUP BY c5.sightDate, c5.uuid
HAVING MAX(c5.sightDate) = (SELECT MAX(c6.sightDate) from pcs.common.fleet.CarLocationMessage c6 WHERE c6.railCarNumber = UPPER(:carNumber) and c6.clmArchived = false)
However, it still requires OPENJPA-1814-2.patch to overcome another problem in OpenJPA.
We have a rather restrictive rules for validating having/grouping clause, the patch is to avoid having/grouping validation if conditional expression involving a subquery.
In such cases, the having/grouping rules will be enforced by the backend DBMSs. Any violation would result in SQLException and would be considered as an user error.
> JPQL fails with Group By and Having aggregate_expression IN (subquery)
> ----------------------------------------------------------------------
>
> Key: OPENJPA-1814
> URL: https://issues.apache.org/jira/browse/OPENJPA-1814
> Project: OpenJPA
> Issue Type: Bug
> Components: kernel
> Affects Versions: 2.1.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Attachments: OPENJPA-1814-1.patch, OPENJPA-1814-2.patch, OPENJPA-1814.patch
>
>
> The following JPQL fail:
> String jpql = "SELECT a.uuid from EntityA a WHERE a.name = 'test' " +
> "GROUP BY a.date1, a.uuid " +
> (1) "HAVING MAX(a.date1) IN (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> (2) "HAVING MAX(a.date1) = (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
> org.apache.openjpa.persistence.ArgumentException: Encountered "MAX ( a .date1) IN" at character 168, but expected: ["(", ")", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT","COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT","LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD","NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT",
> "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM","UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>,<IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
> at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9566)
> at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9443)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)
> The fix involves two changes:
> (1) jjt grammar change
> (2) OpenJPA performs a preliminary validation to ensure the expression in the having clause is included in the group-by list. However, this validation checking should be done only on the LHS of the having clause (see expr1 below), not on the RHS (see expr2 below). For example:
> Having expr1 = expr2
> Only expr1 should be in the group-by list
> The current visitor pattern can not tell which node to visit without massive change. The alternative is to disable the checking by OpenJPA and let the backend to determine whether the generated SQL is valid or not.
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.