|
[
Permlink
| « Hide
]
Andy Jefferson added a comment - 14/Dec/05 12:54 AM
Why ? Is it not allowed for the JDO implementation to interpret that fields in the result should be automatically added to the grouping clause ? All fields in the result have to be in the grouping if they are in the result, so its hardly rocket science for a JDO impl to add them itself.
my $0.02:
I think it is dangerous to automatically add the field to the grouping, because this changes the semantics of the query as specified by the user. How do you know that the user forgot to add the field to the grouping. Maybe the error is a missing aggregate for the result field: "SELECT department, AVG(salary) FROM ...". I would prefer throwing an error and let the user fix the query. Here's the specification:
When grouping is specified, each result expression must be one of: an expression contained in the grouping expression; or, an aggregate expression evaluated once per group. The query groups all elements where all expressions specified in setGrouping have the same values. The query result consists of one element per group. I think that automatically adding fields to the grouping clause is likely to change the expected result, and therefore won't be what the user expects. So I believe the spec is correct in requiring an exception to be thrown. OK, so lets take this one step further then. I look at "SingleStringQuery" test. It requires a query with a result clause, an ordering clause and a grouping clause. The grouping provided is consistent with the result clause, but the grouping clause doesnt include the field that is required for ordering. When we convert this into a SQL SELECT, using the policy of never adding anything that the user doesnt explicitly provide we would end up with a statement
SELECT a, b FROM t GROUP BY a, b ORDER BY c and Derby will promptly throw an error because "c" is not in the SELECT clause. The JDO impl could add the ordering column(s) to the SELECT clause, but then Derby will promptly throw an error because "c" is not in the GROUP BY clause. "For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions" So what do we do in this situation? The implementation is presumably allowed to add the ordering column(s) to the select, but isn't permitted to add it to the grouping ? I've added this issue to the JDO specification issues list as Issue 149.
Ordering should have the same restrictions as for the Select clause. That is, if grouping is used, only expressions in the Grouping clause and aggregate expressions can be in the Ordering clause. The JDO implementation is not permitted to modify the Select clause, the Grouping clause, or the Ordering clause. These are user-visible and should not be changed by the implementation. However, the JDO implementation is required to construct valid SQL if the query is being used with a relational datastore. This means that the SQL SELECT might need to have expressions added to the user's Select clause to include expressions in the Grouping and Ordering clauses. Today in the specification there are restrictions on the expressions that can be used in the Select clause if there is a Grouping clause: <spec 14.6.10> Only expressions in the Grouping clause and aggregate expressions can be in the Select clause. </spec 14.6.10> A similar restriction is needed for the Ordering clause. <proposed> Only expressions in the Grouping clause and aggregate expressions can be in the Ordering clause. </proposed> JPOX CVS changed to only use user-specified grouping and not to add on any required by ordering/selects. This means that org.apache.jdo.tck.query.jdoql.keywords.SingleString and org.apache.jdo.tck.query.api.SingleStringQuery fail due to ordering constraints that are not specified in the grouping.
Craig just checked in the remaining part of this issue and changed ordering to a grouped field in TCK query tests SingleStringQuery and SingleString.
|
||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Bug
Resolved
Major
JPOX must throw JDOUserException for group by queries which select non-grouped fields.
