Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-940

ORDER BY with LIMIT in SQL Server does not work correctly

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.5
    • Component/s: Platforms
    • Security Level: All
    • Environment:
      SQL Server

      Description

      The function doModifyLimitQuery() in Doctrine\DBAL\Platforms\SQLServerPlatform does work correctly.

      It removes the user generated ORDER BY (gets moved to OVER clause), but does not apply an ORDER BY on the row number created with ROW_NUMBER().

      $orderBy = stristr($query, 'ORDER BY');
      
      //Remove ORDER BY from $query (including nested parentheses in order by list).
      $query = preg_replace('/\s+ORDER\s+BY\s+([^()]+|\((?:(?:(?>[^()]+)|(?R))*)\))+/i', '', $query);
      
      $format  = 'SELECT * FROM (%s) AS doctrine_tbl WHERE doctrine_rownum BETWEEN %d AND %d';
      

      The last format string should be:

      $format  = 'SELECT * FROM (%s) AS doctrine_tbl WHERE doctrine_rownum BETWEEN %d AND %d ORDER BY doctrine_rownum';
      

        Issue Links

          Activity

          Hide
          Marco Pivetta added a comment -

          Possible relation with DBAL-927

          Show
          Marco Pivetta added a comment - Possible relation with DBAL-927
          Show
          Steve Müller added a comment - Fixed as of https://github.com/doctrine/dbal/commit/040d49c16a737d1349ae86443d64b3382cf4ce5d

            People

            • Assignee:
              Steve Müller
              Reporter:
              M.K.
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: