ZF-1037: MS SQL Server: PDO limit() function does not work when count+offset > number of rows in table
MSSQL PDO limit( function.
The principle discussed and referenced within the code does not work in all cases.
Consider a table view_content_admin with 14 record within it. limit is called with offset:10 count:10
SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 20 content.* FROM view_content_admin ORDER BY content.name ASC) AS select_limit_rev ORDER BY name DESC ) AS select_limit ORDER BY name ASC
Given the logic specified in the above query records 5-14 are produced rather than the desired 10-14.
The core logic of the SQL theory splits into creating two sets: 1.count+offset 2.count.
When the 'count+offset' is set is larger than that of the number of records within the table/result there are not enough rows in the set in order for the second set 'count' to capture the desired rows. It will always capture more results than desired. The set 'count' needs to take account of the number rows within the table/result in order to correclty select the limit,offset.
so if count+offset > rowCount
- (rowCount - offset)+1
The difficult task is knowing the number of rows for a select statement. This is impossible to gather in all contexts as the limit function may be called before other where clauses are added. And having to run the query to get the rowCount has significant performance issues.
So in summary When the set of results is smaller than count+offset the rows returned do not match those expect from the offset and limit values. It seems impossible for the limit function to work in its current guise.