Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-713

MSSQL: Wrong Placement of "ROW_NUMBER() OVER" when using Subqueries in SELECT part

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.4.1
    • Fix Version/s: 2.5
    • Component/s: Platforms
    • Security Level: All
    • Labels:
    • Environment:
      PHP 5.4.4 (with pdo_sqlsrv extension)
      Windows 7

      Description

      I'm trying to create a DQL query like that:

      SELECT Task.id AS id, Task.date AS date, (
      	SELECT COUNT(p.posNr)
      	FROM Project\Entity\Position p
      	WHERE Task.id=p.ref
      ) AS poscount
      FROM Project\Entity\Task Task
      WHERE Task.id <> 0 AND Task.status < 3
      ORDER BY Task.date DESC
      

      This works flawlessly on MSSQL until i try to apply a LIMIT/OFFSET by using setFirstResult() and setMaxResults().
      Applying a Limit results in an invoke of "doModifyLimitQuery()" in "Doctrine\DBAL\Platforms\SQLServerPlatform".

      The function implementation clearly states what's wrong:

      //Replace only first occurrence of FROM with $over to prevent changing FROM also in subqueries.
      $over  = 'ORDER BY ' . implode(', ', $overColumns);
      $query = preg_replace('/\sFROM\s/i', ", ROW_NUMBER() OVER ($over) AS doctrine_rownum FROM ", $query, 1);
      

      This breaks support with subqueries in SELECT statements.

        Activity

        Hide
        Steve Müller added a comment -

        Yeah this seems to be indeed wrong. This method is going one step forward and one step back with each adjustment I don't feel comfortable trying to fix that one as it is really sensitive. But you are welcome to provide a patch for this on github (or someone else).

        Show
        Steve Müller added a comment - Yeah this seems to be indeed wrong. This method is going one step forward and one step back with each adjustment I don't feel comfortable trying to fix that one as it is really sensitive. But you are welcome to provide a patch for this on github (or someone else).
        Hide
        Tom Drissen added a comment -

        I ran into this bug today, which is really annoying. Is there any change this will be fixed shortly?
        How about checking parenthesises to determine the 'base' FROM/table?

        Show
        Tom Drissen added a comment - I ran into this bug today, which is really annoying. Is there any change this will be fixed shortly? How about checking parenthesises to determine the 'base' FROM/table?
        Hide
        Steve Müller added a comment -
        Show
        Steve Müller added a comment - Patch supplied in PR: https://github.com/doctrine/dbal/pull/512
        Hide
        Doctrine Bot added a comment -

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

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

        Important Notice:

        The usage of preg_replace() in this bugfix can cause Apache to abort the PHP execution because of a stack overflow.
        Apache's default value for "ThreadStackSize" on Windows is 1MB. This is not sufficient if you use preg_replace() on long queries.
        I had to increase the size to 8MB.
        This should be mentioned somewhere.

        Show
        M.K. added a comment - Important Notice: The usage of preg_replace() in this bugfix can cause Apache to abort the PHP execution because of a stack overflow. Apache's default value for "ThreadStackSize" on Windows is 1MB. This is not sufficient if you use preg_replace() on long queries. I had to increase the size to 8MB. This should be mentioned somewhere.
        Hide
        Marco Pivetta added a comment -

        M.K. how long is the query with which you are experiencing this? I'd say it's more a PHP bug than problem of the DBAL

        Show
        Marco Pivetta added a comment - M.K. how long is the query with which you are experiencing this? I'd say it's more a PHP bug than problem of the DBAL
        Hide
        Steve Müller added a comment -

        That might be an issue of using recursion in the regular expression maybe. I'm not sure if that is known limitation of PHP. But it seems weird to me you have to adjust apache config instead of PHP for this.

        Show
        Steve Müller added a comment - That might be an issue of using recursion in the regular expression maybe. I'm not sure if that is known limitation of PHP. But it seems weird to me you have to adjust apache config instead of PHP for this.
        Hide
        M.K. added a comment - - edited

        My Query is 1275 characters long.

        It's not a bug at all, just a misconfiguration in Apache. But it's really hard to debug this problem, because Apache just kills PHP and doesn't say a word about it. So it would be kind to document this somewhere, so that new Doctrine Users can configure their Apache correctly.

        Show
        M.K. added a comment - - edited My Query is 1275 characters long. It's not a bug at all, just a misconfiguration in Apache. But it's really hard to debug this problem, because Apache just kills PHP and doesn't say a word about it. So it would be kind to document this somewhere, so that new Doctrine Users can configure their Apache correctly.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            M.K.
          • Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: