Doctrine 1
  1. Doctrine 1
  2. DC-757

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

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.2
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      Doctrine SVN 1.2 r7676

      Description

      Hi,
      I'm blocked on a bad issue

      I have a schema that uses fields aliases:

      schema.yml
      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

      Fatal error: Uncaught exception 'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'p.user_id' in 'on clause'' in /[...]/doctrine_test/lib/vendor/doctrine/Doctrine/Connection.php:1082

      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

        Activity

        Hide
        Guilliam X added a comment -

        Waiting for better, I attach the patch I used;
        but it's not a good one, as it only "solves" the field name problem but not the SUBSTRING one, and is more a patch than a fix... (sorry I don't feel to refactor Doctrine_Query_JoinCondition::load() :s)

        Show
        Guilliam X added a comment - Waiting for better, I attach the patch I used; but it's not a good one, as it only "solves" the field name problem but not the SUBSTRING one, and is more a patch than a fix... (sorry I don't feel to refactor Doctrine_Query_JoinCondition::load() :s)

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Guilliam X
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: