In SQL query, field name is not replaced with real column name in right part of join condition

Hi,
I'm blocked on a bad issue

I have a schema that uses fields aliases:

User:

Phonenumber:
  columns:
    theuser:
      name: colprefix_user_id as user_id   #note the column alias
      type: integer
    thenumber:
      name: colprefix_number as number
      type: string(20)
  relations:
    User:
      local: user_id
      foreign: id
      foreignAlias: Phonenumbers

Now if I make the following 2 DQL queries, the first "plain" and the second with redefining the ON clause, then print generated SQL:

$q1 = Doctrine_Query::create()
    ->from('User u')
    ->innerJoin('u.Phonenumbers p');

$q2 = Doctrine_Query::create()
    ->from('User u')
    ->innerJoin('u.Phonenumbers p ON u.id = p.user_id');

echo $q1->getSqlQuery() . "\n";
echo $q2->getSqlQuery() . "\n";

I get (lines formatted a little):

SELECT u.id AS u__id, p.id AS p__id, p.colprefix_user_id AS p__colprefix_user_id, p.colprefix_number AS p__colprefix_number
        FROM user u INNER JOIN phonenumber p ON u.id = p.colprefix_user_id

SELECT u.id AS u__id, p.id AS p__id, p.colprefix_user_id AS p__colprefix_user_id, p.colprefix_number AS p__colprefix_number
        FROM user u INNER JOIN phonenumber p ON (u.id = p.user_id)

Note how, on the right end, p.user_id is not replaced with p.colprefix_user_id in the 2nd query!
If I try to execute $q2 I get

This only happens for the right member of the join condition. If i swap the fields like this:

$q3 = Doctrine_Query::create()
    ->from('User u')
    ->innerJoin('u.Phonenumbers p ON p.user_id = u.id'); //swapped

echo $q3->getSqlQuery() . "\n";

then we can see that the left member is replaced well:

SELECT u.id AS u__id, p.id AS p__id, p.colprefix_user_id AS p__colprefix_user_id, p.colprefix_number AS p__colprefix_number
        FROM user u INNER JOIN phonenumber p ON (p.colprefix_user_id = u.id)

(This is only an example, there's no point redefining the ON clause here, but the same is occurring in the project I work on, in a WITH clause).

I think the problem is in Doctrine_Query_JoinCondition::load(), in which

• the left member of condition is directly parsed in a call to Doctrine_Query::parseClause();
• whereas the right member is checked if it's a subquery, and if not, is transformed into the result of Doctrine_Expression::getSql() then passed to Doctrine_Query_JoinCondition::parseLiteralValue() or Doctrine_Query::parseClause() if it is an SQL function...

This can also produce weird results with SQL functions:

$q4 = Doctrine_Query::create()
    ->from('User u')
    ->innerJoin('u.Phonenumbers p WITH SUBSTRING(p.number,0,3) = \'555\''); //left

$q5 = Doctrine_Query::create()
    ->from('User u')
    ->innerJoin('u.Phonenumbers p WITH \'555\' = SUBSTRING(p.number,0,3)'); //right

echo $q4->getSqlQuery() . "\n";
echo $q5->getSqlQuery() . "\n";

Output:

SELECT u.id AS u__id, p.id AS p__id, p.colprefix_user_id AS p__colprefix_user_id, p.colprefix_number AS p__colprefix_number
        FROM user u INNER JOIN phonenumber p ON u.id = p.colprefix_user_id AND (SUBSTRING(p.colprefix_number FROM 0 FOR 3) = '555')

SELECT u.id AS u__id, p.id AS p__id, p.colprefix_user_id AS p__colprefix_user_id, p.colprefix_number AS p__colprefix_number
        FROM user u INNER JOIN phonenumber p ON u.id = p.colprefix_user_id AND ('555' = SUBSTRING(p.colprefix_number FROM 0 FOR 3 FROM ))

Now, the correct column name is always used, but in the 2nd case the "SUBSTRING" function looks like it has been parsed twice :/

I think both members (left and right) of condition should be treated equally (but I have no fix for now... :s)

Thank you