Issues

ZF-8944: fetchRow() ignores limit offset

Description

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')
  ->order('result')
  ->limit(1,10);
          
echo (string) $teams;
// SELECT `teams`.* FROM `teams` WHERE (colour = 'orange') ORDER BY `result` ASC LIMIT 1 OFFSET 10
        
$teams->fetchRow($teams);
// 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));

Comments

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

 Zend_Db_Table_Select_OracleTest
 .

 Zend_Db_Table_Select_Pdo_MysqlTest
 .

 Zend_Db_Table_Select_Pdo_OciTest
 .

 Zend_Db_Table_Select_Pdo_PgsqlTest
 .

 Zend_Db_Table_Select_Pdo_SqliteTest
 .

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 [http://framework.zend.com/svn/framework/…]?

Because it's in http://framework.zend.com/svn/framework/… (with Table/Select instead of Select) ;)