DDC-2622: Paginator with ORDER BY not working in MSSQL

[doctrinebot]

Jira issue originally created by user flip101:

PHP code to test (A symfony 2.3 controller):

<?php
public function testAction() {
    $em = $this->getDoctrine()->getManager();
    $query = $em->createQuery("
        SELECT report, user
        FROM Report:Report report

        JOIN report.user user

        WHERE user.id = ?1
        ORDER BY report.created DESC
    ");
    $query->setMaxResults(10);
    $query->setParameter(1, 1);
    $results = new Paginator($query, $fetchJoinCollection = true);
    foreach ($results as $result) {}; // This was needed to trigger the query o_O

    return new Response();
}

Schema:
One User to Many Reports

SQL + ERROR:

An exception occurred while executing '
SELECT *
FROM (
    SELECT DISTINCT id0
        ,ROW_NUMBER() OVER (
            ORDER BY r0_.aangemaakt DESC
            ) AS doctrine_rownum
    FROM (
        SELECT r0_.id AS id0
            ,r0_.Naam AS Naam1
            ,r0_.Omschrijving AS Omschrijving2
            ,r0_.aangemaakt AS aangemaakt3
            ,r0_.gewijzigd AS gewijzigd4
            ,r0_.verwijderd AS verwijderd5
            ,g1_.username AS username6
            ,g1*.username_canonical AS username*canonical7
            ,g1_.email AS email8
            ,g1*.email_canonical AS email*canonical9
            ,g1_.enabled AS enabled10
            ,g1_.salt AS salt11
            ,g1_.password AS password12
            ,g1*.last_login AS last*login13
            ,g1_.locked AS locked14
            ,g1_.expired AS expired15
            ,g1*.expires_at AS expires*at16
            ,g1*.confirmation_token AS confirmation*token17
            ,g1*.password_requested_at AS password_requested*at18
            ,g1_.roles AS roles19
            ,g1*.credentials_expired AS credentials*expired20
            ,g1*.credentials_expire_at AS credentials_expire*at21
            ,g1_.id AS id22
        FROM Rapporten r0_ WITH (NOLOCK)
        INNER JOIN Gebruiker g1* ON r0_.GebruikerId = g1*.id
        WHERE (g1_.id = ?)
            AND (r0_.verwijderd IS NULL)
        ) dctrn_result
    ) AS doctrine_tbl
WHERE doctrine_rownum BETWEEN 1
        AND 10
' with params [1]:

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The multi-part identifier "r0_.aangemaakt" could not be bound. 

FIX:
Change

ORDER BY r0_.aangemaakt DESC
to
ORDER BY aangemaakt3 DESC

[doctrinebot]

Comment created by flip101:

I didn't get the Paginator working yet, but as i understand this is the first of 3 (maybe 2) queries, as described here: http://docs.doctrine-project.org/en/latest/tutorials/pagination.html It seems that in this first query it's not necessary to SELECT all these columns, so there is an opportunity here for a performance boost when not selecting them. (They still have to be selected in the final query to get the results).

[doctrinebot]

Comment created by @Ocramius:

Just a note: the DQL query you're doing here is very dangerous hydration-wise. Don't ever filter on fetch-joined results.

[doctrinebot]

Comment created by @Ocramius:

Provided patches at #789 and doctrine/dbal#371

[doctrinebot]

Comment created by flip101:

I don't understand your comment about filtering on fetch-join results being dangerous for hydration, could you please elaborate?

[doctrinebot]

Comment created by @Ocramius:

[~flip101] it's unrelated to this change. I'd just explain that on IRC to avoid cluttering the issue here.

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-371] was closed:
doctrine/dbal#371

[doctrinebot]

Comment created by flip101:

Can be closed in favor of http://www.doctrine-project.org/jira/browse/[DDC-2687](http://www.doctrine-project.org/jira/browse/DDC-2687)

[doctrinebot]

Comment created by @Ocramius:

Handled in DDC-2687

[doctrinebot]

Issue was closed with resolution "Duplicate"

[doctrinebot]

Comment created by @doctrinebot:

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