ZF-8944: fetchRow() ignores limit offset

Issue Type:
Bug
Created:
2010-01-26T12:28:29.000+0000
Last Updated:
2011-04-27T20:21:52.000+0000
Status:
Resolved
Fix version(s):
  • 1.10.2 (24/Feb/10)
Reporter:
Lauri Elevant (clon)
Assignee:
Mickael Perraud (mikaelkael)
Tags:
  • Zend_Db
Related issues:
Attachments:

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

Posted by Lauri Elevant (clon) on 2010-01-26T12:35:08.000+0000

Sorry for the repeated edits :)

Posted by Michael Rehbein (tech13) on 2010-02-18T14:07:10.000+0000

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

Posted by Mickael Perraud (mikaelkael) on 2010-02-19T13:17:59.000+0000

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.

Posted by The Lone Coder (loconut) on 2011-02-06T12:13:52.000+0000

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);

Posted by Kim Blomqvist (kblomqvist) on 2011-04-22T21:29:48.000+0000

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)

Posted by Mickael Perraud (mikaelkael) on 2011-04-26T07:39:03.000+0000

Trunk, r21101: http://framework.zend.com/code/comp.php/…

1.10 branch, r21103: http://framework.zend.com/code/comp.php/…

Posted by Kim Blomqvist (kblomqvist) on 2011-04-26T15:15:00.000+0000

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/…]?

Posted by Mickael Perraud (mikaelkael) on 2011-04-27T20:21:52.000+0000

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