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

Issue Type: Bug Created: 2008-12-01T14:56:28.000+0000 Last Updated: 2011-08-20T15:13:12.000+0000 Status: Resolved Fix version(s): Reporter: Garrett Hood (jc8) Assignee: Satoru Yoshida (satoruyoshida) Tags: - Zend_Db

Related issues: - ZF-4099



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

<pre class="highlight">
$db = new Zend_Db_Adapter_Pdo_Mssql($config);
$select = $db->select()
        array('t' => 'tickets'),
    ->where($db->quoteInto('customerid = ?',$customerId))
    ->order(array('status DESC','cdate DESC'))

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

<pre class="highlight">
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.


Posted by old of Satoru Yoshida ( on 2009-05-14T05:21:44.000+0000

It duplicates ZF-4099

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.