Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-48

Doctrine\DBAL\Platforms\MsSqlPlatform Incorrect Offset Limit Sql

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Platforms
    • Labels:
      None
    • Environment:
      All MSSQL

      Description

      The way the offset query is constructed (line 473) it always pulls the top records from the query even when and offset is present, so if you have an offset of 0 and limit of 10 you get the first 10 records. When you change the offset to 5 you still get the first 10 records from the original query. This issue was also in the 1.2 driver code.

      This is basically what gets created when an offset is present with a limit of 10 and an offset of 5 :
      SELECT * FROM
      (SELECT TOP 10 * FROM
      (SELECT TOP 15
      col1, col2, colN FROM table
      ) AS [inner_tbl]
      ) AS [outer_tbl]

      The fix is to reconstruct the query to remove the outer_tbl SELECT TOP and make it something like the following:
      SELECT * FROM (
      SELECT TOP [limit] ROW_NUMBER() OVER (ORDER BY [primary_key]) as RowNum, col1, col2, colN FROM table
      ) as [inner_tbl] WHERE [inner_tbl].RowNum BETWEEN [offset] AND [limit + offset]

        Activity

          People

          • Assignee:
            Juozas Kaziukenas
            Reporter:
            Scott Connelly
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: