DDC-2687: Paginator with ORDER BY not working in MSSQL #3369

New Issue

Open

opened 2026-01-22 14:18:41 +01:00 by admin · 0 comments

admin commented 2026-01-22 14:18:41 +01:00

Owner

Copy Link

Originally created by @doctrinebot on GitHub (Sep 18, 2013).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user flip101:

This bug report is similar to this one:
http://www.doctrine-project.org/jira/browse/DDC-2622

It's decided to make a new bug report to keep things cleaner (commits have already been done on the other report).

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);
    $paginator = new Paginator($query, $fetchJoinCollection = true);
    $result = $paginator->getIterator(); // Trigger querying, using paginator
    //$result = $query->getResult();

    return [
        'var' => $result
    ];
}
// returning the result to a template debug function

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

sqlORDER BY r0_.aangemaakt DESC
to
sqlORDER BY aangemaakt3 DESC

This fix should only be applied in case of the Paginator scenario, which looks like this:

    $paginator = new Paginator($query, $fetchJoinCollection = true);
    $result = $paginator->getIterator(); // Trigger querying, using paginator
    //$result = $query->getResult();

When getting a normal result, like this:

    //$paginator = new Paginator($query, $fetchJoinCollection = true);
    //$result = $paginator->getIterator(); // Trigger querying, using paginator
    $result = $query->getResult();

it works fine without any problems.

The "normal" result should keep working correctly!

The SQL produced by the non-paginator result:

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
        ,r0_.GebruikerId AS GebruikerId23
        ,g1*.group_id AS group*id24
        ,ROW_NUMBER() OVER (
            ORDER BY r0_.aangemaakt DESC
            ) AS doctrine_rownum
    FROM Rapporten r0_ WITH (NOLOCK)
    INNER JOIN Gebruiker g1* ON r0_.GebruikerId = g1*.id
    WHERE (g1_.id = ?)
        AND (r0_.verwijderd IS NULL)
    ) AS doctrine_tbl
WHERE doctrine_rownum BETWEEN 1
        AND 10

Notice sqlORDER BY r0_.aangemaakt DESC this is correct!

The difference is that the Paginator wraps the query in in a query with distinct, this is done here:
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L160

So far i know two different solutions.

Detect the wrapping query in SQLServerPlatform::doModifyLimitQuery with regex. See relevant code snippet below

if (preg*match('/SELECT DISTINCT .* FROM \(.*\) dctrn*result/', $query)) {
        $isWrapped = true;
} else {
        $isWrapped = false;
}

//Find alias for each colum used in ORDER BY
if ( ! empty($orderbyColumns)) {
        foreach ($orderbyColumns as $column) {

                if ($isWrapped) {
                        $pattern    = sprintf('/%s\.%s\s</ins>(AS\s<ins>)?([<sup>,\s\)]</ins>)/i', $column['table'], $column['column']);
                        $overColumn = preg_match($pattern, $query, $matches) ? $matches[2] : $column['column'];
                } else {
                        $pattern    = sprintf('/%s\.(%s)\s**(AS)?\s*([</sup>,\s\)]**)/i', $column['table'], $column['column']);
                        $overColumn = preg_match($pattern, $query, $matches)
                                ? ($column['hasTable'] ? $column['table']  . '.' : '') . $column['column'] 
                                : $column['column'];
                }

                if (isset($column['sort'])) {
                        $overColumn .= ' ' . $column['sort'];
                }

                $overColumns[] = $overColumn;
        }
}

This code would replace:
https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php#L856-L870
And takes two lines of codes from this commit:
5d7bcb6637/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php (L860-L861)

Another solution is that the Paginator should be responsible for notifying the SQLServerPlatform::doModifyLimitQuery method if the query is wrapped or not. In that case, this line of code
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L171
should be replaced by

$sql, $this->maxResults, $this->firstResult, true

And this line of code:
https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php#L818
should be replaced by

protected function doModifyLimitQuery($query, $limit, $offset = null, $isWrapped = false)

Of course parts of the code from the previous solution are still needed. But the $isWrapped detection is not done anymore by regex this way.

Originally created by @doctrinebot on GitHub (Sep 18, 2013). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user flip101: This bug report is similar to this one: http://www.doctrine-project.org/jira/browse/[DDC-2622](http://www.doctrine-project.org/jira/browse/DDC-2622) It's decided to make a new bug report to keep things cleaner (commits have already been done on the other report). PHP code to test (A symfony 2.3 controller): ``` php <?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); $paginator = new Paginator($query, $fetchJoinCollection = true); $result = $paginator->getIterator(); // Trigger querying, using paginator //$result = $query->getResult(); return [ 'var' => $result ]; } // returning the result to a template debug function ``` Schema: One User to Many Reports SQL <ins> ERROR: ``` sql 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 `sqlORDER BY r0_.aangemaakt DESC` to `sqlORDER BY aangemaakt3 DESC` This fix should only be applied in case of the Paginator scenario, which looks like this: ``` php $paginator = new Paginator($query, $fetchJoinCollection = true); $result = $paginator->getIterator(); // Trigger querying, using paginator //$result = $query->getResult(); ``` When getting a normal result, like this: ``` php //$paginator = new Paginator($query, $fetchJoinCollection = true); //$result = $paginator->getIterator(); // Trigger querying, using paginator $result = $query->getResult(); ``` it works fine without any problems. The "normal" result should keep working correctly! The SQL produced by the non-paginator result: ``` sql 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 ,r0_.GebruikerId AS GebruikerId23 ,g1*.group_id AS group*id24 ,ROW_NUMBER() OVER ( ORDER BY r0_.aangemaakt DESC ) AS doctrine_rownum FROM Rapporten r0_ WITH (NOLOCK) INNER JOIN Gebruiker g1* ON r0_.GebruikerId = g1*.id WHERE (g1_.id = ?) AND (r0_.verwijderd IS NULL) ) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 10 ``` Notice `sqlORDER BY r0_.aangemaakt DESC` this is correct! The difference is that the Paginator wraps the query in in a query with distinct, this is done here: https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L160 So far i know two different solutions. 1. Detect the wrapping query in SQLServerPlatform::doModifyLimitQuery with regex. See relevant code snippet below ``` php if (preg*match('/SELECT DISTINCT .* FROM \(.*\) dctrn*result/', $query)) { $isWrapped = true; } else { $isWrapped = false; } //Find alias for each colum used in ORDER BY if ( ! empty($orderbyColumns)) { foreach ($orderbyColumns as $column) { if ($isWrapped) { $pattern = sprintf('/%s\.%s\s</ins>(AS\s<ins>)?([<sup>,\s\)]</ins>)/i', $column['table'], $column['column']); $overColumn = preg_match($pattern, $query, $matches) ? $matches[2] : $column['column']; } else { $pattern = sprintf('/%s\.(%s)\s**(AS)?\s*([</sup>,\s\)]**)/i', $column['table'], $column['column']); $overColumn = preg_match($pattern, $query, $matches) ? ($column['hasTable'] ? $column['table'] . '.' : '') . $column['column'] : $column['column']; } if (isset($column['sort'])) { $overColumn .= ' ' . $column['sort']; } $overColumns[] = $overColumn; } } ``` This code would replace: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php#L856-L870 And takes two lines of codes from this commit: https://github.com/doctrine/dbal/blob/5d7bcb6637646eb1daf6d90827233f5562cbda29/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php#L860-L861 Another solution is that the Paginator should be responsible for notifying the SQLServerPlatform::doModifyLimitQuery method if the query is wrapped or not. In that case, this line of code https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L171 should be replaced by ``` php $sql, $this->maxResults, $this->firstResult, true ``` And this line of code: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php#L818 should be replaced by ``` php protected function doModifyLimitQuery($query, $limit, $offset = null, $isWrapped = false) ``` Of course parts of the code from the previous solution are still needed. But the $isWrapped detection is not done anymore by regex this way.

admin added the Bug label 2026-01-22 14:18:41 +01:00

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

3.6.x

2.20.x

dependabot/github_actions/2.20.x/doctrine/dot-github/dot-github/workflows/composer-lint.yml-14.0.0

3.7.x

4.0.x

2.21.x

old-prototype-3.x

3.6.2

3.6.1

3.6.0

2.20.9

3.5.8

3.5.7

3.5.6

3.5.5

2.20.8

3.5.4

3.5.3

2.20.7

3.5.2

2.20.6

3.5.1

3.5.0

3.4.4

3.4.3

3.4.2

2.20.5

3.4.1

3.4.0

3.3.4

2.20.4

3.3.3

2.20.3

3.3.2

2.20.2

3.3.1

2.20.1

3.3.0

3.2.3

2.20.0

2.19.8

3.2.2

2.19.7

3.2.1

2.19.6

3.2.0

3.1.4

3.1.3

2.19.5

3.1.2

2.19.4

3.1.1

2.19.3

2.19.2

2.19.1

3.1.0

2.19.0

3.0.3

2.18.3

3.0.2

2.18.2

3.0.1

2.18.1

3.0.0

2.18.0

2.17.5

3.0.0-RC1

2.17.4

2.17.3

2.17.2

2.17.1

3.0.0-beta2

2.17.0

2.16.3

3.0.0-beta1

2.16.2

2.16.1

2.16.0

2.15.5

2.15.4

2.15.3

2.15.2

2.15.1

2.15.0

2.14.3

2.14.2

2.14.1

2.14.0

2.13.5

2.13.4

2.13.3

2.13.2

2.13.1

2.13.0

2.12.4

2.12.3

2.12.2

2.12.1

2.12.0

2.11.3

2.11.2

2.11.1

2.11.0

2.10.5

2.10.4

2.10.3

2.10.2

2.10.1

2.10.0

2.9.6

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.8.5

2.8.4

2.8.3

2.8.2

2.8.1

2.8.0

2.7.5

2.7.4

v2.7.3

v2.7.2

v2.7.1

v2.7.0

v2.6.6

v2.6.5

v2.6.4

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.14

v2.5.13

v2.5.12

v2.5.11

v2.5.10

v2.5.9

v2.5.8

v2.5.7

v2.5.6

v2.5.5

v2.5.4

v2.5.3

v2.5.2

v2.4.8

v2.5.1

v2.5.0

v2.5.0-RC2

v2.5.0-RC1

v2.5.0-beta1

v2.5.0-alpha2

v2.5.0-alpha1

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.3.6

v2.4.2

2.3.5

v2.4.1

v2.4.0

2.4.0-RC2

2.4.0-RC1

2.4.0-BETA2

2.3.4

2.3.3

2.4.0-BETA1

2.3.2

2.3.1

2.3.0

2.3.0-RC4

2.3.0-RC3

2.3.0-RC2

2.2.3

2.3.0-RC1

2.3.0-BETA1

2.1.7

2.2.2

2.2.1

2.1.6

2.2.0

2.2.0-RC1

2.2.0-BETA2

2.2.0-BETA1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.0.7

2.1.0

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.6

2.1.0BETA1

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC2

2.0.0RC1

2.0.0-BETA4

2.0.0-BETA3

2.0.0-BETA2

2.0.0-BETA1

Labels

Clear labels

BC Break

Bug

Can't Fix

Deprecation

Documentation

DQL

Duplicate

Failing Test

Hacktoberfest

Improvement

Improvement

Incomplete

Invalid

Missing Tests

New Feature

PR Created

pull-request

Mirrored from GitHub Pull Request

Question

Regression

Status: Waiting feedback

Waiting feedback

WIP

Won't Fix

No Label Bug

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

admin

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#3369