DBAL-788: ORDER BY with function COUNT() fails #2018

Closed
doctrinebot opened this Issue Jan 8, 2014 · 12 comments

2 participants

@doctrinebot

Jira issue originally created by user flip101:

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

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

@doctrinebot

Comment created by mklocke:

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.

@doctrinebot

Comment created by @deeky666:

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 :D

@doctrinebot

Comment created by mklocke:

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.

@doctrinebot

Comment created by @deeky666:

[~mklocke] 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.

@doctrinebot

Comment created by mklocke:

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

@doctrinebot

Comment created by @deeky666:

[mklocke] 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, [flip101] 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...

@doctrinebot

Comment created by flip101:

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

@doctrinebot

Comment created by @doctrinebot:

A related Github Pull-Request [GH-573] was closed:
#573

@doctrinebot

Comment created by @deeky666:

Fixed in commit: 4a7ff71

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added the Bug label Dec 7, 2015
@deeky666 deeky666 was assigned by doctrinebot Dec 7, 2015
@doctrinebot doctrinebot added this to the 2.5 milestone Dec 7, 2015
@doctrinebot doctrinebot closed this Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment