DDC-1800: Paginator results is wrong if your query use order by clause #2456

Closed
doctrinebot opened this Issue Apr 27, 2012 · 8 comments

2 participants

@doctrinebot

Jira issue originally created by user mdrolet:

NOTE: I didn't try this on other database, I'm using Oracle.

if my original fetchJoin query use an order by clause, the results is not keeping the provided order by clause and re-order them by id.

here is my generated query to get the distinct records that get generated:

SELECT distinct ID0
FROM
(
SELECT f0.id AS ID0, f0_.deal_type_id AS DEAL_TYPE_ID1, f0_.title AS TITLE2, f0_.deal_date AS DEAL_DATE3, f0.amount AS AMOUNT4,
f0.abstract AS ABSTRACT5, f0_.created_date AS CREATED_DATE6, f0_.last_updated_date AS LAST_UPDATEDDATE7,
f0.object_status_id AS OBJECT_STATUS_ID8, f0_.published_date AS PUBLISHED_DATE9, f0_.publishing_status_id AS PUBLISHING_STATUSID10,
f1.id AS ID11, f1_.role_id AS ROLE_ID12, f1_.role_type_id AS ROLE_TYPE_ID13, f2_.id AS ID14, f3_.id AS ID15, f4_.id AS ID16, f5.id AS ID17,
c6.id AS ID18, d7.id AS ID19
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 foproperty_deal f3_ ON f0_.id = f3_.dealid
INNER JOIN foproperty f4_ ON f3_.property_id = f4.id
LEFT JOIN foproperty_asset f5_ ON f4_.id = f5_.propertyid
LEFT JOIN coasset c6_ ON f5_.asset_id = c6.id
LEFT JOIN dsrecord d7_ ON c6_.ds_id = d7.id
WHERE f1.peopleid = 2
AND f0.object_statusid <> 3
AND f0.publishing_statusid = 2
ORDER BY f0.deal_date DESC, f0_.publisheddate DESC
)

running this query I get the id 30, 44 when the inner query return 44, 30

here is the query that should get generated to take care of the order by clause:
SELECT distinct ID0, rownum+
FROM
(
SELECT f0.id AS ID0, f0_.deal_type_id AS DEAL_TYPE_ID1, f0_.title AS TITLE2, f0_.deal_date AS DEAL_DATE3, f0.amount AS AMOUNT4,
f0.abstract AS ABSTRACT5, f0_.created_date AS CREATED_DATE6, f0_.last_updated_date AS LAST_UPDATEDDATE7,
f0.object_status_id AS OBJECT_STATUS_ID8, f0_.published_date AS PUBLISHED_DATE9, f0_.publishing_status_id AS PUBLISHING_STATUSID10,
f1.id AS ID11, f1_.role_id AS ROLE_ID12, f1_.role_type_id AS ROLE_TYPE_ID13, f2_.id AS ID14, f3_.id AS ID15, f4_.id AS ID16, f5.id AS ID17,
c6.id AS ID18, d7.id AS ID19
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 foproperty_deal f3_ ON f0_.id = f3_.dealid
INNER JOIN foproperty f4_ ON f3_.property_id = f4.id
LEFT JOIN foproperty_asset f5_ ON f4_.id = f5_.propertyid
LEFT JOIN coasset c6_ ON f5_.asset_id = c6.id
LEFT JOIN dsrecord d7_ ON c6_.ds_id = d7.id
WHERE f1.peopleid = 2
AND f0.object_statusid <> 3
AND f0.publishing_statusid = 2
ORDER BY f0.deal_date DESC, f0_.publisheddate DESC
) ORDER BY rownum ASC

To fix this on the Paginator code:

file: ORM/Tools/Pagination/LimitSubqueryOutputWalker.php
method: walkSelectStatement

change:
$sql = sprintf('SELECT b., rownum as rn FROM (SELECT DISTINCT %s FROM (%s)) b', // AS *dctrnresult',
implode(', ', $sqlIdentifier), $sql);

for:
$sql = sprintf('SELECT b., rownum as rn FROM (SELECT DISTINCT %s, numrow FROM (%s) ORDER BY numrow ASC) b', // AS *dctrnresult',
implode(', ', $sqlIdentifier), $sql);

@doctrinebot

Comment created by mdrolet:

rownum instead of numrow. sorry.

$sql = sprintf('SELECT b.*, rownum as rn FROM (SELECT DISTINCT %s, rownum FROM (%s) ORDER BY rownum ASC) b',
implode(', ', $sqlIdentifier), $sql);

@doctrinebot

Comment created by @beberlei:

Doctrine 2.2.2 doesnt have the LimitSubqueryoutputWalker and Doctrine 2.3-dev does not have the line in the code. Can you make a more explicit statement of where the change is necessary?

@doctrinebot

Comment created by mdrolet:

It's in the Pagination of version 2.2.2. ORM/Tools/Pagination/LimitSubqueryOutputWalker.php

@doctrinebot

Comment created by @beberlei:

Fixed

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot

Comment created by rkolbe:

This issue is popping it's head up again!

Benjamin, your tests don't test for the ordering problem unless those tests are happening somewhere else?

f55b541

I am performing a query as complex as Marc's and I experience the same exact issue. I have checked out today's latest master branch as well as the 2.3 tag with no change.

Please advise.

@doctrinebot

Comment created by rkolbe:

I have a PR in #645

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.3 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment