Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-834

SQLServer modifyLimitQuery does not work with aggregate functions in ORDER BY

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.4.2
    • Fix Version/s: 2.5
    • Component/s: Platforms
    • Labels:
    • Environment:
      SQL Server 2008 SP3

      Description

      Starting with Doctrine 2.4, the `modifyLimitQuery` method does not work anymore with query using ORDER BY MAX(...)
      See this example:

      $sql = "SELECT MAX(heading_id) aliased, code
      	FROM operator_model_operator
      	GROUP BY code
      	ORDER BY MAX(heading_id) DESC
      ";
      $sql = $this->em->getConnection()->getDatabasePlatform()->modifyLimitQuery(
      	$sql, 1, 0
      );
      

      Doctrine generates this SQL, which is invalid:

      SELECT * FROM (SELECT MAX(heading_id) aliased, code
      , ROW_NUMBER() OVER (ORDER BY MAX(heading_id) AS doctrine_rownum FROM operator_model_operator GROUP BY code) DESC
      ) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 1

      The ORDER BY in moved into the OVER(), but the `preg_replace` in SQLServerPlatform.php stops to replace at the closing ")".

        Issue Links

          Activity

          Hide
          M.K. added a comment -
          SELECT MAX(heading_id) aliased, code
          FROM operator_model_operator
          GROUP BY code
          ORDER BY aliased DESC
          

          This Query won't work with modifyLimitQuery

          Show
          M.K. added a comment - SELECT MAX(heading_id) aliased, code FROM operator_model_operator GROUP BY code ORDER BY aliased DESC This Query won't work with modifyLimitQuery
          Hide
          Steve Müller added a comment -

          M.K. Okay I think I get what you mean but that is another issue IMO. There should be a new ticket for this.

          Show
          Steve Müller added a comment - M.K. Okay I think I get what you mean but that is another issue IMO. There should be a new ticket for this.
          Hide
          Francesco Montefoschi added a comment -

          Can we merge this in master/2.5?

          Show
          Francesco Montefoschi added a comment - Can we merge this in master/2.5?
          Hide
          Doctrine Bot added a comment -

          A related Github Pull-Request [GH-573] was closed:
          https://github.com/doctrine/dbal/pull/573

          Show
          Doctrine Bot added a comment - A related Github Pull-Request [GH-573] was closed: https://github.com/doctrine/dbal/pull/573
          Show
          Steve Müller added a comment - Fixed in commit: https://github.com/doctrine/dbal/commit/4a7ff71ec3b57af7d70f1180897502f8a156d59b

            People

            • Assignee:
              Steve Müller
              Reporter:
              Francesco Montefoschi
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: