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: - ZF-853



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

  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.


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

How about this solution:

<pre class="highlight">
FROM tablename
    SELECT TOP  key
    FROM tablename
    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.

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.