Description Adrian Goerler 2010-10-06 10:14:31 EDT

The test JUnitJPQLComplexAggregateTestSuite.complexCountOnJoinedVariableOverManyToManySelfRefRelationship
executes the query 

SELECT COUNT(cc) FROM Customer c LEFT JOIN c.CCustomers cc GROUP BY c.name order by c.name

JPA 2.0 specifies the following:

The syntax of the ORDER BY clause is

orderby_clause ::= ORDER BY orderby_item {, orderby_item}*
orderby_item ::= { state_field_path_expression | result_variable } [ASC | DESC]

An orderby_item must be one of the following:
1. A state_field_path_expression that evaluates to an orderable state field of an entity or
embeddable class abstract schema type designated in the SELECT clause by one of the following:
• a general_identification_variable
• a single_valued_object_path_expression
2. A state_field_path_expression that evaluates to the same state field of the same entity or
embeddable abstract schema type as a state_field_path_expression in the SELECT clause
3. A result_variable that refers to an orderable item in the SELECT clause for which the same
result_variable has been specified. This may be the result of an aggregate_expression, a
scalar_expression, or a state_field_path_expression in the SELECT clause.

For c.name, non of the above cases applies. Hence, the query is beyond the specification.

On MaxDB, the query is mapped to

SELECT COUNT(t0.CUST_ID) FROM CMP3_CUSTOMER t1 LEFT OUTER JOIN (CMP3_CUSTOMER_CUSTOMER t2 JOIN CMP3_CUSTOMER t0 ON (t0.CUST_ID = t2.CCustomers_CUST_ID)) ON (t2.Customer_CUST_ID = t1.CUST_ID) GROUP BY t1.NAME ORDER BY t1.NAME ASC

This query is rejected by the MaxDB with the exception "Order column must be output column".

To fix the issue, I suggest to add the grouping column "c.name" to the select clause.