Paginator's CountOutputWalker not remove ORDER BY for MSSQL platform #6671

New Issue

admin · 2026-01-22T15:36:47+01:00

admin commented

2026-01-22 15:36:47 +01:00

Originally created by @mleko64 on GitHub (Apr 1, 2021).

In lib/Doctrine/ORM/Tools/Pagination/CountOutputWalker.php file on line 89 is condition which prevents ORDER BY clause in sql query for MSSQL platform (prevents error like "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."):

if ($this->platform->getName() === 'mssql') {
    $AST->orderByClause = null;
}

but this is not enought.

After this condition, the parent method is called (on line 93):

$sql = parent::walkSelectStatement($AST);

and in lib/Doctrine/ORM/Query/SqlWalker.php file on line 559 is condition which checks if ORDER BY clause should be in sql query or not:

if ($AST->orderByClause) {
    $sql .= $this->walkOrderByClause($AST->orderByClause);
}

and that is OK, but below that, there is another condition (on line 563) where is a main problem:

$orderBySql = $this->generateOrderedCollectionOrderByItems();
if (! $AST->orderByClause && $orderBySql) {
    $sql .= ' ORDER BY ' . $orderBySql;
}

Although $AST->orderByClause is set to null, $orderBySql may be true and the ORDER BY clause will be added to the SQL query. So, this ORDER BY generates error from MSSQL server.
The error can be triggered by adding a join to another entity in the query, and the main entity has a field with @ORM\OrderBy annotation.
For example if entity "Product" has a field "$parameters" which is a relation (One-To-Many) to "Parameter" entity and this field has a annotation with @ORM\OrderBy and we create a query builder with join to this field "parameters" and we use this query builder to Paginator then ORDER BY clause will be applied and we will get a error from MSSQL database.

Originally created by @mleko64 on GitHub (Apr 1, 2021). In `lib/Doctrine/ORM/Tools/Pagination/CountOutputWalker.php` file on line 89 is condition which prevents ORDER BY clause in sql query for MSSQL platform (prevents error like "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."): ```php if ($this->platform->getName() === 'mssql') { $AST->orderByClause = null; } ``` but this is not enought. After this condition, the parent method is called (on line 93): ```php $sql = parent::walkSelectStatement($AST); ``` and in `lib/Doctrine/ORM/Query/SqlWalker.php` file on line 559 is condition which checks if ORDER BY clause should be in sql query or not: ```php if ($AST->orderByClause) { $sql .= $this->walkOrderByClause($AST->orderByClause); } ``` and that is OK, but below that, there is another condition (on line 563) where is a main problem: ```php $orderBySql = $this->generateOrderedCollectionOrderByItems(); if (! $AST->orderByClause && $orderBySql) { $sql .= ' ORDER BY ' . $orderBySql; } ``` Although `$AST->orderByClause` is set to `null`, `$orderBySql` may be true and the ORDER BY clause will be added to the SQL query. So, this ORDER BY generates error from MSSQL server. The error can be triggered by adding a join to another entity in the query, and the main entity has a field with `@ORM\OrderBy` annotation. For example if entity "Product" has a field "$parameters" which is a relation (One-To-Many) to "Parameter" entity and this field has a annotation with `@ORM\OrderBy` and we create a query builder with join to this field "parameters" and we use this query builder to Paginator then ORDER BY clause will be applied and we will get a error from MSSQL database.

admin commented

2026-01-22 15:36:48 +01:00

@Matt-PMCT commented on GitHub (Jul 5, 2025):

This continues to be a problem, which I'm going to summarize again since it has been a while and some things like the initial mssql check have changed.

Lines 58-60 appear to be an attempt to prevent this problem from occurring:

        if ($this->platform instanceof SQLServerPlatform) {
            $selectStatement->orderByClause = null;
        }

But in line 62 this command calls the SqlWalker.php file, at line 518-527 the orderby clause is skipped due to being null, and then immediately an ORDER BY is added to the query.


        if ($selectStatement->orderByClause) {
            $sql .= $this->walkOrderByClause($selectStatement->orderByClause);
        }

        $orderBySql = $this->generateOrderedCollectionOrderByItems();
        if (! $selectStatement->orderByClause && $orderBySql) {
            $sql .= ' ORDER BY ' . $orderBySql;
        }

Basically the CountOutputWalker attempts to not break the SQL server and then the SQL Walker breaks SQL server.

A short example of this problem is that I have a query that Doctrine generates that I is attempted to be used in a Paginator:

SELECT l0_.id AS id_0, l0_.date_time_from AS date_time_from_1, s1_.from_date_time AS from_date_time_14 
FROM logEntry l0_ 
LEFT JOIN subEntry s1_ ON l0_.id = s1_.log_entry_id 
WHERE l0_.log_entry_book_id IN (?) AND l0_.date_time_from IS NOT NULL 
ORDER BY s1_.from_date_time ASC

This query is passed to lib/Doctrine/ORM/Tools/Pagination/CountOutputWalker.php which on line 119 wraps the query to be inside of another query. This produces:

SELECT COUNT(*) AS dctrn_count 
FROM (
    SELECT DISTINCT id_0 
    FROM (
        SELECT l0_.id AS id_0, l0_.date_time_from AS date_time_from_1, s1_.from_date_time AS from_date_time_14 
        FROM logEntry l0_ 
        LEFT JOIN subEntry s1_ ON l0_.id = s1_.log_entry_id 
        WHERE l0_.log_entry_book_id IN (?) AND l0_.date_time_from IS NOT NULL 
        ORDER BY s1_.from_date_time ASC
    ) dctrn_result
) dctrn_table`

SQL Server then throws an error because you included an ORDER BY statement inside a subquery:

An exception occurred while executing a query: SQLSTATE [42000, 1033]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

I have come up with some ghetto solutions, but don't feel comfortable or capable enough to apply it to such an important project.

@Matt-PMCT commented on GitHub (Jul 5, 2025): This continues to be a problem, which I'm going to summarize again since it has been a while and some things like the initial mssql check have changed. Lines 58-60 appear to be an attempt to prevent this problem from occurring: ```php if ($this->platform instanceof SQLServerPlatform) { $selectStatement->orderByClause = null; } ``` But in line 62 this command calls the SqlWalker.php file, at line 518-527 the orderby clause is skipped due to being null, and then immediately an ORDER BY is added to the query. ```php if ($selectStatement->orderByClause) { $sql .= $this->walkOrderByClause($selectStatement->orderByClause); } $orderBySql = $this->generateOrderedCollectionOrderByItems(); if (! $selectStatement->orderByClause && $orderBySql) { $sql .= ' ORDER BY ' . $orderBySql; } ``` Basically the CountOutputWalker attempts to not break the SQL server and then the SQL Walker breaks SQL server. A short example of this problem is that I have a query that Doctrine generates that I is attempted to be used in a Paginator: ```sql SELECT l0_.id AS id_0, l0_.date_time_from AS date_time_from_1, s1_.from_date_time AS from_date_time_14 FROM logEntry l0_ LEFT JOIN subEntry s1_ ON l0_.id = s1_.log_entry_id WHERE l0_.log_entry_book_id IN (?) AND l0_.date_time_from IS NOT NULL ORDER BY s1_.from_date_time ASC ``` This query is passed to lib/Doctrine/ORM/Tools/Pagination/CountOutputWalker.php which on line 119 wraps the query to be inside of another query. This produces: ```sql SELECT COUNT(*) AS dctrn_count FROM ( SELECT DISTINCT id_0 FROM ( SELECT l0_.id AS id_0, l0_.date_time_from AS date_time_from_1, s1_.from_date_time AS from_date_time_14 FROM logEntry l0_ LEFT JOIN subEntry s1_ ON l0_.id = s1_.log_entry_id WHERE l0_.log_entry_book_id IN (?) AND l0_.date_time_from IS NOT NULL ORDER BY s1_.from_date_time ASC ) dctrn_result ) dctrn_table` ``` SQL Server then throws an error because you included an ORDER BY statement inside a subquery: > An exception occurred while executing a query: SQLSTATE [42000, 1033]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. > SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. I have come up with some ghetto solutions, but don't feel comfortable or capable enough to apply it to such an important project.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6671