[DC-403] Eliminate queries produced by Doctrine_Node_NestedSet::getDescendants() when possible Created: 06/Jan/10  Updated: 16/Apr/10

Status: Open
Project: Doctrine 1
Component/s: Nested Set
Affects Version/s: 1.2.1
Fix Version/s: 1.2.0

Type: Improvement Priority: Major
Reporter: alex Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 2
Labels: None


 Description   

How to reproduce

  1. fetch a tree with nested set
    $root = Doctrine::getTable($model)->getTree()->findRoot($rootId);
    $treeObject= Doctrine::getTable($model)->getTree()->fetchTree($root->getId()); 
    
  2. issue
    $root->getNode()->getChildren();
    
    or there will be a total query mess in a lambda function with a loop getChildren().
    ...
    <?php foreach($root->getChildren() as $child): ?>
       ....
    <?php endforeach; ?>
    ...
    
    

Problem is described more verbosely here – http://codeutopia.net/blog/2008/08/30/understanding-doctrines-nestedset-feature/

What behavior is expected

An already fetched record instance is returned, without calling database and without causing query flooding.

Like in propel.

In propel if you are calling ....::retrieveTree($id) it returns the root
with completed, pooled (cached) and linked tree.

What happens

An additional query is issued

This is a big slip in architecture of Doctrine`s nested set implementation.






[DC-278] Invalid qubquery generated if using oracle adapter instead of pdo_oci Created: 23/Nov/09  Updated: 09/Jun/10

Status: Reopened
Project: Doctrine 1
Component/s: None
Affects Version/s: None
Fix Version/s: 1.2.0

Type: Bug Priority: Major
Reporter: Thomas Wahle Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 0
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);
}



 Comments   
Comment by Jonathan H. Wage [ 23/Nov/09 ]

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

Comment by Thomas Wahle [ 24/Nov/09 ]

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>

Comment by Jonathan H. Wage [ 24/Nov/09 ]

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.

Comment by Thomas Wahle [ 25/Nov/09 ]

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)

Comment by Thomas Wahle [ 10/Dec/09 ]

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"

Comment by Thomas Wahle [ 11/Dec/09 ]

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;

Comment by Jonathan H. Wage [ 08/Jun/10 ]

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

Comment by Thomas Wahle [ 09/Jun/10 ]

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

Comment by Jonathan H. Wage [ 09/Jun/10 ]

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.





Generated at Tue Oct 21 15:33:49 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.