ZF-853: MS SQL Server: limit() function does not behave as expected when no order is applied
(!) The approach of having the adapter help limit the query makes it much more difficult to accomplish, and it might be better for items like the select class to have adapter specific construction of queries. People writing their own queries will unlikely call the limit method directly, so only auto-generated queries will need it. Therefore make the query building smarter rather than trying to make the limit() function so smart.
The MS SQL Server limit() function in class Zend_Db_Adapter_Pdo_Mssql is not correct.
For example, it takes the source query:
select * from HumanResources.Employee emp;
and with a limit applied of 10 count records at offset 5 generates:
SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 15 * from HumanResources.Employee emp) AS inner_tbl) AS outer_tbl
In effect the generated query selects the first 10 rows rather than the 5th-15th. To break it down, the inner most query returns the top 15 rows, then the next outer takes those top 15 and grabs the top 10 which makes the most inner query meaningless.
This is due to the fact that the system relies on the "order by" clause of the query to get the records it wants and without the clause being present it does not add one (what would it order by anyway?)
(!) If it can't handle a query that does not have "order by" and it is deemed that limit() only works with ordered queries, then an exception should be thrown indicating the query is invalid just as the other parameters are checked at the top of the method.
Also, for SQL Server 2005 the best approach for paging like this is (which really should be special cased since it is a special feature just for this purpose):
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY EmployeeID) AS rnum, * FROM HumanResources.Employee) AS EmpWithRNUM where rnum >= 5 and rnum <= 15
For older versions or cases where you have no known "order by", the best way is really to use temporary tables or one of the other odd paging choices.
You can try this style if you know the primary key. This version does anti-joins with index scans while the original version (as implemented) causes 2 sorts followed by an index scan.
select top 15 * from HumanResources.Employee emp where emp.EmployeeID not in (select top 5 emp2.EmployeeID from HumanResources.Employee emp2)
(!) Other things this limit() function breaks on is "union" queries and possibly other queries where it will place an accidental "order by" into the wrong place.