Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-788

ORDER BY with function COUNT() fails

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Platforms
    • Security Level: All
    • Labels:
      None
    • Environment:
      mssql 2008 R2

      Description

      This:
      ORDER BY ad.name ASC, count(filter.value) DESC

      Fails with:
      Error: Expected end of string, got '('

        Activity

        Hide
        M.K. added a comment -

        I have the same problem using SUM() in ORDER BY. I think the doctrine documentation says, that you have to use an alias in ORDER BY. This works fine with MySQL, but fails in MSSQL, because MSSQL doesn't allow aliases in ORDER BY.
        I think using aliases in DQL should be fine, so it's rather a problem in SQLServerPlatform class. Aggregate functions in ORDER BY are pretty basic stuff.

        Issue should be moved to DBAL.

        Show
        M.K. added a comment - I have the same problem using SUM() in ORDER BY. I think the doctrine documentation says, that you have to use an alias in ORDER BY. This works fine with MySQL, but fails in MSSQL, because MSSQL doesn't allow aliases in ORDER BY. I think using aliases in DQL should be fine, so it's rather a problem in SQLServerPlatform class. Aggregate functions in ORDER BY are pretty basic stuff. Issue should be moved to DBAL.
        Hide
        Steve Müller added a comment -

        Is using aggregate functions in ORDER BY even possible in SQL Server? It's not clear from the documentation. However it looks like ORDER BY SQL generation might have to be delegated to the specific platform just like LIMIT/OFFSET clauses. This would be another big mess for SQL Server

        Show
        Steve Müller added a comment - Is using aggregate functions in ORDER BY even possible in SQL Server? It's not clear from the documentation. However it looks like ORDER BY SQL generation might have to be delegated to the specific platform just like LIMIT/OFFSET clauses. This would be another big mess for SQL Server
        Hide
        M.K. added a comment - - edited

        I use MSSQL 2008 R2 as well and it seems MSSQL is fine with using aliases in ORDER BY after all. If i run a DQL-Query like that:

        SELECT t.id, SUM(pos.price) AS amount
        FROM Project\Entity\Task t
        LEFT JOIN Project\Entity\Position pos WITH t.id = pos.tid
        GROUP BY t.id
        ORDER BY amount ASC
        

        ... everything is fine. SQL looks like this:

        SELECT t0_.id AS id0, SUM(p0_.price) AS sclr1
        FROM task t0_ WITH (NOLOCK)
        LEFT JOIN position p0_ ON (t0_.id = p0_.tid)
        GROUP BY t0_.id
        ORDER BY sclr1 ASC
        

        MSSQL seems to be okay with the alias. But if apply a limit on the DQL-Query, SQL looks like this:

        SELECT *
        FROM (
        	SELECT t0_.id AS id0, SUM(p0_.price) AS sclr1, ROW_NUMBER() OVER (ORDER BY sclr1 ASC) AS doctrine_rownum
        	FROM task t0_ WITH (NOLOCK)
        	LEFT JOIN position p0_ ON (t0_.id = p0_.tid)
        	GROUP BY p0_.id
        ) AS doctrine_tbl
        WHERE doctrine_rownum BETWEEN 1 AND 50
        

        Execution fails with Error: "Invalid column name 'sclr1'"

        I'm not really familiar with MSSQL which is why i decided to use Doctrine after all. But i hope this helps.

        Show
        M.K. added a comment - - edited I use MSSQL 2008 R2 as well and it seems MSSQL is fine with using aliases in ORDER BY after all. If i run a DQL-Query like that: SELECT t.id, SUM(pos.price) AS amount FROM Project\Entity\Task t LEFT JOIN Project\Entity\Position pos WITH t.id = pos.tid GROUP BY t.id ORDER BY amount ASC ... everything is fine. SQL looks like this: SELECT t0_.id AS id0, SUM(p0_.price) AS sclr1 FROM task t0_ WITH (NOLOCK) LEFT JOIN position p0_ ON (t0_.id = p0_.tid) GROUP BY t0_.id ORDER BY sclr1 ASC MSSQL seems to be okay with the alias. But if apply a limit on the DQL-Query, SQL looks like this: SELECT * FROM ( SELECT t0_.id AS id0, SUM(p0_.price) AS sclr1, ROW_NUMBER() OVER (ORDER BY sclr1 ASC) AS doctrine_rownum FROM task t0_ WITH (NOLOCK) LEFT JOIN position p0_ ON (t0_.id = p0_.tid) GROUP BY p0_.id ) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 50 Execution fails with Error: "Invalid column name 'sclr1'" I'm not really familiar with MSSQL which is why i decided to use Doctrine after all. But i hope this helps.
        Hide
        Steve Müller added a comment -

        M.K. Thanks for the detailed information. The fact that you are using a limit here was missing. SQL Server does not support referring to expressions or column aliases from the select list in OVER() clause.
        See here: http://msdn.microsoft.com/en-us/library/ms189461.aspx
        Could you please test if it possible to specify the SUM() expression directly in the OVER() clause? Like the following:

        SELECT *
        FROM (
        	SELECT t0_.id AS id0, SUM(p0_.price) AS sclr1, ROW_NUMBER() OVER (ORDER BY SUM(p0_.price) ASC) AS doctrine_rownum
        	FROM task t0_ WITH (NOLOCK)
        	LEFT JOIN position p0_ ON (t0_.id = p0_.tid)
        	GROUP BY p0_.id
        ) AS doctrine_tbl
        WHERE doctrine_rownum BETWEEN 1 AND 50
        

        If that works we might be able to rewrite the SQLServerPlatform::modifyLimitQuery() to respect that. Otherwise I really don't know what to do about it.

        Show
        Steve Müller added a comment - M.K. Thanks for the detailed information. The fact that you are using a limit here was missing. SQL Server does not support referring to expressions or column aliases from the select list in OVER() clause. See here: http://msdn.microsoft.com/en-us/library/ms189461.aspx Could you please test if it possible to specify the SUM() expression directly in the OVER() clause? Like the following: SELECT * FROM ( SELECT t0_.id AS id0, SUM(p0_.price) AS sclr1, ROW_NUMBER() OVER (ORDER BY SUM(p0_.price) ASC) AS doctrine_rownum FROM task t0_ WITH (NOLOCK) LEFT JOIN position p0_ ON (t0_.id = p0_.tid) GROUP BY p0_.id ) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 50 If that works we might be able to rewrite the SQLServerPlatform::modifyLimitQuery() to respect that. Otherwise I really don't know what to do about it.
        Hide
        M.K. added a comment -

        Yup, using the SUM() expression in the OVER() clause works just fine.

        Show
        M.K. added a comment - Yup, using the SUM() expression in the OVER() clause works just fine.
        Hide
        Steve Müller added a comment -

        M.K. Thank you for investigating. I would like to move this ticket to DBAL because it is a DBAL issue. But I guess that makes two issues now because if I understand correctly, Flip did not use a limit/offset query modification but did not use an alias in the ORDER BY clause either but instead directly specified a COUNT() expression...

        Show
        Steve Müller added a comment - M.K. Thank you for investigating. I would like to move this ticket to DBAL because it is a DBAL issue. But I guess that makes two issues now because if I understand correctly, Flip did not use a limit/offset query modification but did not use an alias in the ORDER BY clause either but instead directly specified a COUNT() expression...
        Hide
        Flip added a comment -

        I can confirm it works when using an alias. Issue can be closed.

        Show
        Flip added a comment - I can confirm it works when using an alias. Issue can be closed.
        Hide
        M.K. added a comment -

        This issue refers to the same problem:
        http://www.doctrine-project.org/jira/browse/DBAL-834

        Show
        M.K. added a comment - This issue refers to the same problem: http://www.doctrine-project.org/jira/browse/DBAL-834

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Flip
          • Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated: