Bug Report: SQL Server Pagination Error with SELECT DISTINCT in `newBuildOrderByAndLimit`

[santilin]

---

Affected Function

on yii2/db/mssql/QueryBuilder.php:

/**
 * Builds the ORDER BY/LIMIT/OFFSET clauses for SQL SERVER 2012 or newer.
 * @param string $sql the existing SQL (without ORDER BY/LIMIT/OFFSET)
 * @param array $orderBy the order by columns. See [[\yii\db\Query::orderBy]] for more details on how to specify this parameter.
 * @param int $limit the limit number. See [[\yii\db\Query::limit]] for more details.
 * @param int $offset the offset number. See [[\yii\db\Query::offset]] for more details.
 * @return string the SQL completed with ORDER BY/LIMIT/OFFSET (if any)
 */
protected function newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
{
    $orderBy = $this->buildOrderBy($orderBy);
    if ($orderBy === '') {
        // ORDER BY clause is required when FETCH and OFFSET are in the SQL
        $orderBy = 'ORDER BY (SELECT NULL)';
    }
    $sql .= $this->separator . $orderBy;
    // [https://technet.microsoft.com/en-us/library/gg699618.aspx]
    $offset = $this->hasOffset($offset) ? $offset : '0';
    $sql .= $this->separator . "OFFSET $offset ROWS";
    if ($this->hasLimit($limit)) {
        $sql .= $this->separator . "FETCH NEXT $limit ROWS ONLY";
    }
    return $sql;
}

Problem Description

When generating paginated queries using SQL Server 2012+ and the original SQL contains SELECT DISTINCT, the current fallback ORDER BY (SELECT NULL) fails with:

SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Example query generated:

SELECT DISTINCT * FROM [EmpleadoNomina] WHERE [UltimaActividadEmpleado]=-1 ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 12 ROWS ONLY

This fails because SQL Server does not accept an ORDER BY expression that isn’t in the SELECT list when using DISTINCT.[11][12]

Suggested Solution

Instead of ORDER BY (SELECT NULL), use:

ORDER BY 1

This safely orders by the first column in the SELECT list and works with SELECT DISTINCT, allowing OFFSET/FETCH to operate without requiring knowledge of specific columns. This is a widely used pattern for generic paging in SQL Server.[12][11]

Proposed Change

Replace:

$orderBy = 'ORDER BY (SELECT NULL)';

With:

$orderBy = 'ORDER BY 1';

Additional Context

Using ORDER BY 1 is not semantically ideal but is required in cases where column names are unavailable, especially with SELECT DISTINCT. This adjustment fixes the SQL generation and resolves the SQL Server error.

[xicond]

but the behavior is different
ORDER BY 1 will order by first column selected by default
ORDER BY (SELECT NULL) sort by the value of a constant expression
could be BC break
maybe can add checker $this->hasLimit($limit)

[santilin]

What does "BC" mean?

[xicond]

BC backward compatibility

[terabytesoftw]

Add the code that produces the error, so i can create a test to reproduce it. I will add tests for SQL2012SP1 so I can check it thoroughly. I will add several tests to cover various cases that i have pending.

[santilin]

(AI generated)

Here is a simple example of a unit test in Yii2 using Codeception that tests the reported bug for the newBuildOrderByAndLimit method or the SQL generation for SQL Server paging.

<?php
namespace tests\unit;

use Yii;
use Codeception\Test\Unit;
use yii\db\Command;

class SqlServerPagingTest extends Unit
{
    /**
     * Test that the SQL generated for paging with SELECT DISTINCT uses ORDER BY 1
     * instead of ORDER BY (SELECT NULL) to avoid SQL Server error.
     */
    public function testPagingOrderByForDistinct()
    {
        $db = Yii::$app->db;

        // Create a Command object simulating paging for a DISTINCT query without orderBy param
        $sqlBase = "SELECT DISTINCT * FROM [EmpleadoNomina] WHERE [UltimaActividadEmpleado] = -1";

        // Normally the orderBy is empty here so the fallback triggers ORDER BY (SELECT NULL)
        // The fixed implementation should use ORDER BY 1 to avoid SQL Server error with DISTINCT
        $orderBy = ''; // empty means fallback code path

        $limit = 12;
        $offset = 0;

        // Build the SQL using the hypothetical fixed method (simulate what Yii2 should do)
        $orderByClause = $orderBy === '' ? 'ORDER BY 1' : 'ORDER BY ' . $orderBy;
        $expectedSql = $sqlBase . " " . $orderByClause . " OFFSET $offset ROWS FETCH NEXT $limit ROWS ONLY";

        // Simulating the method call that generates the SQL (replace with actual call if available)
        $sqlGenerated = $this->buildOrderByAndLimit($sqlBase, $orderBy, $limit, $offset);

        // Assert the SQL generated matches expected behavior
        $this->assertEquals($expectedSql, $sqlGenerated);
    }

    /**
     * Simulated version of the fixed newBuildOrderByAndLimit method from Yii2 db command,
     * adjusted to fix the bug with DISTINCT and fallback orderBy.
     */
    protected function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
    {
        $orderBy = $orderBy === '' ? 'ORDER BY 1' : 'ORDER BY ' . $orderBy;
        $offset = $offset !== null ? $offset : 0;
        $sql .= " $orderBy OFFSET $offset ROWS";
        if ($limit !== null) {
            $sql .= " FETCH NEXT $limit ROWS ONLY";
        }
        return $sql;
    }
}

Explanation:

• This unit test simulates the creation of a SQL Server paging query with SELECT DISTINCT.
• It asserts that when no explicit orderBy is provided, the clause falls back to ORDER BY 1 not ORDER BY (SELECT NULL).
• This avoids the SQL Server syntax error with paging and DISTINCT.
• The buildOrderByAndLimit method is simulated here as the "fixed" behavior.
• Replace Yii::$app->db with the actual DB component you use if needed.
• This test can be placed in tests/unit and run with Codeception.

If the actual method exists in Yii2, replace the simulation with a real method call.