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 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
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"> 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.
Have you found an issue?
See the Overview section for more details.