Doctrine 1
  1. Doctrine 1
  2. DC-701

Aggregates functions do not return proper values when using many relationships and limits

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Blocker Blocker
    • Resolution: Unresolved
    • Affects Version/s: 1.2.2
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:
      XP Xamp

      Description

      Hi All

      I have encountered a problem that seems very core to the way that doctrine works – if you apply an aggregate function to a column in a table and then join to another table via a many relationship while also using a limit, like so:

      $q = Doctrine_Query::create();
      $q->from('Customer Customer'); 
      $q->addSelect('COUNT(Customer.id) as COUNT_customer_id');
      $q->addSelect('Customer_Order.id as order_id'); 
      $q->leftJoin('Customer.Order Customer_Order'); // Many relationship here
      $q->limit(20);
      

      It produces this correct DQL:

      SELECT COUNT(Customer.id) as COUNT_customer_id, Customer_Order.id as order_id 
      FROM Customer Customer 
        LEFT JOIN Customer.Order Customer_Order 
      LIMIT 20
      

      However the SQL it produces will not return an accurate count – the count is restricted by the limit:

      SELECT COUNT(p.id) AS p__0, p2.id AS p2__1 
      FROM product_customers p 
        LEFT JOIN product_orders p2 ON p.id = p2.customer_id 
      WHERE p.id IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20')
      

      This produces a count of 21 instead of what it should be (1000).

      The reason for this is because Doctrine's internal functionality does an intermediary query where it gets gets the ids needed from the customer table in order to use them as the IN portion of the constraints on the final query – like so:

      SELECT DISTINCT p3.id FROM product_customers p3 LIMIT 20
      

      It may seem strange that I am applying a limit to a query which will aggregate to 1 to row . In the actual queries that alerted me to this problem I am using a group by. The reason I am not reporting this bug with a group by in my example however is that you can not use a group by with a many relationship and a limit in doctrine 1.2.2 as it works currently (see bug: DC-594). However I am personally able to use a limit with a group by and many relationship since I am using a version of the code I patched my self to repair bug DC-594.

      Here is my sample schema in case its helpful.

      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:
          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
        options:
          type: InnoDB
      

      Thanks much.

      Will Ferrer

      edit: split SQL code to make the discussion readable without huge horizontal scrolling...

        Activity

        will ferrer created issue -
        Jonathan H. Wage made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Can't Fix [ 7 ]
        will ferrer made changes -
        Attachment disableLimitSubquery_2010-06-10_Doctrine_1.2_SVN.patch [ 10637 ]
        Attachment disableLimitSubquery_and_HYDRATE_ARRAY_SHALLOW_2010-06-10_Doctrine_1.2_SVN.patch [ 10638 ]
        will ferrer made changes -
        Resolution Can't Fix [ 7 ]
        Status Resolved [ 5 ] Reopened [ 4 ]
        Guilliam X made changes -
        Description Hi All

        I have encountered a problem that seems very core to the way that doctrine works -- if you apply an aggregate function to a column in a table and then join to another table via a many relationship while also using a limit, like so:

        {code}
        $q = Doctrine_Query::create();
        $q->from('Customer Customer');
        $q->addSelect('COUNT(Customer.id) as COUNT_customer_id');
        $q->addSelect('Customer_Order.id as order_id');
        $q->leftJoin('Customer.Order Customer_Order'); // Many relationship here
        $q->limit(20);
        {code}

        It produces this correct DQL:
        {code}
        SELECT COUNT(Customer.id) as COUNT_customer_id, Customer_Order.id as order_id FROM Customer Customer LEFT JOIN Customer.Order Customer_Order LIMIT 20
        {code}

        However the SQL it produces will not return an accurate count -- the count is restricted by the limit:

        {code}
        SELECT COUNT(p.id) AS p__0, p2.id AS p2__1 FROM product_customers p LEFT JOIN product_orders p2 ON p.id = p2.customer_id WHERE p.id IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20')
        {code}

        This produces a count of 21 instead of what it should be (1000).

        The reason for this is because Doctrine's internal functionality does an intermediary query where it gets gets the ids needed from the customer table in order to use them as the IN portion of the constraints on the final query -- like so:

        {code}
        SELECT DISTINCT p3.id FROM product_customers p3 LIMIT 20
        {code}

        It may seem strange that I am applying a limit to a query which will aggregate to 1 to row . In the actual queries that alerted me to this problem I am using a group by. The reason I am not reporting this bug with a group by in my example however is that you can not use a group by with a many relationship and a limit in doctrine 1.2.2 as it works currently (see bug: DC-594). However I am personally able to use a limit with a group by and many relationship since I am using a version of the code I patched my self to repair bug DC-594.

        Here is my sample schema in case its helpful.

        {code}
        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:
            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
          options:
            type: InnoDB
        {code}

        Thanks much.

        Will Ferrer
        Hi All

        I have encountered a problem that seems very core to the way that doctrine works -- if you apply an aggregate function to a column in a table and then join to another table via a many relationship while also using a limit, like so:

        {code}
        $q = Doctrine_Query::create();
        $q->from('Customer Customer');
        $q->addSelect('COUNT(Customer.id) as COUNT_customer_id');
        $q->addSelect('Customer_Order.id as order_id');
        $q->leftJoin('Customer.Order Customer_Order'); // Many relationship here
        $q->limit(20);
        {code}

        It produces this correct DQL:
        {code}
        SELECT COUNT(Customer.id) as COUNT_customer_id, Customer_Order.id as order_id
        FROM Customer Customer
          LEFT JOIN Customer.Order Customer_Order
        LIMIT 20
        {code}

        However the SQL it produces will not return an accurate count -- the count is restricted by the limit:

        {code}
        SELECT COUNT(p.id) AS p__0, p2.id AS p2__1
        FROM product_customers p
          LEFT JOIN product_orders p2 ON p.id = p2.customer_id
        WHERE p.id IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20')
        {code}

        This produces a count of 21 instead of what it should be (1000).

        The reason for this is because Doctrine's internal functionality does an intermediary query where it gets gets the ids needed from the customer table in order to use them as the IN portion of the constraints on the final query -- like so:

        {code}
        SELECT DISTINCT p3.id FROM product_customers p3 LIMIT 20
        {code}

        It may seem strange that I am applying a limit to a query which will aggregate to 1 to row . In the actual queries that alerted me to this problem I am using a group by. The reason I am not reporting this bug with a group by in my example however is that you can not use a group by with a many relationship and a limit in doctrine 1.2.2 as it works currently (see bug: DC-594). However I am personally able to use a limit with a group by and many relationship since I am using a version of the code I patched my self to repair bug DC-594.

        Here is my sample schema in case its helpful.

        {code}
        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:
            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
          options:
            type: InnoDB
        {code}

        Thanks much.

        Will Ferrer

        ~edit: split SQL code to make the discussion readable without huge horizontal scrolling...~
        Jonathan H. Wage made changes -
        Status Reopened [ 4 ] Resolved [ 5 ]
        Fix Version/s 1.2.4 [ 10063 ]
        Resolution Fixed [ 1 ]
        will ferrer made changes -
        will ferrer made changes -
        Attachment disableLimitSubquery_2010-06-10_Doctrine_1.2_SVN.patch [ 10637 ]
        will ferrer made changes -
        Attachment disableLimitSubquery_and_HYDRATE_ARRAY_SHALLOW_2010-06-10_Doctrine_1.2_SVN.patch [ 10638 ]
        will ferrer made changes -
        will ferrer made changes -
        Resolution Fixed [ 1 ]
        Status Resolved [ 5 ] Reopened [ 4 ]
        Guilherme Blanco made changes -
        Fix Version/s 1.2.4 [ 10063 ]

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            will ferrer
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: