[DDC-1800] Paginator results is wrong if your query use order by clause Created: 27/Apr/12  Updated: 17/Apr/14  Resolved: 29/Aug/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.2.2
Fix Version/s: 2.3, 2.3.1
Security Level: All

Type: Bug Priority: Major
Reporter: Marc Drolet Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

linux oracle



 Description   

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_UPDATED_DATE7,
f0_.object_status_id AS OBJECT_STATUS_ID8, f0_.published_date AS PUBLISHED_DATE9, f0_.publishing_status_id AS PUBLISHING_STATUS_ID10,
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 fo_deal f0_
INNER JOIN fo_deal_role f1_ ON f0_.id = f1_.deal_id
INNER JOIN fo_people f2_ ON f1_.people_id = f2_.id
INNER JOIN fo_property_deal f3_ ON f0_.id = f3_.deal_id
INNER JOIN fo_property f4_ ON f3_.property_id = f4_.id
LEFT JOIN fo_property_asset f5_ ON f4_.id = f5_.property_id
LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id
LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id
WHERE f1_.people_id = 2
AND f0_.object_status_id <> 3
AND f0_.publishing_status_id = 2
ORDER BY f0_.deal_date DESC, f0_.published_date 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_UPDATED_DATE7,
f0_.object_status_id AS OBJECT_STATUS_ID8, f0_.published_date AS PUBLISHED_DATE9, f0_.publishing_status_id AS PUBLISHING_STATUS_ID10,
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 fo_deal f0_
INNER JOIN fo_deal_role f1_ ON f0_.id = f1_.deal_id
INNER JOIN fo_people f2_ ON f1_.people_id = f2_.id
INNER JOIN fo_property_deal f3_ ON f0_.id = f3_.deal_id
INNER JOIN fo_property f4_ ON f3_.property_id = f4_.id
LEFT JOIN fo_property_asset f5_ ON f4_.id = f5_.property_id
LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id
LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id
WHERE f1_.people_id = 2
AND f0_.object_status_id <> 3
AND f0_.publishing_status_id = 2
ORDER BY f0_.deal_date DESC, f0_.published_date 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 _dctrn_result',
implode(', ', $sqlIdentifier), $sql);

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



 Comments   
Comment by Marc Drolet [ 14/May/12 ]

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

Comment by Benjamin Eberlei [ 07/Jul/12 ]

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?

Comment by Marc Drolet [ 09/Jul/12 ]

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

Comment by Benjamin Eberlei [ 09/Jul/12 ]

This is the 2.2 branch, https://github.com/doctrine/doctrine2/tree/2.2/lib/Doctrine/ORM/Tools/Pagination and https://github.com/doctrine/doctrine2/tree/2.2.2/lib/Doctrine/ORM/Tools/Pagination is the 2.2.2 tag.

no LimitSubqueryOutputWalker.php in there.

Comment by Benjamin Eberlei [ 29/Aug/12 ]

Fixed

Comment by Raymond Kolbe [ 09/Apr/13 ]

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?

https://github.com/doctrine/doctrine2/commit/f55b5411c8b1f75bf2b5cf5ffe4bc50034fb91cb

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.

Comment by Raymond Kolbe [ 09/Apr/13 ]

I have a PR in https://github.com/doctrine/doctrine2/pull/645





[DDC-1977] Undefined index in ParameterTypeInferer Created: 10/Aug/12  Updated: 17/Apr/14  Resolved: 25/Aug/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.2.3
Fix Version/s: 2.3, 2.3.1
Security Level: All

Type: Bug Priority: Minor
Reporter: Matt Button Assignee: Fabio B. Silva
Resolution: Fixed Votes: 0
Labels: None
Environment:

PHP 5.3.6-13ubuntu3.8 with Suhosin-Patch (cli) (built: Jun 13 2012 17:19:54)

{ "package": "doctrine/common", "version": "2.2.2" }

,

{ "package": "doctrine/dbal", "version": "2.2.x-dev", "source-reference": "b961a3fce6bf220f1dca47d7d747b9074bea4730", "commit-date": "1341779435" }

,

{ "package": "doctrine/doctrine-bundle", "version": "dev-master", "source-reference": "62134e6a8dd3f330131ee6a970f0cee1d7760c1d", "commit-date": "1343203511" }

,

{ "package": "doctrine/orm", "version": "2.2.x-dev", "source-reference": "5d2a3bcb3b467f41ee58575764f3ba84937f76e4", "commit-date": "1341676080" }

,



 Description   

Trying to bind an empty array as a parameter to a raw SQL query results in an undefined index error on line 59.

https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Query/ParameterTypeInferer.php#L59



 Comments   
Comment by Matt Button [ 10/Aug/12 ]

One way to work around this is to specify the type of the array as the third parameter to addParameter

Comment by Fabio B. Silva [ 25/Aug/12 ]

Fixed by : https://github.com/doctrine/doctrine2/commit/ece6a005bcecc4a9e4a154d9379cfbe141370415





[DDC-1925] Bug in UnitOfWork and ManyToMany relations Created: 14/Jul/12  Updated: 16/Apr/14  Resolved: 29/Jul/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.1
Fix Version/s: 2.3
Security Level: All

Type: Bug Priority: Major
Reporter: Andrew Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: None
Environment:

symfony2



 Description   

Lets say, I have entity Forum with ManyToMany relations with User.
I need to validate user changes and I use code like

$uow = $this->getDoctrine()>getEntityManager()>getUnitOfWork();
$uow->computeChangeSets();
$changeSet = $uow->getEntityChangeSet($forum);
if (.... bla-bla-bla....)

{ $em = $this->getDoctrine()->getEntityManager(); $em->persist($forum); $em->flush(); }

Unfortunately, whenever I try to change manyToMany relations - I got error
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4-4' for key 'PRIMARY'

If I comment uow code - everything works just great.

It looks like bug in UnitOfWork implementation.

Let me know if you need more details from me.



 Comments   
Comment by Marco Pivetta [ 16/Jul/12 ]

What is the part you commented out? Also, in what context is your code executed?

Comment by Andrew [ 16/Jul/12 ]

I got error when $em->persist($forum); $em->flush(); executed.

I can create github repository with code, which reproduce this error, if you want.

Comment by Marco Pivetta [ 16/Jul/12 ]

Please do

Comment by Andrew [ 16/Jul/12 ]

that would be simple symfony2 application - will it work for you?

Comment by Marco Pivetta [ 16/Jul/12 ]

As long as the code is related to doctrine. Otherwise this issue is quite incomplete

Comment by Andrew [ 16/Jul/12 ]

Done, please check https://github.com/zhil/testDoctrine

In few words, when I add code like
$uow = $this->getDoctrine()>getEntityManager()>getUnitOfWork();
$uow->computeChangeSets();
$changeSet = $uow->getEntityChangeSet($product);

before $em->persist(); $em->flush();

I got fake MYSQL error

Thank in advance for your help

Comment by Andrew [ 19/Jul/12 ]

Just wonder - is it bug in doctrine2 or its problems somewhere else? (symfony2/my code/ etc.)

This is part of the live project - I need to fix it

Comment by Marco Pivetta [ 19/Jul/12 ]

I think it is related with the fact that you're using the `UnitOfWork` manually. You can probably try to fix the problem by moving your code to an event subscriber until this is fixed.

Comment by Andrew [ 19/Jul/12 ]

Well, I used UnitOfWork, because I need to know what was changed during object validation (for example, property status can be changed only in predefined cases etc.).

Ok, thanks for the suggestion - I will implement some temporary solution for this problem

Comment by Marco Pivetta [ 19/Jul/12 ]

I think I spotted where this happens, but I don't have a clear overview on the situation. Will try to work on this...

Comment by Andrew [ 19/Jul/12 ]

Thanks for the checking this issue.
I have already patched my application with ugly patch. Just in case solution will take some time and somebody else will need similar patch. I patched entity like

entity {
public $previousStatusBugfix = -1;
public function setStatus($status)

{ // check http://www.doctrine-project.org/jira/browse/DDC-1925?focusedCommentId=18344#comment-18344 // Ticket #651 $this->previousStatusBugfix = $this->status; $this->status = $status; }

}

and validator

if(($object->previousStatusBugfix != 1) && ($object>previousStatusBugfix != $object->getStatus()))

{ $changeSet = array("status"=>array(0=>$object->previousStatusBugfix, 1=>$object->getStatus())); }

Comment by Marco Pivetta [ 19/Jul/12 ]

I just wrote a couple of tests (attaching them to the issue shortly) and found out that on `>=2.2.x` your code runs perfectly.
The problem is on the `2.1.x` branch, and the commit that fixed the issue is https://github.com/doctrine/doctrine2/commit/4474d30 for DDC-1210

Now looking if it can be merged into `2.1.x` since it doesn't seem to cause any BC break.

Comment by Benjamin Eberlei [ 19/Jul/12 ]

A related Github Pull-Request [GH-402] was opened
https://github.com/doctrine/doctrine2/pull/402

Comment by Benjamin Eberlei [ 19/Jul/12 ]

A related Github Pull-Request [GH-403] was opened
https://github.com/doctrine/doctrine2/pull/403

Comment by Marco Pivetta [ 19/Jul/12 ]

Duplicate of DDC-1210

Comment by Benjamin Eberlei [ 23/Jul/12 ]

A related Github Pull-Request [GH-403] was closed
https://github.com/doctrine/doctrine2/pull/403

Comment by Benjamin Eberlei [ 29/Jul/12 ]

A related Github Pull-Request [GH-402] was closed
https://github.com/doctrine/doctrine2/pull/402

Comment by Doctrine Bot [ 12/Nov/13 ]

A related Github Pull-Request [GH-402] was closed:
https://github.com/doctrine/dbal/pull/402

Comment by Doctrine Bot [ 13/Nov/13 ]

A related Github Pull-Request [GH-403] was closed:
https://github.com/doctrine/dbal/pull/403





Generated at Sun Apr 20 00:45:58 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.