Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1800

Paginator results is wrong if your query use order by clause

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.2
    • Fix Version/s: 2.2.4, 2.3
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None
    • Environment:
      linux oracle

      Description

      NOTE: I didn't try this on other database, I'm using Oracle.

      if my original fetchJoin query use an order by clause, the results is not keeping the provided order by clause and re-order them by id.

      here is my generated query to get the distinct records that get generated:

      SELECT distinct ID0
      FROM
      (
      SELECT f0_.id AS ID0, f0_.deal_type_id AS DEAL_TYPE_ID1, f0_.title AS TITLE2, f0_.deal_date AS DEAL_DATE3, f0_.amount AS AMOUNT4,
      f0_.abstract AS ABSTRACT5, f0_.created_date AS CREATED_DATE6, f0_.last_updated_date AS LAST_UPDATED_DATE7,
      f0_.object_status_id AS OBJECT_STATUS_ID8, f0_.published_date AS PUBLISHED_DATE9, f0_.publishing_status_id AS PUBLISHING_STATUS_ID10,
      f1_.id AS ID11, f1_.role_id AS ROLE_ID12, f1_.role_type_id AS ROLE_TYPE_ID13, f2_.id AS ID14, f3_.id AS ID15, f4_.id AS ID16, f5_.id AS ID17,
      c6_.id AS ID18, d7_.id AS ID19
      FROM fo_deal f0_
      INNER JOIN fo_deal_role f1_ ON f0_.id = f1_.deal_id
      INNER JOIN fo_people f2_ ON f1_.people_id = f2_.id
      INNER JOIN fo_property_deal f3_ ON f0_.id = f3_.deal_id
      INNER JOIN fo_property f4_ ON f3_.property_id = f4_.id
      LEFT JOIN fo_property_asset f5_ ON f4_.id = f5_.property_id
      LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id
      LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id
      WHERE f1_.people_id = 2
      AND f0_.object_status_id <> 3
      AND f0_.publishing_status_id = 2
      ORDER BY f0_.deal_date DESC, f0_.published_date DESC
      )

      running this query I get the id 30, 44 when the inner query return 44, 30

      here is the query that should get generated to take care of the order by clause:
      SELECT distinct ID0, rownum+
      FROM
      (
      SELECT f0_.id AS ID0, f0_.deal_type_id AS DEAL_TYPE_ID1, f0_.title AS TITLE2, f0_.deal_date AS DEAL_DATE3, f0_.amount AS AMOUNT4,
      f0_.abstract AS ABSTRACT5, f0_.created_date AS CREATED_DATE6, f0_.last_updated_date AS LAST_UPDATED_DATE7,
      f0_.object_status_id AS OBJECT_STATUS_ID8, f0_.published_date AS PUBLISHED_DATE9, f0_.publishing_status_id AS PUBLISHING_STATUS_ID10,
      f1_.id AS ID11, f1_.role_id AS ROLE_ID12, f1_.role_type_id AS ROLE_TYPE_ID13, f2_.id AS ID14, f3_.id AS ID15, f4_.id AS ID16, f5_.id AS ID17,
      c6_.id AS ID18, d7_.id AS ID19
      FROM fo_deal f0_
      INNER JOIN fo_deal_role f1_ ON f0_.id = f1_.deal_id
      INNER JOIN fo_people f2_ ON f1_.people_id = f2_.id
      INNER JOIN fo_property_deal f3_ ON f0_.id = f3_.deal_id
      INNER JOIN fo_property f4_ ON f3_.property_id = f4_.id
      LEFT JOIN fo_property_asset f5_ ON f4_.id = f5_.property_id
      LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id
      LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id
      WHERE f1_.people_id = 2
      AND f0_.object_status_id <> 3
      AND f0_.publishing_status_id = 2
      ORDER BY f0_.deal_date DESC, f0_.published_date DESC
      ) ORDER BY rownum ASC

      To fix this on the Paginator code:

      file: ORM/Tools/Pagination/LimitSubqueryOutputWalker.php
      method: walkSelectStatement

      change:
      $sql = sprintf('SELECT b.*, rownum as rn FROM (SELECT DISTINCT %s FROM (%s)) b', // AS _dctrn_result',
      implode(', ', $sqlIdentifier), $sql);

      for:
      $sql = sprintf('SELECT b.*, rownum as rn FROM (SELECT DISTINCT %s, numrow FROM (%s) ORDER BY numrow ASC) b', // AS _dctrn_result',
      implode(', ', $sqlIdentifier), $sql);

        Activity

        Hide
        Marc Drolet added a comment -

        rownum instead of numrow. sorry.

        $sql = sprintf('SELECT b.*, rownum as rn FROM (SELECT DISTINCT %s, rownum FROM (%s) ORDER BY rownum ASC) b',
        implode(', ', $sqlIdentifier), $sql);

        Show
        Marc Drolet added a comment - rownum instead of numrow. sorry. $sql = sprintf('SELECT b.*, rownum as rn FROM (SELECT DISTINCT %s, rownum FROM (%s) ORDER BY rownum ASC) b', implode(', ', $sqlIdentifier), $sql);
        Hide
        Benjamin Eberlei added a comment -

        Doctrine 2.2.2 doesnt have the LimitSubqueryoutputWalker and Doctrine 2.3-dev does not have the line in the code. Can you make a more explicit statement of where the change is necessary?

        Show
        Benjamin Eberlei added a comment - Doctrine 2.2.2 doesnt have the LimitSubqueryoutputWalker and Doctrine 2.3-dev does not have the line in the code. Can you make a more explicit statement of where the change is necessary?
        Hide
        Marc Drolet added a comment -

        It's in the Pagination of version 2.2.2. ORM/Tools/Pagination/LimitSubqueryOutputWalker.php

        Show
        Marc Drolet added a comment - It's in the Pagination of version 2.2.2. ORM/Tools/Pagination/LimitSubqueryOutputWalker.php
        Hide
        Benjamin Eberlei added a comment -
        Show
        Benjamin Eberlei added a comment - This is the 2.2 branch, https://github.com/doctrine/doctrine2/tree/2.2/lib/Doctrine/ORM/Tools/Pagination and https://github.com/doctrine/doctrine2/tree/2.2.2/lib/Doctrine/ORM/Tools/Pagination is the 2.2.2 tag. no LimitSubqueryOutputWalker.php in there.
        Hide
        Benjamin Eberlei added a comment -

        Fixed

        Show
        Benjamin Eberlei added a comment - Fixed
        Hide
        Raymond Kolbe added a comment -

        This issue is popping it's head up again!

        Benjamin, your tests don't test for the ordering problem unless those tests are happening somewhere else?

        https://github.com/doctrine/doctrine2/commit/f55b5411c8b1f75bf2b5cf5ffe4bc50034fb91cb

        I am performing a query as complex as Marc's and I experience the same exact issue. I have checked out today's latest master branch as well as the 2.3 tag with no change.

        Please advise.

        Show
        Raymond Kolbe added a comment - This issue is popping it's head up again! Benjamin, your tests don't test for the ordering problem unless those tests are happening somewhere else? https://github.com/doctrine/doctrine2/commit/f55b5411c8b1f75bf2b5cf5ffe4bc50034fb91cb I am performing a query as complex as Marc's and I experience the same exact issue. I have checked out today's latest master branch as well as the 2.3 tag with no change. Please advise.
        Hide
        Raymond Kolbe added a comment -
        Show
        Raymond Kolbe added a comment - I have a PR in https://github.com/doctrine/doctrine2/pull/645

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Marc Drolet
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: