You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Andre (JIRA)" <ji...@apache.org> on 2010/10/19 11:21:27 UTC
[jira] Created: (OPENJPA-1843) error in subquery sum is null
error in subquery sum is null
-----------------------------
Key: OPENJPA-1843
URL: https://issues.apache.org/jira/browse/OPENJPA-1843
Project: OpenJPA
Issue Type: Bug
Components: query
Affects Versions: 2.0.1
Environment: Windows
Reporter: Andre
The following is my query
select id from InventoryDetail id
join id.inventory i join i.adjustment ad where
( ((select sum(cogsd.quantity) from id.costOfGoodsSoldDetails cogsd) is null) or
(select sum(cogsd.quantity) from id.costOfGoodsSoldDetails cogsd) < id.quantity)
and ad.organisation = :organisation
and id.item = :item
order by ad.transactionDate asc
and i get the following exception
Exception in thread "main" <openjpa-2.0.1-r422266:989424 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: "Encountered "select sum ( cogsd . quantity ) from id . costOfGoodsSoldDetails cogsd ) is" at character 86, but expected: ["(", ")", "*", "+", ",", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CASE", "COALESCE", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INDEX", "INNER", "IS", "JOIN", "KEY", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "NULLIF", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM", "TYPE", "UPDATE", "UPPER", "VALUE", "WHERE", <BOOLEAN_LITERAL>, <DATE_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>, <TIMESTAMP_LITERAL>, <TIME_LITERAL>]." while parsing JPQL "select id from InventoryDetail id join id.inventory i join i.adjustment ad where ( ((select sum(cogsd.quantity) from id.costOfGoodsSoldDetails cogsd) is null) or (select sum(cogsd.quantity) from id.costOfGoodsSoldDetails cogsd) < id.quantity) and ad.organisation = :organisation and id.item = :item order by ad.transactionDate asc". See nested stack trace for original parse error.
at org.apache.openjpa.kernel.jpql.JPQLParser.parse(JPQLParser.java:51)
at org.apache.openjpa.kernel.ExpressionStoreQuery.newCompilation(ExpressionStoreQuery.java:150)
at org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:670)
at org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:652)
at org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:618)
at org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:680)
at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:587)
at org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:986)
at org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:972)
at org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:100)
at id.web.pos.integra.utility.Tester.main(Tester.java:33)
Caused by: <openjpa-2.0.1-r422266:989424 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Encountered "select sum ( cogsd . quantity ) from id . costOfGoodsSoldDetails cogsd ) is" at character 86, but expected: ["(", ")", "*", "+", ",", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CASE", "COALESCE", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INDEX", "INNER", "IS", "JOIN", "KEY", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "NULLIF", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM", "TYPE", "UPDATE", "UPPER", "VALUE", "WHERE", <BOOLEAN_LITERAL>, <DATE_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>, <TIMESTAMP_LITERAL>, <TIME_LITERAL>].
at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:13087)
at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:12961)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1979)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1957)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1807)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1769)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1974)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1957)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1807)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1769)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1974)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1957)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1807)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1769)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1974)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1957)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1807)
at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1769)
at org.apache.openjpa.kernel.jpql.JPQL.where_clause(JPQL.java:1587)
at org.apache.openjpa.kernel.jpql.JPQL.select_statement(JPQL.java:91)
at org.apache.openjpa.kernel.jpql.JPQL.parseQuery(JPQL.java:63)
at org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.parse(JPQLExpressionBuilder.java:2371)
at org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.<init>(JPQLExpressionBuilder.java:2358)
at org.apache.openjpa.kernel.jpql.JPQLParser.parse(JPQLParser.java:49)
... 10 more
It worked on Hibernate, i tried replacing is null with '= 0' and its fine but dunno whether the result is correct or not. Because OpenJPA do not support writing to embbeddables i have to rewrite some of my code 1st in order to test.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-1843) error in subquery sum is null
Posted by "Andre (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1843?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12922956#action_12922956 ]
Andre commented on OPENJPA-1843:
--------------------------------
i found a workaround for this issue, by replacing [(select sum(cogsd.quantity) from id.costOfGoodsSoldDetails cogsd) is null] with [id.costOfGoodsSoldDetails is empty]
But I think it should be allowed
> error in subquery sum is null
> -----------------------------
>
> Key: OPENJPA-1843
> URL: https://issues.apache.org/jira/browse/OPENJPA-1843
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Affects Versions: 2.0.1
> Environment: Windows
> Reporter: Andre
>
> The following is my query
> select id from InventoryDetail id
> join id.inventory i join i.adjustment ad where
> ( ((select sum(cogsd.quantity) from id.costOfGoodsSoldDetails cogsd) is null) or
> (select sum(cogsd.quantity) from id.costOfGoodsSoldDetails cogsd) < id.quantity)
> and ad.organisation = :organisation
> and id.item = :item
> order by ad.transactionDate asc
> and i get the following exception
> Exception in thread "main" <openjpa-2.0.1-r422266:989424 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: "Encountered "select sum ( cogsd . quantity ) from id . costOfGoodsSoldDetails cogsd ) is" at character 86, but expected: ["(", ")", "*", "+", ",", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CASE", "COALESCE", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INDEX", "INNER", "IS", "JOIN", "KEY", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "NULLIF", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM", "TYPE", "UPDATE", "UPPER", "VALUE", "WHERE", <BOOLEAN_LITERAL>, <DATE_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>, <TIMESTAMP_LITERAL>, <TIME_LITERAL>]." while parsing JPQL "select id from InventoryDetail id join id.inventory i join i.adjustment ad where ( ((select sum(cogsd.quantity) from id.costOfGoodsSoldDetails cogsd) is null) or (select sum(cogsd.quantity) from id.costOfGoodsSoldDetails cogsd) < id.quantity) and ad.organisation = :organisation and id.item = :item order by ad.transactionDate asc". See nested stack trace for original parse error.
> at org.apache.openjpa.kernel.jpql.JPQLParser.parse(JPQLParser.java:51)
> at org.apache.openjpa.kernel.ExpressionStoreQuery.newCompilation(ExpressionStoreQuery.java:150)
> at org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:670)
> at org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:652)
> at org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:618)
> at org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:680)
> at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:587)
> at org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:986)
> at org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:972)
> at org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:100)
> at id.web.pos.integra.utility.Tester.main(Tester.java:33)
> Caused by: <openjpa-2.0.1-r422266:989424 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Encountered "select sum ( cogsd . quantity ) from id . costOfGoodsSoldDetails cogsd ) is" at character 86, but expected: ["(", ")", "*", "+", ",", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CASE", "COALESCE", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INDEX", "INNER", "IS", "JOIN", "KEY", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "NULLIF", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM", "TYPE", "UPDATE", "UPPER", "VALUE", "WHERE", <BOOLEAN_LITERAL>, <DATE_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>, <TIMESTAMP_LITERAL>, <TIME_LITERAL>].
> at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:13087)
> at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:12961)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1979)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1957)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1807)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1769)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1974)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1957)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1807)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1769)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1974)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1957)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1807)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1769)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1974)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1957)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1807)
> at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1769)
> at org.apache.openjpa.kernel.jpql.JPQL.where_clause(JPQL.java:1587)
> at org.apache.openjpa.kernel.jpql.JPQL.select_statement(JPQL.java:91)
> at org.apache.openjpa.kernel.jpql.JPQL.parseQuery(JPQL.java:63)
> at org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.parse(JPQLExpressionBuilder.java:2371)
> at org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.<init>(JPQLExpressionBuilder.java:2358)
> at org.apache.openjpa.kernel.jpql.JPQLParser.parse(JPQLParser.java:49)
> ... 10 more
> It worked on Hibernate, i tried replacing is null with '= 0' and its fine but dunno whether the result is correct or not. Because OpenJPA do not support writing to embbeddables i have to rewrite some of my code 1st in order to test.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.