Doctrine 1
  1. Doctrine 1
  2. DC-594

When using a combination of: a group by field referencing a table in a relation, a join to a different table via a many type relation and a limit clause, doctrine creates a broken query then throws an exception

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 1.2.2
    • Fix Version/s: 1.2.3
    • Component/s: Query, Relations
    • Labels:
      None
    • Environment:
      XP Xamp Current

      Description

      Hi All

      I have run into a very problematic Doctrine 1.2.2 bug which puts me in quite a bit of danger of having to rewrite my whole app in Doctrine 2 (hopefully if this is a bug it isn't present in Doctrine 2).

      The problem I am running into seems like something that probably would have been found and rectified however so hopefully there is something wrong in my execution.

      The problem I am running into is caused when I have a combination of the following 3 things in my query:
      1) A group by field referencing a table in a relation
      2) A join to a different table via a many type relation
      3) A limit clause

      This combination causes Doctrine to create a broken SQL query which it then throws an exception about when I try to execute the query or call getSqlQuery() on it.

      Using some sample data I put together some very simple examples to illustrate the problem:

      $q = Doctrine_Query::create(); 
      $q->from('Customer Customer'); 
      $q->leftJoin('Customer.Order Order'); 
      $q->leftJoin('Customer.Zip Zip'); 
      $q->addGroupBy('Zip.city');
      $q->addSelect('Zip.city as city');
      $q->addSelect('Customer.customer_id'); 
      $q->addSelect('Order.order_id');
      $q->offset(0);
      $q->limit(5);
      

      This creates the following dql:

      SELECT Zip.city as city, Customer.customer_id, Order.order_id FROM Customer Customer LEFT JOIN Customer.Order Order LEFT JOIN Customer.Zip Zip GROUP BY Zip.city LIMIT 5 OFFSET 0

      However when I attempt to run $q->getSqlQuery() an exception is thrown:
      {"type":"exception","tid":3,"exception":{},"message":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'z2.city' in 'group statement'. Failing Query: \"SELECT DISTINCT c2.customer_id FROM customers c2 GROUP BY z2.city LIMIT 5\"","where":"#0 C:\\htdocs\\php_library\\Doctrine-1.2.1\\lib\\Doctrine
      Connection.php(1025): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Mysql), 'SELECT DISTINCT...')\n#1 C:\\htdocs\\php_library\\Doctrine-1.2.1\\lib\\Doctrine
      Query.php(1263): Doctrine_Connection->execute('SELECT DISTINCT...', Array)\n#2 C:\\htdocs\\php_library\\Doctrine-1.2.1\\lib\\Doctrine
      Query.php(1122): Doctrine_Query->buildSqlQuery(true)\n#3............

      As you can see from the SQL in the exception Doctrine is trying to create a query that groups by a field from the Zip table with out first joining to it (SELECT DISTINCT c2.customer_id FROM customers c2 GROUP BY z2.city LIMIT 5).

      I know Doctrine works some magic to get limit statements to work with joins and I suspect that something with in that magic may be broken, but hopefully its something I am doing wrong.

      Take out any one of the 3 things I mentioned I above and everything works fine – the following all work:

      Remove the limit:

      $q = Doctrine_Query::create(); 
      $q->from('Customer Customer'); 
      $q->leftJoin('Customer.Order Order'); 
      $q->leftJoin('Customer.Zip Zip'); 
      $q->addGroupBy('Zip.city'); 
      $q->addSelect('Zip.city as city');
      $q->addSelect('Order.order_id');
      $q->addSelect('Customer.customer_id'); 
      

      Remove the additional join:

      $q = Doctrine_Query::create(); 
      $q->from('Customer Customer'); 
      $q->leftJoin('Customer.Zip Zip'); 
      $q->addGroupBy('Zip.city'); 
      $q->addSelect('Zip.city as city');
      $q->addSelect('Customer.customer_id');
      $q->offset(0);
      $q->limit(5);
      

      Remove the group by:

      $q = Doctrine_Query::create(); 
      $q->from('Customer Customer'); 
      $q->leftJoin('Customer.Order Order'); 
      $q->leftJoin('Customer.Zip Zip'); 
      $q->addSelect('Zip.city as city');
      $q->addSelect('Customer.customer_id'); 
      $q->addSelect('Order.order_id');
      $q->offset(0);
      $q->limit(5);
      

      Its also worth noting that the following changes also stop the problem from happening:
      Changing the relation to the Order table to be a one relation instead of a many relation.
      Changing the group by to a field located in the "from" table (such as: $q->addGroupBy('Customer.customer_id')

      Here are the relevant parts of my sample data schema:

      detect_relations: false
      package: Example
      options:
        type: INNODB
        charset: utf8
      Order:
        tableName: orders
        columns:
          order_id:
            type: integer(4)
            primary: true
            notnull: true
          customer_id:
            type: integer(4)
          order_date: timestamp
        relations:
          OrderItem:
            type: many
            local: order_id
            foreign: order_id
          Customer:
            type: one
            local: customer_id
            foreign: customer_id
        options:
          type: InnoDB
      Customer:
        tableName: customers
        columns:
          customer_id:
            type: integer(4)
            primary: true
            notnull: true
            autoincrement: true
          firstname:
            type: string(45)
          lastname:
            type: string(45)
          streetaddress:
            type: string(45)
          city:
            type: string(45)
          state:
            type: string(45)
          postalcode:
            type: string(45)
        relations:
          Order:
            type: many
            local: customer_id
            foreign: customer_id
          Zip:
            type: one
            local: postalcode
            foreign: postalcode
        options:
          type: InnoDB
      Zip:
        connection: default_schema
        tableName: zips
        columns:
          postalcode:
            type: varchar(30)
            primary: true
          latitude: 'float(10,6)'
          longitude: 'float(10,6)'
          city: string(50)
          state: string(50)
          country: string(50)
          type: string(50)
        relations:
          Customer:
            type: many
            local: postalcode
            foreign: postalcode
      

      Thank for any advice or information you can give me on this.

      Best regards

      Will Ferrer

        Activity

        Hide
        will ferrer added a comment - - edited

        I realized another crucial aspect of the problem. I am using a left join to my Zip table instead of using an inner join. When I change my code to do an inner join it works again:

        $q = Doctrine_Query::create(); 
        $q->from('Customer Customer'); 
        $q->leftJoin('Customer.Order Order'); 
        $q->innerJoin('Customer.Zip Zip'); 
        $q->addGroupBy('Zip.city');
        $q->addSelect('Zip.city as city');
        $q->addSelect('Customer.customer_id'); 
        $q->addSelect('Order.order_id');
        $q->offset(0);
        $q->limit(5);
        

        Thanks much in advance.

        Will Ferrer

        Show
        will ferrer added a comment - - edited I realized another crucial aspect of the problem. I am using a left join to my Zip table instead of using an inner join. When I change my code to do an inner join it works again: $q = Doctrine_Query::create(); $q->from('Customer Customer'); $q->leftJoin('Customer.Order Order'); $q->innerJoin('Customer.Zip Zip'); $q->addGroupBy('Zip.city'); $q->addSelect('Zip.city as city'); $q->addSelect('Customer.customer_id'); $q->addSelect('Order.order_id'); $q->offset(0); $q->limit(5); Thanks much in advance. Will Ferrer
        Hide
        will ferrer added a comment -

        I took a look at the doctrine 1.2.2 code to try to track down what was causing this bug and I think I have found and fixed it in my copy of the code base.

        The problem is on line 1459 of Doctrine_Query and looks like it was just an oversight. The code was checking if it should preserve left joins while generating the subquery based on whether or not there were any orderBys, wheres, or havings added to the query. I changed the code to also watch for groupBys and it seems to have resolved this issue.

        The code was:

         if (empty($this->_sqlParts['orderby']) && empty($this->_sqlParts['where']) && empty($this->_sqlParts['having'])) {
        

        I changed it to:

        if (empty($this->_sqlParts['orderby']) && empty($this->_sqlParts['where']) && empty($this->_sqlParts['having']) && empty($this->_sqlParts['groupby'])) {
        

        Please let me know if I am over looking anything. If I am not then this change should probably be added to the next revision of doctrine.

        Sincerely

        Will Ferrer

        Show
        will ferrer added a comment - I took a look at the doctrine 1.2.2 code to try to track down what was causing this bug and I think I have found and fixed it in my copy of the code base. The problem is on line 1459 of Doctrine_Query and looks like it was just an oversight. The code was checking if it should preserve left joins while generating the subquery based on whether or not there were any orderBys, wheres, or havings added to the query. I changed the code to also watch for groupBys and it seems to have resolved this issue. The code was: if (empty($ this ->_sqlParts['orderby']) && empty($ this ->_sqlParts['where']) && empty($ this ->_sqlParts['having'])) { I changed it to: if (empty($ this ->_sqlParts['orderby']) && empty($ this ->_sqlParts['where']) && empty($ this ->_sqlParts['having']) && empty($ this ->_sqlParts['groupby'])) { Please let me know if I am over looking anything. If I am not then this change should probably be added to the next revision of doctrine. Sincerely Will Ferrer

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            will ferrer
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: