SQL Server platform cannot apply LIMIT to a UNION query with ORDER BY

[morozov]

The cases below were reproduced on Doctrine DBAL 2.5.4 and SQL Server 11.0.2100.60 with the following code:

$query = '...';
$query = $conn->getDatabasePlatform()->modifyLimitQuery($query, 20, 0);
$conn->query($query);

1. UNION with sub-queries surrounded by parentheses

   (SELECT id FROM accounts) UNION ALL (SELECT id FROM contacts) ORDER BY id

   Then TOP keyword is not added to the query:

   WITH dctrn_cte AS ((SELECT id
                       FROM accounts)
                      UNION ALL (SELECT id
                                 FROM contacts)
                      ORDER BY id) SELECT *
                                   FROM (SELECT
                                           *,
                                           ROW_NUMBER()
                                           OVER (
                                             ORDER BY (SELECT 0)) AS doctrine_rownum
                                         FROM dctrn_cte) AS doctrine_tbl
                                   WHERE doctrine_rownum BETWEEN 1 AND 20
                                   ORDER BY doctrine_rownum ASC

   Which results into SQL error:

   [156] Incorrect syntax near the keyword 'ORDER'.
   

2. UNION without parentheses

   SELECT id FROM accounts UNION ALL SELECT id FROM contacts ORDER BY id

   Then TOP keyword is added to the first sub-query instead of the resulting set.

   WITH dctrn_cte AS (SELECT TOP 20 id
                      FROM accounts
                      UNION ALL SELECT id
                                FROM contacts
                      ORDER BY id) SELECT *
                                   FROM (SELECT
                                           *,
                                           ROW_NUMBER()
                                           OVER (
                                             ORDER BY (SELECT 0)) AS doctrine_rownum
                                         FROM dctrn_cte) AS doctrine_tbl
                                   WHERE doctrine_rownum BETWEEN 1 AND 20
                                   ORDER BY doctrine_rownum ASC

   The SQL server still seems to not like the ORDER BY in the CTE:

   [1033] 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.
   

3. UNION with line breaks.

   SELECT id FROM accounts
   UNION ALL
   SELECT id FROM contacts
   ORDER BY id

   Unlike Handle arbitrary whitespaces when parsing SQL in order to apply LIMIT for MS SQL Server #2372 where only an invalid query is produced, in this case PHP goes into infinite loop:

   Notice: String offset cast occurred in .../vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php on line 1232
   
   Call Stack:
       0.0001     234936   1. {main}() .../main.php:0
       0.1328   36317880   2. Doctrine\DBAL\Platforms\AbstractPlatform->modifyLimitQuery() .../main.php:26
       0.1328   36318080   3. Doctrine\DBAL\Platforms\SQLServerPlatform->doModifyLimitQuery() .../vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/AbstractPlatform.php:3319
       0.1329   36318872   4. Doctrine\DBAL\Platforms\SQLServerPlatform->scrubInnerOrderBy() .../vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php:1197
   
   The above repeats endlessly
   

4. UNION as sub-query where ORDER BY is moved to the outer query.

   SELECT * FROM (SELECT id FROM accounts UNION ALL SELECT id FROM contacts) union_tmp ORDER BY id

   This approach works.

[Ocramius]

We never actually supported UNION queries, and don't have tests for them. Adding another sub-query for MSSQL seems feasible.

[morozov]

Closing as irrelevant as of #3756. All the queries in the description work as expected in 3.0.0.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.