ZF-1037: MS SQL Server: PDO limit() function does not work when count+offset > number of rows in table

Issue Type:
Bug
Created:
2007-03-12T06:05:51.000+0000
Last Updated:
2007-07-05T14:43:55.000+0000
Status:
Resolved
Fix version(s):
  • 1.0.0 RC1 (28/May/07)
Reporter:
Joseph Wilk (joesniff)
Assignee:
Bill Karwin (bkarwin)
Tags:
  • Zend_Db
Related issues:
Attachments:

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/…

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

  1. (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.

Comments

Posted by Bill Karwin (bkarwin) on 2007-03-12T10:58:12.000+0000

How about this solution:


SELECT TOP  *
FROM tablename
WHERE key NOT IN (
    SELECT TOP  key
    FROM tablename
    ORDER BY key
)
ORDER BY key

This 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.

Posted by Bill Karwin (bkarwin) on 2007-04-22T18:10:22.000+0000

After thinking about this issue I realize that my supposed solution above only works when ordering by a column with a UNIQUE or PRIMARY KEY constraint on it. It doesn't work for general usage of LIMIT.

This problem may not be solvable without fetching the query result set up to LIMIT+OFFSET rows, and discarding the first OFFSET rows. This is terribly inefficient.

The other possible solution would be to say we don't support the limit() method for the Pdo/Mssql adapter, because it's not possible to solve it efficiently in the general case. Many cases can be solved by using "TOP" but not all cases.

Instead the limit() method in the Pdo/Mssql adapter would throw an exception; anyone who wants similar behavior must solve it in a case-by-case basis. This would also resolve ZF-853.

Posted by Bill Karwin (bkarwin) on 2007-05-19T14:50:04.000+0000

This issue is actually a subset of the problems described in ZF-853.

Posted by Bill Karwin (bkarwin) on 2007-05-19T14:50:21.000+0000

Resolving issue as duplicate.