Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-787

[GH-512] Fix modifying limit/offset for statements with subqueries on SQL Server

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.5, 2.4.3
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      This issue is created automatically through a Github pull request on behalf of deeky666:

      Url: https://github.com/doctrine/dbal/pull/512

      Message:

      `SELECT` statements that contains subqueries in the `SELECT` clause do not get properly rewritten with a limit and/or offset applied to it resulting in wrong SQL.

      *Example*
      ```sql
      SELECT foo.id, (SELECT COUNT FROM bar) AS bar_count FROM foo
      ```

      *Expected with a limit of 10*
      ```sql
      SELECT * FROM (SELECT foo.id, (SELECT COUNT FROM bar) AS bar_count, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS doctrine_rownum FROM foo) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 10
      ```

      *Actual with a limit of 10*
      ```sql
      SELECT * FROM (SELECT foo.id, (SELECT COUNT, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS doctrine_rownum FROM bar) AS bar_count FROM foo) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 10
      ```

      The `ROW_NUMBER() OVER` clause is misplaced into the subselect instead of into the main `FROM` clause.
      What this PR does is recursively matching any (nested) parentheses inside the main `SELECT` clause to be able to identify the main `FROM` clause to add the `ROW_NUMBER() OVER` clause to.
      This of course is far from perfect for matching all kinds of possible `SELECT` statement syntaxes but it fixes this particular issue, which is pretty common IMO.

        Activity

        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

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Doctrine Bot
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: