Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1791

Paginator query doesn't work using Oracle

    Details

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

      Description

      Ive build a dql query using the queryBuilder and I've passed the queryBuilder to the Paginator object. The sql query failed.

      here is the sql query that get executed:

      SELECT a.*
      FROM
      (
      SELECT DISTINCT ID0
      FROM
      (
      SELECT
      f0_.id AS ID0, f0_.title AS TITLE1,
      f1_.id AS ID2,
      f2_.id AS ID3, f2_.first_name AS FIRST_NAME4, f2_.middle_name AS MIDDLE_NAME5, f2_.last_name AS LAST_NAME6, f2_.phone AS PHONE7, f2_.url AS URL8,
      f2_.email AS EMAIL9, f2_.profile AS PROFILE10, f2_.created_date AS CREATED_DATE11, f2_.last_updated_date AS LAST_UPDATED_DATE12,
      f2_.object_status_id AS OBJECT_STATUS_ID13, f2_.published_date AS PUBLISHED_DATE14, f2_.publishing_status_id AS PUBLISHING_STATUS_ID15,
      f3_.id AS ID16, f3_.title AS TITLE17, f3_.start_date AS START_DATE18, f3_.end_date AS END_DATE19,
      f4_.id AS ID20, f4_.name AS NAME21,
      f5_.id AS ID22,
      c6_.id AS ID23,
      d7_.id AS ID24
      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_position f3_ ON f2_.id = f3_.people_id
      INNER JOIN fo_company f4_ ON f3_.company_id = f4_.id
      LEFT JOIN fo_people_asset f5_ ON f2_.id = f5_.people_id
      LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id
      LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id
      WHERE f2_.object_status_id <> 3
      AND f2_.publishing_status_id = 2
      ORDER BY f0_.published_date DESC
      ) AS _dctrn_result
      ) a
      WHERE ROWNUM <= 3

      It seems that oracle doesn't accept the AS _dctrn_result. if I remove the AS _dctrn_result in the query, the query works and I get the proper information.

        Activity

        Hide
        Marc Drolet added a comment -

        Here is the error message sent by oracle:
        ORA-00907: missing right parenthesis
        00907. 00000 - "missing right parenthesis"
        *Cause:
        *Action:
        Error at Line: 29 Column: 4

        Also, if I remove the keyword AS, and let the _dctrn_result, I get the error:
        ORA-00911: invalid character
        00911. 00000 - "invalid character"
        *Cause: identifiers may not start with any ASCII character other than
        letters and numbers. $#_ are also allowed after the first
        character. Identifiers enclosed by doublequotes may contain
        any character other than a doublequote. Alternative quotes
        (q'#...#') cannot use spaces, tabs, or carriage returns as
        delimiters. For all other contexts, consult the SQL Language
        Reference Manual.
        *Action:
        Error at Line: 29 Column: 4

        So, If I remove the _ from the alias it work.

        Show
        Marc Drolet added a comment - Here is the error message sent by oracle: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause: *Action: Error at Line: 29 Column: 4 Also, if I remove the keyword AS, and let the _dctrn_result, I get the error: ORA-00911: invalid character 00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. *Action: Error at Line: 29 Column: 4 So, If I remove the _ from the alias it work.
        Hide
        Marc Drolet added a comment -

        here is the fix I've done on my local environment:

        ORM/Tools/Pagination/LimitSubqueryOutputWalker.php line 126

        I've remove the AS _dctrn_result alias from the query.

        I've grep for dctrn_result into the ORM directory and I didn't find any occurence of dctrn_result, so it's not used anywhere.

        Show
        Marc Drolet added a comment - here is the fix I've done on my local environment: ORM/Tools/Pagination/LimitSubqueryOutputWalker.php line 126 I've remove the AS _dctrn_result alias from the query. I've grep for dctrn_result into the ORM directory and I didn't find any occurence of dctrn_result, so it's not used anywhere.
        Hide
        Marc Drolet added a comment - - edited

        I would also point out the the result that I get is not correct when we use an order by clause.

        To get the correct result when an order by clause is provided, I need to add another level of query.
        here is the query that need to be executed to get the result in the proper order by clause.

        SELECT a.*
        FROM
        (
        select b.*, rownum as rn
        from
        (
        SELECT DISTINCT ID0
        FROM
        (
        SELECT
        f0_.id AS ID0, f0_.title AS TITLE1,
        f1_.id AS ID2,
        f2_.id AS ID3, f2_.first_name AS FIRST_NAME4, f2_.middle_name AS MIDDLE_NAME5, f2_.last_name AS LAST_NAME6, f2_.phone AS PHONE7, f2_.url AS URL8,
        f2_.email AS EMAIL9, f2_.profile AS PROFILE10, f2_.created_date AS CREATED_DATE11, f2_.last_updated_date AS LAST_UPDATED_DATE12,
        f2_.object_status_id AS OBJECT_STATUS_ID13, f2_.published_date AS PUBLISHED_DATE14, f2_.publishing_status_id AS PUBLISHING_STATUS_ID15,
        f3_.id AS ID16, f3_.title AS TITLE17, f3_.start_date AS START_DATE18, f3_.end_date AS END_DATE19,
        f4_.id AS ID20, f4_.name AS NAME21,
        f5_.id AS ID22,
        c6_.id AS ID23,
        d7_.id AS ID24
        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_position f3_ ON f2_.id = f3_.people_id
        INNER JOIN fo_company f4_ ON f3_.company_id = f4_.id
        LEFT JOIN fo_people_asset f5_ ON f2_.id = f5_.people_id
        LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id
        LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id
        WHERE f2_.object_status_id <> 3
        AND f2_.publishing_status_id = 2
        ORDER BY f0_.deal_date DESC, f0_.published_date DESC
        ) dctrn_result
        ) b
        ) a
        WHERE a.rn <= 3

        Show
        Marc Drolet added a comment - - edited I would also point out the the result that I get is not correct when we use an order by clause. To get the correct result when an order by clause is provided, I need to add another level of query. here is the query that need to be executed to get the result in the proper order by clause. SELECT a.* FROM ( select b.*, rownum as rn from ( SELECT DISTINCT ID0 FROM ( SELECT f0_.id AS ID0, f0_.title AS TITLE1, f1_.id AS ID2, f2_.id AS ID3, f2_.first_name AS FIRST_NAME4, f2_.middle_name AS MIDDLE_NAME5, f2_.last_name AS LAST_NAME6, f2_.phone AS PHONE7, f2_.url AS URL8, f2_.email AS EMAIL9, f2_.profile AS PROFILE10, f2_.created_date AS CREATED_DATE11, f2_.last_updated_date AS LAST_UPDATED_DATE12, f2_.object_status_id AS OBJECT_STATUS_ID13, f2_.published_date AS PUBLISHED_DATE14, f2_.publishing_status_id AS PUBLISHING_STATUS_ID15, f3_.id AS ID16, f3_.title AS TITLE17, f3_.start_date AS START_DATE18, f3_.end_date AS END_DATE19, f4_.id AS ID20, f4_.name AS NAME21, f5_.id AS ID22, c6_.id AS ID23, d7_.id AS ID24 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_position f3_ ON f2_.id = f3_.people_id INNER JOIN fo_company f4_ ON f3_.company_id = f4_.id LEFT JOIN fo_people_asset f5_ ON f2_.id = f5_.people_id LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id WHERE f2_.object_status_id <> 3 AND f2_.publishing_status_id = 2 ORDER BY f0_.deal_date DESC, f0_.published_date DESC ) dctrn_result ) b ) a WHERE a.rn <= 3
        Hide
        Marc Drolet added a comment - - edited

        I've fix the limit order by issue (for oracle only)

        here is the change I've made to the file: ORM/Tools/Pagination/LimitSubqueryOutputWalker.php
        method: walkSelectStatement
        starting at lime 129.

        I've replace this code:
        $sql = $this->platform->modifyLimitQuery(
        $sql, $this->maxResults, $this->firstResult
        );

        for this one:
        if ($this->firstResult > 0)

        { $sql = 'SELECT * FROM ( SELECT a.*, rownum AS doctrine_rownum FROM (' . $sql . ') a WHERE a.rn >= ' . ($this->firstResult + 1) . ' ) WHERE doctrine_rownum >= ' . $this->maxResults; }

        else

        { $sql = 'SELECT a.* FROM (' . $sql . ') a WHERE a.rn <= ' . $this->maxResults; }
        Show
        Marc Drolet added a comment - - edited I've fix the limit order by issue (for oracle only) here is the change I've made to the file: ORM/Tools/Pagination/LimitSubqueryOutputWalker.php method: walkSelectStatement starting at lime 129. I've replace this code: $sql = $this->platform->modifyLimitQuery( $sql, $this->maxResults, $this->firstResult ); for this one: if ($this->firstResult > 0) { $sql = 'SELECT * FROM ( SELECT a.*, rownum AS doctrine_rownum FROM (' . $sql . ') a WHERE a.rn >= ' . ($this->firstResult + 1) . ' ) WHERE doctrine_rownum >= ' . $this->maxResults; } else { $sql = 'SELECT a.* FROM (' . $sql . ') a WHERE a.rn <= ' . $this->maxResults; }
        Hide
        Marc Drolet added a comment - - edited

        The countOutputWalker also need to be modified.

        file: Doctrine/ORM/Tools/Pagination/CountOutputWalker.php
        method: walkSelectStatement

        for oracle it doesn't work:
        return sprintf('SELECT %s AS _dctrn_count FROM (SELECT DISTINCT %s FROM (%s) AS _dctrn_result) AS _dctrn_table',

        need to be changed for:
        return sprintf('SELECT %s AS DCTRN_COUNT FROM (SELECT DISTINCT %s FROM (%s) DCTRN_RESULT) DCTRN_TABLE',

        I want to point out the I've change the alias to UPPER CASE, cause oracle return the array result with field names in upper case.
        This affect the count method of the file Doctrine/ORM/Tools/Pagination/Paginator.php that need to be modified.

        file: Doctrine/ORM/Tools/Pagination/Paginator.php
        method: count
        line: 131 change $rsm->addScalarResult('_dctrn_count', 'count'); for $rsm->addScalarResult('DCTRN_COUNT', 'count');

        Show
        Marc Drolet added a comment - - edited The countOutputWalker also need to be modified. file: Doctrine/ORM/Tools/Pagination/CountOutputWalker.php method: walkSelectStatement for oracle it doesn't work: return sprintf('SELECT %s AS _dctrn_count FROM (SELECT DISTINCT %s FROM (%s) AS _dctrn_result) AS _dctrn_table', need to be changed for: return sprintf('SELECT %s AS DCTRN_COUNT FROM (SELECT DISTINCT %s FROM (%s) DCTRN_RESULT) DCTRN_TABLE', I want to point out the I've change the alias to UPPER CASE, cause oracle return the array result with field names in upper case. This affect the count method of the file Doctrine/ORM/Tools/Pagination/Paginator.php that need to be modified. file: Doctrine/ORM/Tools/Pagination/Paginator.php method: count line: 131 change $rsm->addScalarResult('_dctrn_count', 'count'); for $rsm->addScalarResult('DCTRN_COUNT', 'count');
        Hide
        Benjamin Eberlei added a comment -

        Fixed, you are not using 2.2.2 btw, this code is only in master.

        Show
        Benjamin Eberlei added a comment - Fixed, you are not using 2.2.2 btw, this code is only in master.

          People

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

            Dates

            • Created:
              Updated:
              Resolved: