Can you provide the code you are using? and show the SQL generated that is causing you problems.

return $query

->select('s.id,s.user_id,s.heading,s.description,s.created_at,s.end_date, sc.consensus_rate as highest_rate') ->from('Speculation s') ->innerJoin('s.User u') ->innerJoin('s.Category cat') ->innerJoin('s.SpeculationTag? st') ->innerJoin('st.Tag t') ->leftJoin('u.Avatar a') ->leftJoin('s.SpeculationConsensus? sc')

->addWhere('sc.id = (SELECT MAX(sc2.id) FROM SpeculationConsensus? sc2 WHERE sc2.speculation_id = s.id)');

This is my query code. in last addWhere() i am using sub query. in sub-query the aliases assigned to sc2 is the same as it was assigned to "sc" one statement before in ->leftJoin('s.SpeculationConsensus? sc') .. So the result gets wrong

my final purpose is that when query renders its should assign new alias to subquery's table

The generated query is

SELECT DISTINCT s4.id FROM speculation s4 INNER JOIN user u2 ON s4.user_id = u2.id INNER JOIN category c2 ON s4.category_id = c2.id INNER JOIN speculation_tag s5 ON s4.id = s5.speculation_id INNER JOIN tag t2 ON s5.tag_id = t2.id LEFT JOIN file f2 ON u2.avatar = f2.id LEFT JOIN speculation_consensus s6 ON s4.id = s6.speculation_id WHERE s4.end_date > "2008-09-17 00:00:00" AND s6.id = (SELECT MAX(s4.id) AS s40 FROM speculation_consensus s4 WHERE s4.speculation_id = s4.id) AND s4.status IN (?) AND s4.deleted_at IS NULL ORDER BY s4.end_date ASC LIMIT 2 - (0 )

here you can see the aliases

waiting for ur reply

Committed coverage in r4983

One way to fix this is to change the code in copyAliases() to this:

    public function copyAliases(Doctrine_Query_Abstract $query)
    {
        $this->_tableAliasMap =& $query->_tableAliasMap;
        $this->_queryComponents = $query->_queryComponents;
        $this->_tableAliasSeeds = $query->_tableAliasSeeds;
        return $this;
    }

The problem is that if the subquery increments one of the aliases then the main query isn't aware of it and tries to generate the same alias that the subquery already generated. The above code means the query and subquery share the same table alias map.

This also fixes #1254

Roman, can you give your opinion on this and reassign the ticket back to me. Thanks, Jon

This is related to #1480. Same type of issue where the subquery information needs to be made available to the main query.

In r5001 this issue was fixed. Thanks for the excellent ticket.