Issues

ZF-11821: There still seams to be a problem with Order by and Limit when useing 2 arguments with DESC

Description

I have read the issues previously reported about "Zend_Db_Select problem with 2 order by and limit" and other bugs about the same thing. I have recently come to the conclusion that the problem with this issue is not fixed entirely in version 1.10.8 of ZendFramework.

The issiue is only arrising when using MSSQL Servers. I think it has something to do with that sends uses TOP command. This is the wrong way to do limit in a MSSQL database.

I would recommend using something like this in MSSQL instead of using MSSQL Top command when doing the limit:

select * from (select Row_Number() over (order by c.dateno1) as RowIndex, * from mytable AS c WHERE c.id = 4) as Sub Where Sub.RowIndex >= 8 and Sub.RowIndex < 16;

I have problems when running a code like this:

$select = $this->select() ->from($this->_name) ->where('id=?', $id) ->where('status!=?', 'AAAA') ->where('status!=?', 'BBBB') ->order(array('dateno1 DESC', 'dateno2 DESC')) ->limit($nbrs,$start);

The ordering of the resultset comes in the wrong order.

Removing the secound agument is wrong way to go but fixes this issue for us right now until this is resolved in ZendFramework.

$select = $this->select() ->from($this->_name) ->where('id=?', $id) ->where('status!=?', 'AAAA') ->where('status!=?', 'BBBB') ->order(array('dateno1 DESC')) ->limit($nbrs,$start);

Comments

Could you run your code against the latest ZF release (1.11.11) to see if the problem still persists there?

If so, could you please provide some additional information to explain the problem you are having? Ideally a snippet of code where the query is built plus the database schema and a few example rows which will illustrate the case where rows are returned in the wrong order.