[DBAL-48] Doctrine\DBAL\Platforms\MsSqlPlatform Incorrect Offset Limit Sql Created: 04/Sep/10  Updated: 14/Nov/10  Resolved: 14/Nov/10

Status: Closed
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Scott Connelly Assignee: Juozas Kaziukenas
Resolution: Invalid Votes: 0
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]



 Comments   
Comment by Juozas Kaziukenas [ 14/Nov/10 ]

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

Generated at Sun Dec 21 13:40:48 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.