Issues

ZF-5129: Zend_Db_Select generates improper query when Zend_Db_Adapter_Pdo_Mssql is used with order() & limit() when order() has 2 or more clauses

Description

Hello. I think I've come across a bug in Zend_Db_Select that is causing my query results to be output in the opposite order I am expecting them. Here is a bit of example code that will hopefully illustrate the problem for you. I am using the 1.7.1 release as of today December 1, 2008. The DB is SQL Server 2005.

Note: This only happens when there are more than one order clause provided while using limit.

// I'd like to order tickets by status then cdate, both in descending order and limit them by N. The table layout is straightforward and the contents of status is either "Open" or "Closed".


$db = new Zend_Db_Adapter_Pdo_Mssql($config);
$select = $db->select()
    ->from(
        array('t' => 'tickets'),
        array('t.ticketid','t.title','t.cdate','t.mdate','t.status')
    )
    ->where($db->quoteInto('customerid = ?',$customerId))
    ->order(array('status DESC','cdate DESC'))
    ->limit(200);

This is the query output to the screen which gives me the results I want, but in an improper order.


SELECT * FROM (SELECT TOP 200 * FROM (SELECT TOP 200 "t"."ticketid", "t"."title", "t"."status", "t"."cdate", "t"."mdate" FROM "tickets" AS "t" WHERE (customerid = '1') ORDER BY "status" DESC, "cdate" DESC) AS inner_tbl ORDER BY "status" , "cdate" ASC) AS outer_tbl 
ORDER BY "status" , "cdate" desc

Have a glance at the last ORDER BY "status", "cdate" desc, shouldn't that be ORDER BY "status" desc, "cdate" desc? When I change the output to that and execute the query I get the results I am looking for on screen.

Any help would be greatly appreciated. Thank you.

Comments

It duplicates ZF-4099