Assigning to [~peptolab] for initial review.

Andre Vignaud in this message propose modifications in Zend_Db_Select:

-   protected function _renderLimitoffset()
+   protected function _renderLimitoffset($sql)
    {
        $count = 0;
        $offset = 0;

        if (!empty($this->_parts[self::LIMIT_OFFSET])) {
            $offset = (int) $this->_parts[self::LIMIT_OFFSET];
            // This should reduce to the max integer PHP can support
            $count = intval(9223372036854775807);
        }

        if (!empty($this->_parts[self::LIMIT_COUNT])) {
            $count = (int) $this->_parts[self::LIMIT_COUNT];
        }

        /*
         * Add limits clause
         */
        if ($count > 0) {
-           return trim($this->_adapter->limit('', $count, $offset));
+           return trim($this->_adapter->limit($sql, $count, $offset));
        }

-        return null;
+        return $sql;
    }

and

    public function __toString()
    {
        $sql = array(self::SQL_SELECT);
        foreach (array_keys(self::$_partsInit) as $part) {
            $method = '_render' . ucfirst($part);
            if (method_exists($this, $method)) {
-               $sql[] = $this->$method();
+               if ( $part == self::LIMIT_OFFSET ) { 
+                   $sql = array($this->_renderLimitoffset(implode(' ', array_filter($sql)))); 
+               } else { 
+                   $sql[] = $this->$method(); 
+               }
            }
        }
        return implode(' ', array_filter($sql));

I've refactored Zend_Db_Select to more gracefully handle the various transformations to the query. Can you please verify the limit() method is now behaving itself?

Fixed in trunk r8784

Was this fixed in 1.5.1? If not, could you get it verified, merge back to 1.5 branch, and mark it fixed for 1.5? If so, could you mark it fixed for 1.5.1?

Thanks.

This was fixed effectively for me in rév. 8784 (for 1.5.1) but not merge back to 1.5.0.

I'm using Zend Framework 1.5.2 and this function is not working for me, I get the following error:

SQLSTATE[HY000]: General error: 918 OCIStmtExecute: ORA-00918: column ambiguously defined

(ext\pdo_oci\oci_statement.c:146)

PHO Code:

$select = $this->db->select() ->from(array('p' => 'HARDWARE_JOBS_VW'), array('HARDWARE_ID','NAME','ACCOUNT','PASSWORD','TYPE_NAME','GROUP_ID','PLATFORM_NAME'))
->join(array('pf' =>'USERS_IN_GROUPS'), 'p.GROUP_ID=pf.GROUP_ID')
->where('pf.USER_ID= ?', $id)
->where('p.GROUP_ID= ?',$groupid) ->limit($page,$limit);

              $stm = $this->db->query($select); 

This is the output from __toString();

{quote} SELECT z2.*

        FROM (

            SELECT ROWNUM AS zend_db_rownum, z1.*

            FROM (

                SELECT p.HARDWARE_ID, p.NAME, p.ACCOUNT, p.PASSWORD, p.TYPE_NAME, p.GROUP_ID, p.PLATFORM_NAME

, pf.* FROM HARDWARE_JOBS_VW p

INNER JOIN USERS_IN_GROUPS pf ON p.GROUP_ID=pf.GROUP_ID WHERE (pf.USER_ID= '8') AND (p.GROUP_ID= '21'

)

            ) z1

        ) z2

        WHERE z2.zend_db_rownum BETWEEN 11 AND 11

{/quote}

Hi,

It is not due to the limit() part but this is due to "join(array('pf' =>'USERS_IN_GROUPS')". In this you select also pf.GROUP_ID so in Z1, you have p.GROUP_ID and pf.GROUP_ID. To resolve this you can add an alias to the first table like this:

$select = $this->db->select()
->from(array('p' => 'HARDWARE_JOBS_VW'),
array('HARDWARE_ID','NAME','ACCOUNT','PASSWORD','TYPE_NAME','gid'=>'GROUP_ID','PLATFORM_NAME'))
->join(array('pf' =>'USERS_IN_GROUPS'),
'p.GROUP_ID=pf.GROUP_ID')
->where('pf.USER_ID= ?', $id)
->where('p.GROUP_ID= ?',$groupid)
->limit($page,$limit);

$stm = $this->db->query($select); 

Thanks that fixed it.