Skip to content

Loading…

DDC-1791: Paginator query doesn't work using Oracle #2445

Closed
doctrinebot opened this Issue · 7 comments

2 participants

@doctrinebot

Jira issue originally created by user mdrolet:

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_UPDATEDDATE12,
f2.object_status_id AS OBJECT_STATUS_ID13, f2_.published_date AS PUBLISHED_DATE14, f2_.publishing_status_id AS PUBLISHING_STATUSID15,
f3.id AS ID16, f3_.title AS TITLE17, f3_.start_date AS START_DATE18, f3_.end_date AS ENDDATE19,
f4.id AS ID20, f4.name AS NAME21,
f5_.id AS ID22,
c6_.id AS ID23,
d7_.id AS ID24
FROM fodeal f0
INNER JOIN fodeal_role f1_ ON f0_.id = f1_.dealid
INNER JOIN fopeople f2_ ON f1_.people_id = f2.id
INNER JOIN foposition f3_ ON f2_.id = f3_.peopleid
INNER JOIN focompany f4_ ON f3_.company_id = f4.id
LEFT JOIN fopeople_asset f5_ ON f2_.id = f5_.peopleid
LEFT JOIN coasset c6_ ON f5_.asset_id = c6.id
LEFT JOIN dsrecord d7_ ON c6_.ds_id = d7.id
WHERE f2.object_statusid <> 3
AND f2.publishing_statusid = 2
ORDER BY f0.publisheddate DESC
) AS dctrnresult
) a
WHERE ROWNUM <= 3

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

@doctrinebot

Comment created by mdrolet:

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 dctrnresult, 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.

@doctrinebot

Comment created by mdrolet:

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

ORM/Tools/Pagination/LimitSubqueryOutputWalker.php line 126

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

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

@doctrinebot

Comment created by mdrolet:

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_UPDATEDDATE12,
f2
.object_status_id AS OBJECT_STATUS_ID13, f2_.published_date AS PUBLISHED_DATE14, f2_.publishing_status_id AS PUBLISHING_STATUSID15,
f3
.id AS ID16, f3_.title AS TITLE17, f3_.start_date AS START_DATE18, f3_.end_date AS ENDDATE19,
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 fodeal_role f1_ ON f0_.id = f1_.dealid
INNER JOIN fopeople f2_ ON f1_.people_id = f2.id
INNER JOIN foposition f3_ ON f2_.id = f3_.peopleid
INNER JOIN focompany f4_ ON f3_.company_id = f4.id
LEFT JOIN fopeople_asset f5_ ON f2_.id = f5_.peopleid
LEFT JOIN coasset c6_ ON f5_.asset_id = c6.id
LEFT JOIN dsrecord d7_ ON c6_.ds_id = d7.id
WHERE f2.object_statusid <> 3
AND f2.publishing_statusid = 2
ORDER BY f0.deal_date DESC, f0_.publisheddate DESC
) dctrn_result
) b
) a
WHERE a.rn <= 3

@doctrinebot

Comment created by mdrolet:

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;
}

@doctrinebot

Comment created by mdrolet:

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 _dctrntable',

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

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('DCTRNCOUNT', 'count');

@doctrinebot

Comment created by @beberlei:

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

@doctrinebot

Issue was closed with resolution "Fixed"

@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.3 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.