Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2836

DQL errors when attempting to use GROUP BY MAX(field)

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: 2.3
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Labels:

      Description

      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.

        Activity

        Jon Langevin created issue -
        Hide
        Marco Pivetta added a comment -

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

        Show
        Marco Pivetta added a comment - Grouping by MAX() is not supported, as MAX() is already an aggregation
        Marco Pivetta made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Invalid [ 6 ]
        Marco Pivetta made changes -
        Project Doctrine Common [ 10043 ] Doctrine 2 - ORM [ 10032 ]
        Key DCOM-185 DDC-2836
        Affects Version/s 2.3 [ 10185 ]
        Affects Version/s 2.3 [ 10183 ]
        Security All [ 10000 ]
        Hide
        Jon Langevin added a comment -

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

        Show
        Jon Langevin added a comment - Shame, because that is useful functionality in SQL. So I assume this means the documentation will be correct then?
        Hide
        Marco Pivetta added a comment -

        Jon Langevin did you check if the EBNF allows that?

        Show
        Marco Pivetta added a comment - Jon Langevin did you check if the EBNF allows that?
        Hide
        Jon Langevin added a comment - - edited

        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> ?

        Show
        Jon Langevin added a comment - - edited 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> ?
        Hide
        Marco Pivetta added a comment -

        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

        Show
        Marco Pivetta added a comment - 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

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-2836, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Jon Langevin
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: