[NamedQuery/JPQL/Validation] - No Aggregatfunction possible within a SELECT

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[NamedQuery/JPQL/Validation] - No Aggregatfunction possible within a SELECT

Stefan Jodl
Hi @all,

The problem with my namedQuery is that I got several 'validation' errors.
But I can run the code (as a named Query as well as a separated test) and get the results as expected.
For me it looks like an validation error inside the dali/eclipselink/eclipse.

What I want?
Do find out with your help if,
- (and the easiestone would be) it's simply my fault.
- It's a bug

What did I found out?
- It occurs in compination when the aggregatefunction (SUM or COUNT) is in a Select Statement

What envirometal do I have?
- Eclipse Neon.1a Release (4.6.1) && Build id: 20161007-1200
- [EclipseLink] org.eclipse.persistence.jpa Version:2.6.4

What did I NOT check?
- Check with hibernate or other equivalent frameworks
- Other Aggregatfunctions


The code wich caused that trouble is long and you would need to fully go into it, therefore I encapsulated my problem to an simply reproduce-able but nonsense query. The full query is on the very end of this post.

Query-Code (SIMPLYFIED):
-----------------------------------------------------------------------
Code:
...
@Entity
@NamedQueries({
    @NamedQuery(name = "Product.test", query ="SELECT p.id, SUM(1+1) FROM Product p GROUP BY p.id")
})
...

-----------------------------------------------------------------------

Error Tooltip:
###############################################
Multiple markers at this line
- The encapsulated expression is not a valid expression.
- The right parenthesis is missing from the SUM
expression.
- The query contains a malformed ending.
- The state field path 'p.id' cannot be resolved to a valid
type.
- The identification variable 'p' is not defined in the
FROM clause.
- A select statement must have a FROM clause.
###############################################



Entity:
----------------------------------------------------------------------
Code:
...
public class Product implements Serializable, EWMSEntity {
        private static final long serialVersionUID = 1L;

        protected Product() {
        }

        public Product(long id, String name, String manufacturer) {
                this.id = id;
                this.name = name;
                this.manufacturer = manufacturer;
        }

        @Id
        private Long id;

        private String manufacturer;

        private String name;
...

-----------------------------------------------


Query-Code (ORIGINAL):
-----------------------------------------------------------------------
Code:
...
@Entity
@NamedQueries({
        @NamedQuery(name = "Product.getStock", query ="SELECT p.id, p.name, "
    + "SUM(CASE WHEN m.movementType = 'in' THEN 1 ELSE -1 END * b.quantity) as stock "
    + "FROM Product p "
    + "INNER JOIN p.stockUnits s "
    + "INNER JOIN s.allocations a "
    + "INNER JOIN a.bookings b "
    + "INNER JOIN b.movement m "
    + "GROUP BY p.id")
})
...

-----------------------------------------------------------------------
Loading...