ZF-10831: Distinct statement doesn't work with basic limit in Zend_Db_Adapter_Sqlsrv

Issue Type:
Bug
Created:
2010-12-16T01:48:33.000+0000
Last Updated:
2012-01-26T12:45:26.000+0000
Status:
Open
Fix version(s):
    Reporter:
    Karel Barborak (kbarbo)
    Assignee:
    Ralph Schindler (ralph)
    Tags:
    • Zend_Db
    Related issues:
    Attachments:

    Description

    I have found an error with basic limit statement if I use Zend_Db_Adapter_Sqlsrv as database adapter. Here is example:

     
$adapter = Zend_Registry::get('dbAdapter'); // Instance of Zend_Db_Adapter_Sqlsrv
$select = $adapter->select();

$select->distinct();
$select->from('Foo', array('FooID'));
$select->limit(5);
echo $select->__toString();

    The result is following:

     
SELECT TOP 5 DISTINCT "Foo"."FooID" FROM "Foo"

    But it produces database error. TOP have to be after DISTINCT.

     
SELECT DISTINCT TOP 5 "Foo"."FooID" FROM "Foo"

    I'm using Zend Framework 1.11.1, Zend Server 5.0.2 (PHP 5.3.2), MSSQL 2005 Express.

    Comments

    Posted by Kevin Sopp (baraclese) on 2011-03-29T07:03:16.000+0000

    The issue that you cannot use distinct() combined with a limit() clause in a select statement is still present in 1.11.4. I fixed it in our local installation with the following change (works in 1.11.3 and 1.11.4):

    In Zend/Db/Adapter/Sqlsrv.php change line 616 from: $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $count . ' ', $sql);

    to: $sql = preg_replace('/^(SELECT\sDISTINCT\s|SELECT\s)/i', '$0 TOP ' . $count . ' ', $sql);

    This will place the DISTINCT keyword before the TOP keyword.

    Posted by Kevin Sopp (baraclese) on 2011-03-29T07:12:48.000+0000

    Same as above comment with pretty printing: from:

    

to:

    Posted by Evgeniy (incadawr) on 2011-04-13T14:15:52.000+0000

    In Zend/Db/Adapter/Sqlsrv.php change line 615: if ($offset == 0) { $sql = preg_replace('/^SELECT\s+(DISTINCT\s)?/i', 'SELECT TOP ' . $count . ' ', $sql); } else { to: $sql = preg_replace( '/^SELECT\s+(DISTINCT\s)?/i', 'SELECT $1TOP ' . ($count+$offset) . ' ', $sql ); if($offset > 0) {

    Posted by Michael Hodgins (michael_hodgins) on 2012-01-26T12:45:26.000+0000

    Still not fixed in 1.11.11, over a year later.