[DDC-2836] DQL errors when attempting to use GROUP BY MAX(field) Created: 26/Mar/13  Updated: 03/Dec/13  Resolved: 03/Dec/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: 2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Jon Langevin Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: dql, groupby, max


Attempting to run DQL similar to:
SELECT a FROM ClassName a GROUP BY MAX(a.depth)

Throws error:
[Semantical Error] line 0, col 250 near 'MAX(ao.depth)': Error: Cannot group by undefined identification or result variable.

Per docs, MAX is allowed within GROUP BY: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#aggregate-functions

If this error is due to some omission on my part, then perhaps the docs should be extended to show a valid GROUP BY MAX() usage, or the error message expanded for a better hint.

Comment by Marco Pivetta [ 03/Dec/13 ]

Grouping by MAX() is not supported, as MAX() is already an aggregation

Comment by Jon Langevin [ 03/Dec/13 ]

Shame, because that is useful functionality in SQL. So I assume this means the documentation will be correct then?

Comment by Marco Pivetta [ 03/Dec/13 ]

Jon Langevin did you check if the EBNF allows that?

Comment by Jon Langevin [ 03/Dec/13 ]

Per the doc link in my initial report, the docs say that the aggregation functions are allowed in both SELECT and GROUP BY clauses.

The following aggregate functions are allowed in SELECT and GROUP BY clauses: AVG, COUNT, MIN, MAX, SUM

Searching EBNF in same page, one of items supported for GROUP BY is:

/* ResultVariable identifier usage of mapped field aliases (the "total" of "COUNT AS total") */
ResultVariable = identifier

So I assume that means we'd have to move AVG, COUNT, MIN, MAX, SUM into the SELECT statement, using SELECT <function> AS <var>, and then GROUP BY <var> ?

Comment by Marco Pivetta [ 03/Dec/13 ]

Jon Langevin I think the syntax allows that, but you should give it a try. I don't see how that is supposed to work given how GROUP BY works in various RDBMS implementations

Generated at Wed Nov 25 01:33:09 EST 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.