Details
Bug
Major
Description
MSSQL PDO limit( function.
The principle discussed and referenced within the code does not work in all cases.
http://lists.bestpractical.com/pipermail/rt-devel/2005-June/007339.html
Example:
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.
1.count+offset
so if count+offset > rowCount
2. (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.
Issue Links
| This issue duplicates: | ||||
| ZF-853 | MS SQL Server: limit() function does not behave as expected when no order is applied |
|
|
|
How about this solution:
SELECT TOP <count> * FROM tablename WHERE key NOT IN ( SELECT TOP <offset> key FROM tablename ORDER BY key ) ORDER BY keyThis should reduce the nested subqueries by one, and support the case you're talking about where count+offset is greater than the number of rows in the table.
SELECT TOP <count> * FROM tablename WHERE key NOT IN ( SELECT TOP <offset> key FROM tablename ORDER BY key ) ORDER BY key