ZF-8944: fetchRow() ignores limit offset


In the fetchRow() method in Zend_Db_Table_Abstract I find this:

  if (!($where instanceof Zend_Db_Table_Select)) {
    /* snip */
  } else {
    $select = $where->limit(1);

So whenever you pass a Zend_Db_Table_Select object including a limit with an offset, say LIMIT 1 OFFSET 10, it overrules it with just LIMIT 1, even though in both cases the resulting rowset contains a maximum of 1 rows. This is unexpected and silently breaks the meaning of the select, resulting in a wrong row being returned.

Steps to reproduce:

$select = $teams->select()
  ->where('colour = ?', 'orange')
echo (string) $teams;
// SELECT `teams`.* FROM `teams` WHERE (colour = 'orange') ORDER BY `result` ASC LIMIT 1 OFFSET 10
// SELECT `teams`.* FROM `teams` WHERE (colour = 'orange') ORDER BY `result` ASC LIMIT 1

Suggested fix:

$select = $where->limit(1, $where->getPart(Zend_Db_Select::LIMIT_OFFSET));


Sorry for the repeated edits :)

Created a unit test based on the comments. Wasn't able to reproduce the error.

Unit test added with r21101 and correction for Pdo_Oci

Tests OK against all databases without changes to code (trunk and 1.10 branch):

phpunit --verbose --group ZF-8944 Zend_Db_AllTests
PHPUnit 3.4.10 by Sebastian Bergmann.

Zend Framework - Zend_Db






Time: 4 seconds, Memory: 23.25Mb

OK (6 tests, 12 assertions)

Please reopen if the problem always occurs.

While you patched the unit test, I don't see that you fixed the root issue?

I opened the ticket that duplicates this one at version 1.10.8 (this one was reported fixed at 1.10.2, but I don't see the patch in the code here on 1.11.3). Would you please double check this issue?

This was a patch against 1.10.8, but you should be able to fix in the current version.

Index: Zend/Db/Table/Abstract.php
--- Zend/Db/Table/Abstract.php  (revision 1791)
+++ Zend/Db/Table/Abstract.php  (working copy)
@@ -1358,10 +1358,10 @@
                 $this->_order($select, $order);
-            $select->limit(1);
+            $select->limit(1, $select->getPart(Zend_Db_Select::LIMIT_OFFSET));
         } else {
-            $select = $where->limit(1);
+            $select = $where->limit(1, $select->getPart(Zend_Db_Select::LIMIT_OFFSET));
         $rows = $this->_fetch($select);

I cannot find the provided unit tests ...

$ phpunit --group ZF-8944 Zend/Db/AllTests.php

PHPUnit 3.5.13 by Sebastian Bergmann.

Time: 1 second, Memory: 18.00Mb

OK (0 tests, 0 assertions)

Sorry but there has to be something that I don't understand now... why I cannot see the test method here […]?

Because it's in… (with Table/Select instead of Select) ;)