Modified:
   branches/0.11/lib/Doctrine/Connection.php
   branches/0.11/lib/Doctrine/Connection/Db2.php
   branches/0.11/lib/Doctrine/Connection/Oracle.php
   branches/0.11/lib/Doctrine/Query.php
   branches/0.11/tests/Query/DriverTestCase.php
Log:
Fixed #1039. bruno.p.reis: please take a look at the changeset and verify that the generated SQL is correct. We can't test against a real oracle db yet.

Modified: branches/0.11/lib/Doctrine/Connection/Db2.php
===================================================================
--- branches/0.11/lib/Doctrine/Connection/Db2.php       2008-05-17 01:21:57 UTC (rev 4371)
+++ branches/0.11/lib/Doctrine/Connection/Db2.php       2008-05-17 12:01:48 UTC (rev 4372)
@@ -55,8 +55,8 @@
             $col = explode('select', $select);

             $sql = 'WITH OFFSET AS(' . $select . ', ROW_NUMBER() ' .
-               'OVER(ORDER BY ' . $col[1] . ') AS dctrn_rownum FROM ' . $table . ')' .
-               $select . 'FROM OFFSET WHERE dctrn_rownum BETWEEN ' . $offset .
+               'OVER(ORDER BY ' . $col[1] . ') AS doctrine_rownum FROM ' . $table . ')' .
+               $select . 'FROM OFFSET WHERE doctrine_rownum BETWEEN ' . $offset .
                    'AND ' . ($offset + $limit - 1);
             return $sql;
         }

Modified: branches/0.11/lib/Doctrine/Connection/Oracle.php
===================================================================
--- branches/0.11/lib/Doctrine/Connection/Oracle.php    2008-05-17 01:21:57 UTC (rev 4371)
+++ branches/0.11/lib/Doctrine/Connection/Oracle.php    2008-05-17 12:01:48 UTC (rev 4372)
@@ -82,6 +82,11 @@
      */
     public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false)
     {
+        return $this->_createLimitSubquery($query, $limit, $offset);
+    }
+    
+    private function _createLimitSubquery($query, $limit, $offset, $column = null)
+    {
         $limit = (int) $limit;
         $offset = (int) $offset;
         if (preg_match('/^\s*SELECT/i', $query)) {
@@ -89,20 +94,37 @@
                 $query .= " FROM dual";
             }
             if ($limit > 0) {
-                // taken from http://svn.ez.no/svn/ezcomponents/packages/Database
                 $max = $offset + $limit;
+                $column = $column === null ? '*' : $column;
                 if ($offset > 0) {
                     $min = $offset + 1;
-                    $query = 'SELECT b.* FROM (
-                                 SELECT a.*, ROWNUM AS doctrine_rownum FROM ('
-                                  . $query . ') a
-                              ) b
-                              WHERE b.doctrine_rownum BETWEEN ' . $min .  ' AND ' . $max;
+                    $query = 'SELECT b.'.$column.' FROM ('.
+                                 'SELECT a.*, ROWNUM AS doctrine_rownum FROM ('
+                                   . $query . ') a '.
+                              ') b '.
+                              'WHERE doctrine_rownum BETWEEN ' . $min .  ' AND ' . $max;
                 } else {
-                    $query = 'SELECT a.* FROM (' . $query .') a WHERE ROWNUM <= ' . $max;
+                    $query = 'SELECT a.'.$column.' FROM (' . $query .') a WHERE ROWNUM <= ' . $max;
                 }
             }
         }
         return $query;
     }
+    
+    /**
+     * Creates the SQL for Oracle that can be used in the subquery for the limit-subquery
+     * algorithm.
+     */
+    public function modifyLimitSubquery(Doctrine_Table $rootTable, $query, $limit = false,
+            $offset = false, $isManip = false)
+    {
+        // NOTE: no composite key support
+        $columnNames = $rootTable->getIdentifierColumnNames();
+        if (count($columnNames) > 1) {
+            throw new Doctrine_Connection_Exception("Composite keys in LIMIT queries are "
+                    . "currently not supported.");
+        }
+        $column = $columnNames[0];
+        return $this->_createLimitSubquery($query, $limit, $offset, $column);
+    }
 }
\ No newline at end of file

Modified: branches/0.11/lib/Doctrine/Connection.php
===================================================================
--- branches/0.11/lib/Doctrine/Connection.php   2008-05-17 01:21:57 UTC (rev 4371)
+++ branches/0.11/lib/Doctrine/Connection.php   2008-05-17 12:01:48 UTC (rev 4372)
@@ -1503,12 +1503,24 @@
      *
      * Some dbms require specific functionality for this. Check the other connection adapters for examples
      *
-     * @return void
+     * @return string
      */
     public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false)
     {
         return $query;
     }
+    
+    /**
+     * Creates dbms specific LIMIT/OFFSET SQL for the subqueries that are used in the
+     * context of the limit-subquery algorithm.
+     *
+     * @return string
+     */
+    public function modifyLimitSubquery(Doctrine_Table $rootTable, $query, $limit = false,
+            $offset = false, $isManip = false)
+    {
+        return $this->modifyLimitQuery($query, $limit, $offset, $isManip);
+    }

     /**
      * returns a string representation of this object

Modified: branches/0.11/lib/Doctrine/Query.php
===================================================================
--- branches/0.11/lib/Doctrine/Query.php        2008-05-17 01:21:57 UTC (rev 4371)
+++ branches/0.11/lib/Doctrine/Query.php        2008-05-17 12:01:48 UTC (rev 4372)
@@ -1201,7 +1201,6 @@

         $modifyLimit = true;
         if ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) {
-
             if ($needsSubQuery) {
                 $subquery = $this->getLimitSubquery();
                 // what about composite keys?
@@ -1209,7 +1208,7 @@
                 switch (strtolower($this->_conn->getDriverName())) {
                     case 'mysql':
                         // mysql doesn't support LIMIT in subqueries
-                        $list     = $this->_conn->execute($subquery, $params)->fetchAll(Doctrine::FETCH_COLUMN);
+                        $list = $this->_conn->execute($subquery, $params)->fetchAll(Doctrine::FETCH_COLUMN);
                         $subquery = implode(', ', array_map(array($this->_conn, 'quote'), $list));
                         break;
                     case 'pgsql':
@@ -1263,17 +1262,17 @@
      */
     public function getLimitSubquery()
     {
-        $map    = reset($this->_queryComponents);
-        $table  = $map['table'];
+        $map = reset($this->_queryComponents);
+        $table = $map['table'];
         $componentAlias = key($this->_queryComponents);

         // get short alias
-        $alias      = $this->getTableAlias($componentAlias);
+        $alias = $this->getTableAlias($componentAlias);
         // what about composite keys?
         $primaryKey = $alias . '.' . $table->getColumnName($table->getIdentifier());

         // initialize the base of the subquery
-        $subquery   = 'SELECT DISTINCT ' . $this->_conn->quoteIdentifier($primaryKey);
+        $subquery = 'SELECT DISTINCT ' . $this->_conn->quoteIdentifier($primaryKey);

         $driverName = $this->_conn->getAttribute(Doctrine::ATTR_DRIVER_NAME);

@@ -1331,7 +1330,7 @@
         $subquery .= ( ! empty($this->_sqlParts['orderby']))? ' ORDER BY ' . implode(', ', $this->_sqlParts['orderby'])   : '';

         // add driver specific limit clause
-        $subquery = $this->_conn->modifyLimitQuery($subquery, $this->_sqlParts['limit'], $this->_sqlParts['offset']);
+        $subquery = $this->_conn->modifyLimitSubquery($table, $subquery, $this->_sqlParts['limit'], $this->_sqlParts['offset']);

         $parts = $this->_tokenizer->quoteExplode($subquery, ' ', "'", "'");

Modified: branches/0.11/tests/Query/DriverTestCase.php
===================================================================
--- branches/0.11/tests/Query/DriverTestCase.php        2008-05-17 01:21:57 UTC (rev 4371)
+++ branches/0.11/tests/Query/DriverTestCase.php        2008-05-17 12:01:48 UTC (rev 4372)
@@ -99,6 +99,36 @@

         $q->from('User u')->limit(5)->offset(2);

-        $this->assertEqual($q->getSql(), 'SELECT * FROM (SELECT a.*, ROWNUM dctrn_rownum FROM (SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, e.password AS e__password, e.type AS e__type, e.created AS e__created, e.updated AS e__updated, e.email_id AS e__email_id FROM entity e WHERE (e.type = 0)) a WHERE ROWNUM <= 7) WHERE dctrn_rownum >= 3');
+        $this->assertEqual($q->getSql(), 'SELECT b.* FROM (SELECT a.*, ROWNUM AS doctrine_rownum FROM (SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, e.password AS e__password, e.type AS e__type, e.created AS e__created, e.updated AS e__updated, e.email_id AS e__email_id FROM entity e WHERE (e.type = 0)) a ) b WHERE doctrine_rownum BETWEEN 3 AND 7');
     }
+    
+    public function testLimitOffsetLimitSubqueriesForOracle()
+    {
+        $this->dbh = new Doctrine_Adapter_Mock('oracle');
+        $conn = $this->manager->openConnection($this->dbh);
+        $q = new Doctrine_Query($conn);
+        $q->from('User u')->innerJoin('u.Phonenumber p')->limit(5)->offset(2);
+        
+        $correctSql = "SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, "
+                            . "e.password AS e__password, e.type AS e__type, e.created AS e__created, "
+                            . "e.updated AS e__updated, e.email_id AS e__email_id, p.id AS p__id, "
+                            . "p.phonenumber AS p__phonenumber, p.entity_id AS p__entity_id "
+                            . "FROM entity e "
+                            . "INNER JOIN phonenumber p ON e.id = p.entity_id "
+                            . "WHERE e.id IN ("
+                                . "SELECT b.id FROM ("
+                                    . "SELECT a.*, ROWNUM AS doctrine_rownum "
+                                    . "FROM ("
+                                        . "SELECT DISTINCT e2.id "
+                                        . "FROM entity e2 "
+                                        . "INNER JOIN phonenumber p2 ON e2.id = p2.entity_id "
+                                        . "WHERE (e2.type = 0)"
+                                    . ") a"
+                                . " ) b "
+                                . "WHERE doctrine_rownum BETWEEN 3 AND 7"
+                            . ") AND (e.type = 0)";
+        
+        $this->assertEqual($q->getSql(), $correctSql);
+    }
+    
 }