DBAL-48: Doctrine\DBAL\Platforms\MsSqlPlatform Incorrect Offset Limit Sql #1680

Closed
doctrinebot opened this Issue Sep 4, 2010 · 2 comments

1 participant

@doctrinebot

Jira issue originally created by user scottsweep:

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] ROWNUMBER() OVER (ORDER BY [primarykey]) as RowNum, col1, col2, colN FROM table
) as [innertbl] WHERE [innertbl].RowNum BETWEEN [offset] AND [limit + offset]

@doctrinebot

Comment created by juokaz:

This is fixed in current master https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MsSqlPlatform.php#L555

Unless this ticket is referring to Doctrine 1

@doctrinebot

Issue was closed with resolution "Invalid"

@doctrinebot doctrinebot added the Bug label Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment