Doctrine 1
  1. Doctrine 1
  2. DC-278

Invalid qubquery generated if using oracle adapter instead of pdo_oci

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: 1.2.0
    • Component/s: None
    • Labels:
      None
    • Environment:
      Linux, Oracle 10g

      Description

      Doctrine generates the following invalid subquery if oracle adapter is used (same query is generated corret if pdo_oci is used):

      SELECT DISTINCT "s2"."code"
      FROM "spend_category" "s2"
      INNER JOIN "company_spend_category" "c4" ON ("s2"."code" = "c4"."spendcategory_code")
      INNER JOIN "company" "c3" ON "c3"."code" = "c4"."company_code"
      WHERE "s2"."code" = '19000000'
      AND "c3"."is_deleted" IS NOT NULL
      AND "s2"."is_deleted" IS NOT NULL
      ORDER BY "c3"."name" desc

      The problem is that the order by column is not part of the selected columns.

      Sample dql statement to reproduce this error:

      $this->pageAllPager = new Doctrine_Pager(
      $q = Doctrine_Query::create()
      ->select('c.code, c.name, c.is_activated, c.is_deleted, sc.code')
      ->from('SpendCategory sc')
      ->innerJoin('sc.Companies c')
      ->where("sc.code = '$spendcategory'")
      ->addWhere('c.is_deleted IS NOT NULL')
      ->addWhere('sc.is_deleted IS NOT NULL')
      ->orderBy($orderBy)
      ->setHydrationMode(Doctrine::HYDRATE_ARRAY),
      $page,
      $resultsPerPage);
      }

        Activity

        Thomas Wahle created issue -
        Hide
        Jonathan H. Wage added a comment -

        I believe this is fixed now in the latest Doctrine 1.2. Can you test and confirm?

        Show
        Jonathan H. Wage added a comment - I believe this is fixed now in the latest Doctrine 1.2. Can you test and confirm?
        Jonathan H. Wage made changes -
        Field Original Value New Value
        Status Open [ 1 ] Closed [ 6 ]
        Fix Version/s 1.2.0-RC1 [ 10041 ]
        Resolution Fixed [ 1 ]
        Hide
        Thomas Wahle added a comment -

        It is not fixed with 1.2 RC1

        Same sql wich work fine with pdo_oci generates an error using the oracle adapter

        <p>
        <b>Message:</b> ORA-01791: Kein mit SELECT ausgewählter Ausdruck : SELECT s.code AS s_code, c.code AS ccode, c.name AS cname, c.is_activated AS cis_activated, c.is_deleted AS c_is_deleted FROM spend_category s INNER JOIN company_spend_category c2 ON (s.code = c2.spendcategory_code) INNER JOIN company c ON c.code = c2.company_code WHERE s.code IN (SELECT a.code FROM ( SELECT DISTINCT s2.code FROM spend_category s2 INNER JOIN company_spend_category c4 ON (s2.code = c4.spendcategory_code) INNER JOIN company c3 ON c3.code = c4.company_code WHERE s2.code = '37000000' AND c3.is_deleted IS NOT NULL AND s2.is_deleted IS NOT NULL ORDER BY c3.name desc ) a WHERE ROWNUM <= 10) AND (s.code = '37000000' AND c.is_deleted IS NOT NULL AND s.is_deleted IS NOT NULL) ORDER BY c.name desc.

        Failing Query: "SELECT s.code AS s_code, c.code AS ccode, c.name AS cname, c.is_activated AS cis_activated, c.is_deleted AS c_is_deleted FROM spend_category s INNER JOIN company_spend_category c2 ON (s.code = c2.spendcategory_code) INNER JOIN company c ON c.code = c2.company_code WHERE s.code IN (SELECT a.code FROM ( SELECT DISTINCT s2.code FROM spend_category s2 INNER JOIN company_spend_category c4 ON (s2.code = c4.spendcategory_code) INNER JOIN company c3 ON c3.code = c4.company_code WHERE s2.code = '37000000' AND c3.is_deleted IS NOT NULL AND s2.is_deleted IS NOT NULL ORDER BY c3.name desc ) a WHERE ROWNUM <= 10) AND (s.code = '37000000' AND c.is_deleted IS NOT NULL AND s.is_deleted IS NOT NULL) ORDER BY c.name desc"</p>

        Show
        Thomas Wahle added a comment - It is not fixed with 1.2 RC1 Same sql wich work fine with pdo_oci generates an error using the oracle adapter <p> <b>Message:</b> ORA-01791: Kein mit SELECT ausgewählter Ausdruck : SELECT s.code AS s_ code, c.code AS c code, c.name AS c name, c.is_activated AS c is_activated, c.is_deleted AS c _is_deleted FROM spend_category s INNER JOIN company_spend_category c2 ON (s.code = c2.spendcategory_code) INNER JOIN company c ON c.code = c2.company_code WHERE s.code IN (SELECT a.code FROM ( SELECT DISTINCT s2.code FROM spend_category s2 INNER JOIN company_spend_category c4 ON (s2.code = c4.spendcategory_code) INNER JOIN company c3 ON c3.code = c4.company_code WHERE s2.code = '37000000' AND c3.is_deleted IS NOT NULL AND s2.is_deleted IS NOT NULL ORDER BY c3.name desc ) a WHERE ROWNUM <= 10) AND (s.code = '37000000' AND c.is_deleted IS NOT NULL AND s.is_deleted IS NOT NULL) ORDER BY c.name desc. Failing Query: "SELECT s.code AS s_ code, c.code AS c code, c.name AS c name, c.is_activated AS c is_activated, c.is_deleted AS c _is_deleted FROM spend_category s INNER JOIN company_spend_category c2 ON (s.code = c2.spendcategory_code) INNER JOIN company c ON c.code = c2.company_code WHERE s.code IN (SELECT a.code FROM ( SELECT DISTINCT s2.code FROM spend_category s2 INNER JOIN company_spend_category c4 ON (s2.code = c4.spendcategory_code) INNER JOIN company c3 ON c3.code = c4.company_code WHERE s2.code = '37000000' AND c3.is_deleted IS NOT NULL AND s2.is_deleted IS NOT NULL ORDER BY c3.name desc ) a WHERE ROWNUM <= 10) AND (s.code = '37000000' AND c.is_deleted IS NOT NULL AND s.is_deleted IS NOT NULL) ORDER BY c.name desc"</p>
        Thomas Wahle made changes -
        Resolution Fixed [ 1 ]
        Status Closed [ 6 ] Reopened [ 4 ]
        Hide
        Jonathan H. Wage added a comment -

        I can't reproduce the issue in a test case. Can you help me with that? When I test what you're describing I get the results that are expected.

        Show
        Jonathan H. Wage added a comment - I can't reproduce the issue in a test case. Can you help me with that? When I test what you're describing I get the results that are expected.
        Jonathan H. Wage made changes -
        Fix Version/s 1.2.0-RC2 [ 10043 ]
        Fix Version/s 1.2.0-RC1 [ 10041 ]
        Hide
        Thomas Wahle added a comment -

        Hi Jon,

        if i undestand the last comment correctly this issue will be fixed in 1.2 RC2.

        I have reviewed lots of existing test cases but did not find an example where pdo_oci or oracle adapter is used.

        Can you please forward the test case you have created for DC-278 to me? I will use this as a template for any further issues and hopefuly reduce your efforts.

        Kind regards
        Tom

        > Jonathan H. Wage updated DC-278:
        > --------------------------------
        >
        > Fix Version/s: 1.2.0-RC2
        > (was: 1.2.0-RC1)

        Show
        Thomas Wahle added a comment - Hi Jon, if i undestand the last comment correctly this issue will be fixed in 1.2 RC2. I have reviewed lots of existing test cases but did not find an example where pdo_oci or oracle adapter is used. Can you please forward the test case you have created for DC-278 to me? I will use this as a template for any further issues and hopefuly reduce your efforts. Kind regards Tom > Jonathan H. Wage updated DC-278 : > -------------------------------- > > Fix Version/s: 1.2.0-RC2 > (was: 1.2.0-RC1)
        Jonathan H. Wage made changes -
        Fix Version/s 1.2.1 [ 10044 ]
        Fix Version/s 1.2.0 [ 10043 ]
        Jonathan H. Wage made changes -
        Status Reopened [ 4 ] Closed [ 6 ]
        Fix Version/s 1.2.0 [ 10043 ]
        Fix Version/s 1.2.1 [ 10044 ]
        Resolution Fixed [ 1 ]
        Hide
        Thomas Wahle added a comment -

        I do believe that I have found the reason and it is still an issue with Doctrine 1.2.1

        Doctrine generate the following subquery which is processed in Doctrine_Query:: getLimitSubquery().

        SELECT b.id FROM
        (
        SELECT a.*, ROWNUM AS doctrine_rownum FROM
        (
        SELECT DISTINCT i.id, i.lft
        FROM item i
        INNER JOIN oum o ON i.oum_id = o.id AND (o.is_deleted IS NOT NULL)
        INNER JOIN item_type i2 ON i.type_id = i2.id AND (i2.is_deleted IS NOT NULL)
        INNER JOIN item_translation i3 ON i.id = i3.id
        LEFT JOIN attachment a ON i.id = a.item_id AND (a.is_deleted = 0)
        WHERE i.bundling_id = ?
        ORDER BY i.lft
        ) a
        ) b
        WHERE doctrine_rownum BETWEEN 3 AND 4

        This function replaces the table alias names. It looks like that the first occurance of "a" is detected and the inner alias for table attachment "a" is replaced by "a2" - that's fine. But also the outer table alias "a" is replaced by "a2" The result will be

        SELECT b.id FROM
        (
        SELECT a.*, ROWNUM AS doctrine_rownum FROM
        (
        [...]
        LEFT JOIN attachment a2 ON i.id = a2.item_id AND (a2.is_deleted = 0)
        WHERE i.bundling_id = ?
        ORDER BY i.lft
        ) a2  !!!!!!
        ) b

        „a" is selected but the alias has been changed to „a2" and this will cause an sql error.

        It looks like that this bug will only raise if a table is used in the subquery which starts with an "a"

        Show
        Thomas Wahle added a comment - I do believe that I have found the reason and it is still an issue with Doctrine 1.2.1 Doctrine generate the following subquery which is processed in Doctrine_Query:: getLimitSubquery(). SELECT b.id FROM ( SELECT a.*, ROWNUM AS doctrine_rownum FROM ( SELECT DISTINCT i.id, i.lft FROM item i INNER JOIN oum o ON i.oum_id = o.id AND (o.is_deleted IS NOT NULL) INNER JOIN item_type i2 ON i.type_id = i2.id AND (i2.is_deleted IS NOT NULL) INNER JOIN item_translation i3 ON i.id = i3.id LEFT JOIN attachment a ON i.id = a.item_id AND (a.is_deleted = 0) WHERE i.bundling_id = ? ORDER BY i.lft ) a ) b WHERE doctrine_rownum BETWEEN 3 AND 4 This function replaces the table alias names. It looks like that the first occurance of "a" is detected and the inner alias for table attachment "a" is replaced by "a2" - that's fine. But also the outer table alias "a" is replaced by "a2" The result will be SELECT b.id FROM ( SELECT a.*, ROWNUM AS doctrine_rownum FROM ( [...] LEFT JOIN attachment a2 ON i.id = a2.item_id AND (a2.is_deleted = 0) WHERE i.bundling_id = ? ORDER BY i.lft ) a2  !!!!!! ) b „a" is selected but the alias has been changed to „a2" and this will cause an sql error. It looks like that this bug will only raise if a table is used in the subquery which starts with an "a"
        Thomas Wahle made changes -
        Resolution Fixed [ 1 ]
        Status Closed [ 6 ] Reopened [ 4 ]
        Hide
        Thomas Wahle added a comment -

        As a workaround i have change "a" to "x" in the Doctrine_Connection_Oracle::_createLimitSubquery()

        As far as no table in our projekt starts with character "x" this works fine for me.

        By the way: Shouldn't it be $this->quoteIdentifier('a') . '.' instead of just a. ??

        original code:
        $query = 'SELECT b.'.$column.' FROM ( '.
        'SELECT a.*, ROWNUM AS doctrine_rownum FROM ( '
        . $query . ' ) ' . $this->quoteIdentifier('a') . ' '.
        ' ) ' . $this->quoteIdentifier('b') . ' '.
        'WHERE doctrine_rownum BETWEEN ' . $min . ' AND ' . $max;

        modified code:
        $query = 'SELECT b.'.$column.' FROM ( '.
        'SELECT x.*, ROWNUM AS doctrine_rownum FROM ( '
        . $query . ' ) ' . $this->quoteIdentifier('x') . ' '.
        ' ) ' . $this->quoteIdentifier('b') . ' '.
        'WHERE doctrine_rownum BETWEEN ' . $min . ' AND ' . $max;

        Show
        Thomas Wahle added a comment - As a workaround i have change "a" to "x" in the Doctrine_Connection_Oracle::_createLimitSubquery() As far as no table in our projekt starts with character "x" this works fine for me. By the way: Shouldn't it be $this->quoteIdentifier('a') . '. ' instead of just a. ?? original code: $query = 'SELECT b.'.$column.' FROM ( '. 'SELECT a.*, ROWNUM AS doctrine_rownum FROM ( ' . $query . ' ) ' . $this->quoteIdentifier('a') . ' '. ' ) ' . $this->quoteIdentifier('b') . ' '. 'WHERE doctrine_rownum BETWEEN ' . $min . ' AND ' . $max; modified code: $query = 'SELECT b.'.$column.' FROM ( '. 'SELECT x.*, ROWNUM AS doctrine_rownum FROM ( ' . $query . ' ) ' . $this->quoteIdentifier('x') . ' '. ' ) ' . $this->quoteIdentifier('b') . ' '. 'WHERE doctrine_rownum BETWEEN ' . $min . ' AND ' . $max;
        Hide
        Jonathan H. Wage added a comment -

        Can you provide the change that fixes the problem for you as a diff so we can test it?

        Show
        Jonathan H. Wage added a comment - Can you provide the change that fixes the problem for you as a diff so we can test it?
        Hide
        Thomas Wahle added a comment -

        Hi Jon,

        long time with no hear from you. Hope you are well!

        I am sorry, we did not fix this bug really. As writte above we have just implemented a work around by choosing a different character ("x" instead of "a") for the name of the generated sub query alias. This works fine with our data model (no table starts with "x") but may cause the same error with other data models.

        At all: There was too much trouble in this project last year. Due to this we made the decision to go ahead with pdo_oci to finish the project in time.

        Kind regards
        Thomas

        Show
        Thomas Wahle added a comment - Hi Jon, long time with no hear from you. Hope you are well! I am sorry, we did not fix this bug really. As writte above we have just implemented a work around by choosing a different character ("x" instead of "a") for the name of the generated sub query alias. This works fine with our data model (no table starts with "x") but may cause the same error with other data models. At all: There was too much trouble in this project last year. Due to this we made the decision to go ahead with pdo_oci to finish the project in time. Kind regards Thomas
        Hide
        Jonathan H. Wage added a comment -

        I'll leave this open if someone runs across the same problem, a test case showing the issue would help with pin pointing the problem area in the code.

        Show
        Jonathan H. Wage added a comment - I'll leave this open if someone runs across the same problem, a test case showing the issue would help with pin pointing the problem area in the code.

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-278, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

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

            Dates

            • Created:
              Updated: