Invalid qubquery generated if using oracle adapter instead of pdo_oci

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

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>

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.

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)

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"

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;

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

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

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.