Take a look on the following query:

SELECT   b.nu_seq_usuario AS b__nu_seq_usuario, b.nome AS b__nome,
         b.senha AS b__senha, b.num AS b__num,
         b.dt_cadastro AS b__dt_cadastro, b.nu_seq_equipe AS b__nu_seq_equipe,
         b2.nu_seq_grupo AS b2__nu_seq_grupo, b2.NAME AS b2__name,
         b4.nu_seq_equipe AS b4__nu_seq_equipe, b4.ds_equipe AS b4__ds_equipe
    FROM bprieto.usuario b LEFT JOIN bprieto.usuario_grupo b3
         ON b.nu_seq_usuario = b3.nu_seq_usuario
         LEFT JOIN bprieto.grupos b2 ON b2.nu_seq_grupo = b3.nu_seq_grupo
         LEFT JOIN bprieto.equipe b4 ON b.nu_seq_equipe = b4.nu_seq_equipe
   WHERE b.nu_seq_usuario IN (
            SELECT a.*
              FROM (SELECT DISTINCT b5.nu_seq_usuario, b5.nome
                               FROM bprieto.usuario b5 LEFT JOIN bprieto.usuario_grupo b7
                                    ON b5.nu_seq_usuario = b7.nu_seq_usuario
                                    LEFT JOIN bprieto.grupos b6
                                    ON b6.nu_seq_grupo = b7.nu_seq_grupo
                                    LEFT JOIN bprieto.equipe b8
                                    ON b5.nu_seq_equipe = b8.nu_seq_equipe
                              WHERE 1 = 1
                           ORDER BY b5.nome) a
             WHERE ROWNUM <= 12)
     AND 1 = 1
ORDER BY b.nome, b2.NAME

lets focus on the following part:

WHERE b.nu_seq_usuario IN (
            SELECT a.*
              FROM (SELECT DISTINCT b5.nu_seq_usuario, b5.nome

Its easy to see that SELECT a.* won´t return only the nu_seq_usuario the most outer query is expecting. And this will not work in Oracle, as it will throw a 'too many values' error or something like that. So the only thing we need to do is:

WHERE b.nu_seq_usuario IN (
            SELECT nu_seq_usuario
              FROM (SELECT DISTINCT b5.nu_seq_usuario, b5.nome

That will work. To do that I had to pass the primary key names to the inside of the Doctrine_Connection_Oracle::modifyLimitSubquery function, so instead of:

 $query = 'SELECT b.* FROM (
                                 SELECT a.*, ROWNUM AS doctrine_rownum FROM ('
                                  . $query . ') a
                              ) b
                              WHERE b.doctrine_rownum BETWEEN ' . $min .  ' AND ' . $max;

I have something like...

$query = 'SELECT '.$primaryKeys.' FROM (
                                 SELECT a.*, ROWNUM AS doctrine_rownum FROM ('
                                  . $query . ') a
                              ) b
                              WHERE b.doctrine_rownum BETWEEN ' . $min .  ' AND ' . $max;

and, with this change I had to do nothing with hydration, I think. Please fell free to ask anything else you need. We sure will benefit from this work here in our job.

romanb:

Ok, i'm starting to get it, but this is really a problem of the limit-subquery algorithm on oracle. If you're not familar with the limit-subquery thing, read this:  http://www.phpdoctrine.org/documentation/manual/0_11?one-page#dql-doctrine-query-language:limit-and-offset-clauses:the-limit-subquery-algorithm I see that the modifyLimitQuery does not work well when used inside this algorithm and that's the problem.

Concerning the second problem: We can't just replace b.* with the primary keys, then it'll work fine inside the limit-subquery but not in other regular limit queries.

So, in my eyes, the current modifyLimitQuery of the Oracle connection is absolutely correct, we should not alter it. Instead we may need to override/replace this method with the one you propose only in the limit-subquery creation or do something similar.

All in all, these 2 are really separate issues from this ticket here. If you don't mind, create 2 new tickets and just copy & paste your last description and our last 2 comments so that we get this properly separated.