[DDC-3056] Return value mismatch between code under HHVM and Zend Created: 28/Mar/14  Updated: 28/Mar/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: Git Master
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Andy hunt Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: hhvm, orm, paginator
Environment:

Two environments:
LAMP stack with PHP 5.4.25 on Ubuntu 12.04
HHVM 3.0.0.-dev (rel) ob Ubuntu 12.04



 Description   

The following code produces differing results under Zend and HHVM runtimes.

// $all::build uses the query builder to select all entities of a type
/** @var \Doctrine\ORM\Query $query **/
$query = $all->build($qb);
$query->setMaxResults($pageSize)->setFirstResult($start);

$paginator = new Paginator($query);
$results = array_values((array)$paginator->getIterator());

Under Zend, $results is a 1-dimensional array containing N elements:
[1, 2, 3].

Under HHVM, $results is a 2-dimensional array containing a single array, containing N elements:
[ [1,3,3] ]



 Comments   
Comment by Christophe Coevoet [ 28/Mar/14 ]

I suggest reporting it to the HHVM team as a bug

Comment by Marco Pivetta [ 28/Mar/14 ]

Also: why are you using an array cast and not iterator_to_array?

Comment by Christophe Coevoet [ 28/Mar/14 ]

@Marco this should be equivalent. Casting a Traversable to array should traverse it. If HHVM does not do it, it is a bug.

Comment by Marco Pivetta [ 28/Mar/14 ]

Christophe Coevoet not really: http://3v4l.org/Z3t4t





[DDC-2954] Paginator loses items Created: 05/Feb/14  Updated: 12/Feb/14

Status: Awaiting Feedback
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3.4, 2.4.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Mariusz Jaskółka Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: Pagination, Paginator
Environment:

Linux and Windows, PHP 5, Oracle(OCI8)


Attachments: File LimitSubqueryOutputWalker.php     File LimitSubqueryOutputWalker_bugfix.php    

 Description   

Sometimes when when I use Paginator (Doctrine\ORM\Tools\Pagination\Paginator)

  • with query contains orderBy
  • with $fetchJoinCollection = true
  • lot of joins with toMany associations

there are too few items in result (no, it is not the end of list). There two situations:
1. I have four items on page 1 and two items on page 2 (pageLimit is 5). It is no so bad
2. I have four items on page 1 and there is no page 2 (while there should be 5 all items). This is very bad because I lose one item.

------------------------------------------------------------------
EDIT:
In function Paginator::getIterator there is variable $ids. In my situation it contains five numbers [34,26,34,15,12]. There is duplicated value 34 but ids of top-level entities should be distinct (as far as we do not use CROSS JOIN, or maybe I am wrong).

The Paginator::count function works correctly, it does not count duplicated values twice.

Statement that gets $ids is like following:
SELECT a.* FROM (SELECT DISTINCT ID2, BEGINTIME70 FROM (...) dctrn_result ORDER BY BEGINTIME70 DESC) a WHERE ROWNUM <= 5

------------------------------------------------------------------
EDIT 2 - Bugfix description:
The result items of the query is not unique because of
"SELECT DISTINCT col_with_id, order_by_column (...) ORDER BY order_by_column".
If we had items like following:
(1,A)
(1,B)
(1,B)
(2,C)

After DISTINCT operation the result would be:
(1,A)
(1,B)
(2,C)

But we want to have unique firs column, not pairs. That's why we should do "ORDER BY" before "DISTINCT" - not in the same time.
Please confirm if the solution is correct.



 Comments   
Comment by Marco Pivetta [ 05/Feb/14 ]

Mariusz Jaskółka this needs more details

Comment by Mariusz Jaskółka [ 06/Feb/14 ]

OK, I will try to find out where the problem is.

Comment by Mariusz Jaskółka [ 06/Feb/14 ]

I have edited description, maybe additional information will help.

Comment by Mariusz Jaskółka [ 07/Feb/14 ]

I send the bugfix in attachment. I will describe it soon.

Comment by Mariusz Jaskółka [ 07/Feb/14 ]

Bugfix version 2 - previously I did not notice that oracle loses order after DISTINCT operation. Thus I used GROUP BY.

Comment by Mariusz Jaskółka [ 11/Feb/14 ]

I do not know if I can change status of this issue from "Awaiting Feedback". I can not see such option





[DDC-2890] Paginator generates invalid sql for some dql with setUseOutputWalkers(false) and $fetchJoinCollection = true Created: 07/Jan/14  Updated: 17/Apr/14  Resolved: 17/Apr/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: 2.4.1
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Jiri Kavalik Assignee: Guilherme Blanco
Resolution: Fixed Votes: 0
Labels: paginator
Environment:

ubuntu 12.04, ZF2 2.2.5, mysql 5.5.34



 Description   

We use doctrine paginator in zf2 for list pagination.

We tried to disable UseOutputWalkers because of performance gain - for some entities expected table size is in millions and we are paginating simple lists with some inner joins - but with UseOutputWalkers(false) and fetchJoinCollection=true (default) we get exception for queries ordering by referenced entity id.

Examples:

  • OK - DQL:
    SELECT Transaction FROM Transaction\Entity\Transaction Transaction ORDER BY Transaction.balance asc
    

    SQL:

    SELECT t0_.id AS id0, t0_.value AS value1, t0_.balance AS balance2, t0_.created_on AS created_on3, t0_.type_id AS type_id4, t0_.canceled_id AS canceled_id5, t0_.canceling_id AS canceling_id6, t0_.wallet_id AS wallet_id7 FROM transaction t0_ ORDER BY t0_.balance ASC
    

    Paginator SQL:

    SELECT count(DISTINCT t0_.id) AS sclr0 FROM transaction t0_
    SELECT DISTINCT t0_.id AS id0, t0_.balance AS balance1 FROM transaction t0_ ORDER BY t0_.balance ASC LIMIT 10 OFFSET 0
    SELECT t0_.id AS id0, t0_.value AS value1, t0_.balance AS balance2, t0_.created_on AS created_on3, t0_.type_id AS type_id4, t0_.canceled_id AS canceled_id5, t0_.canceling_id AS canceling_id6, t0_.wallet_id AS wallet_id7 FROM transaction t0_ WHERE t0_.id IN (?) ORDER BY t0_.balance ASC
    
  • Exception - Error producing an iterator - DQL:
    SELECT Transaction FROM Transaction\Entity\Transaction Transaction ORDER BY Transaction.type asc
    

    SQL:

    SELECT t0_.id AS id0, t0_.value AS value1, t0_.balance AS balance2, t0_.created_on AS created_on3, t0_.type_id AS type_id4, t0_.canceled_id AS canceled_id5, t0_.canceling_id AS canceling_id6, t0_.wallet_id AS wallet_id7 FROM transaction t0_ ORDER BY t0_.type_id ASC
    

    Paginator SQL with error:

    SELECT count(DISTINCT t0_.id) AS sclr0 FROM transaction t0_
    SELECT DISTINCT t0_.id AS id0, t0_. AS _1 FROM transaction t0_ ORDER BY t0_.type_id ASC LIMIT 10 OFFSET 0
    SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS _1 FROM transaction t0_ ORDER BY t0_.type_id ASC LIMIT 10 OFFSET 0' at line 1
    

    Same query with $fetchJoinCollection = false - OK - paginator SQL:

    SELECT count(DISTINCT t0_.id) AS sclr0 FROM transaction t0_
    SELECT t0_.id AS id0, t0_.value AS value1, t0_.balance AS balance2, t0_.created_on AS created_on3, t0_.type_id AS type_id4, t0_.canceled_id AS canceled_id5, t0_.canceling_id AS canceling_id6, t0_.wallet_id AS wallet_id7 FROM transaction t0_ ORDER BY t0_.type_id ASC LIMIT 10 OFFSET 0
    
  • using setUseOutputWalkers(true) generates most robust queries but count is really slow for 200k+ tables


 Comments   
Comment by Guilherme Blanco [ 17/Apr/14 ]

As of https://github.com/doctrine/doctrine2/commit/be1cc14a9c8641774d614f788103cef4a5373bb1 issue is now fixed.





[DDC-2794] the Paginator does not support arbitrary join Created: 14/Nov/13  Updated: 23/Dec/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: Git Master, 2.3.4, 2.4.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 2
Labels: paginator


 Description   

Using the following query with the paginator fails:

SELECT u
FROM User u
JOIN Message m WITH m.author = u
WHERE m.status = 'draft' -- a condition justifying the join

The CountWalker and the CountOutputWalker both throw a exception saying "Cannot count query which selects two FROM components, cannot make distinction"

This message is wrong (I can make distinction here. Only User is selected in the result set) and confusing (I spent some time finding the second FROM before figuring it was related to the use of an arbitrary join instead of an association join).



 Comments   
Comment by Pierre-Antoine Pinel [ 23/Dec/13 ]

Hi,

I have the same issue.
My query is : SELECT entity FROM W\XBundle\Entity\X entity LEFT JOIN Z\YBundle\Entity\Y y WITH y.x = entity LEFT JOIN y.v v WHERE v.name LIKE :like

Is there a way to fix this issue ? I really need this to work with the Paginator.

Best,

Comment by Christophe Coevoet [ 23/Dec/13 ]

Why changing the status to "Awaiting feedback" ? It is not waiting for a feedback of the reporter to check the fix. It is an open ticket which does not have a fix yet





[DDC-2752] Item order lost using paginator Created: 20/Oct/13  Updated: 13/Dec/13  Resolved: 13/Dec/13

Status: Closed
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Kristof Mariën Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: paginator


 Description   

We are using the doctrine pagination tool for faster queries in paginated results.

But doctrine is now creating the following query:

SELECT DISTINCT id0 FROM (
SELECT c0_.id AS id0, c0_.number AS number1
FROM cudi.stock_orders_items c0_
INNER JOIN cudi.stock_orders c1_ ON c0_.order_id = c1_.id
WHERE c1_.date_ordered IS NOT NULL AND c1_.date_ordered > $1 AND c1_.date_ordered < $2
ORDER BY c1_.date_ordered DESC
) dctrn_result LIMIT 25 OFFSET 50

But due to the distinct, the order is lost and the results are not sorted by date_ordered anymore. How can this be solved?

Thanks



 Comments   
Comment by Marco Pivetta [ 20/Oct/13 ]

Kristof Coomans is the order lost already in this result or in the `WHERE IN()` subsequent query?

Comment by Kristof Mariën [ 20/Oct/13 ]

I was digging in the paginator code and this is the query used to select the ids in \Doctrine\ORM\Tools\Pagination\Paginator (line 173)

The subquery from above, outputs the results in the right order. But after applying the distinct the order is messed up. I think this problem needs to be solved by the preserveSqlOrdering function in LimitSubqueryOutputWalker. But this function doesn't add a column to sort on...

Comment by Benjamin Eberlei [ 26/Oct/13 ]

Kristof Mariën Since you have the example already, so you mean the ORDER BY has to be part of the subquery and ALSO added to the outer query? That actually makes sense.

Comment by Kristof Mariën [ 28/Oct/13 ]

That would solve the problem for me.

Comment by Kristof Mariën [ 20/Nov/13 ]

Any progress on this issue?

Comment by Kristof Mariën [ 04/Dec/13 ]

I have found another query with the same problem. The common thing they have is that I want to order on a column of a joined entity.

Comment by Kristof Mariën [ 06/Dec/13 ]

After digging in the code I found that I just had to add the joined entity to my select statement. This issue can be closed





pager produces wrong results on postgresql (DDC-1958)

[DDC-2729] Same bug affects SQLServer2008Platform Created: 07/Oct/13  Updated: 07/Oct/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: 2.4
Fix Version/s: None
Security Level: All

Type: Sub-task Priority: Major
Reporter: Rafi Adnan Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: paginator
Environment:

SQL Server 10.50.1617
unixODBC 2.2.14
PHP 5.4.19



 Description   

If the class is patched with the following:

if ($this->platform instanceof SQLServer2008Platform)

{ $this->preserveSqlOrdering($AST, $sqlIdentifier, $innerSql, $sql); }

It fixes the issue.






[DDC-2381] Pagination query can be simplified when simple joins are applied Created: 31/Mar/13  Updated: 29/Nov/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3, 2.4
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Minor
Reporter: Sergey Gerdel Assignee: Marco Pivetta
Resolution: Unresolved Votes: 2
Labels: paginator

Attachments: HTML File EXPLAIN.htmL     HTML File EXPLAIN1.html     HTML File EXPLAIN2.html     HTML File EXPLAIN3.htm     HTML File EXPLAIN4.htm    

 Description   

Hi.
In mysql db table i have > 200,000 items.
I use native doctrine pagination for paging the items list.
But generated query that gets ids for items list in paging works more then 150 sec on my workstation

SELECT DISTINCT id0 FROM (SELECT m0_.id AS id0, m0_.title AS title1, m0_.text AS text2, m0_.price AS price3, m0_.originalPrice AS originalPrice4, m0_.condition_type AS condition_type5, m0_.image_1 AS image_16, m0_.image_2 AS image_27, m0_.image_3 AS image_38, m0_.image_4 AS image_49, m0_.image_5 AS image_510, m0_.video AS video11, m0_.contact_email AS contact_email12, m0_.contact_name AS contact_name13, m0_.contact_phone AS contact_phone14, m0_.contact_type AS contact_type15, m0_.published AS published16, m0_.type AS type17, m0_.status AS status18, m0_.highlight AS highlight19, m0_.urgent AS urgent20, m0_.topads AS topads21, m0_.period AS period22, m0_.hits AS hits23, m0_.ip AS ip24, m0_.created_at AS created_at25, m0_.updated_at AS updated_at26 FROM milla_message m0_ INNER JOIN milla_currency m1_ ON m0_.currency_id = m1_.id INNER JOIN milla_category m2_ ON m0_.category_id = m2_.id INNER JOIN milla_region m3_ ON m0_.region_id = m3_.id INNER JOIN milla_city m4_ ON m0_.city_id = m4_.id WHERE m0_.status = 1 ORDER BY m0_.published DESC) dctrn_result LIMIT 20 OFFSET 0

source code
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L141

why SELECT DISTINCT %s FROM (%s) dctrn_result ???
why not SELECT DISTINCT m0_.id AS id0 FROM milla_message m0_ WHERE m0_.status = 1 ORDER BY m0_.published DESC LIMIT 20 OFFSET 0



 Comments   
Comment by Marco Pivetta [ 31/Mar/13 ]

Not a blocker

Comment by Marco Pivetta [ 31/Mar/13 ]

What's the result of `EXPLAIN` on a query without the subquery?

Comment by Sergey Gerdel [ 31/Mar/13 ]

explain without the subquery

Comment by Marco Pivetta [ 31/Mar/13 ]

Sergey Gerdel that's not the same query.

Comment by Marco Pivetta [ 31/Mar/13 ]

Sergey Gerdel this is still using

Using index; Using temporary; Using filesort

Check your indexes

Comment by Sergey Gerdel [ 31/Mar/13 ]

Not in the index problem

SELECT DISTINCT id0 FROM (SELECT m0_.id AS id0, m0_.title AS title1, m0_.text AS text2, m0_.price AS price3, m0_.originalPrice AS originalPrice4, m0_.condition_type AS condition_type5, m0_.image_1 AS image_16, m0_.image_2 AS image_27, m0_.image_3 AS image_38, m0_.image_4 AS image_49, m0_.image_5 AS image_510, m0_.video AS video11, m0_.contact_email AS contact_email12, m0_.contact_name AS contact_name13, m0_.contact_phone AS contact_phone14, m0_.contact_type AS contact_type15, m0_.published AS published16, m0_.type AS type17, m0_.status AS status18, m0_.highlight AS highlight19, m0_.urgent AS urgent20, m0_.topads AS topads21, m0_.period AS period22, m0_.hits AS hits23, m0_.ip AS ip24, m0_.created_at AS created_at25, m0_.updated_at AS updated_at26 FROM milla_message m0_ WHERE m0_.status = 1 ORDER BY m0_.published DESC) dctrn_result LIMIT 20 OFFSET 0

Time: 104.614s explain 3

SELECT DISTINCT m0_.id AS id0 FROM milla_message m0_ WHERE m0_.status = 1 ORDER BY m0_.published DESC LIMIT 20 OFFSET 0;

Time: 0.001s explain 4

Comment by Marco Pivetta [ 01/Apr/13 ]

Sergey Gerdel the ORM cannot simplify a complex query that way. There may be a conditional on one of the joined results, or generally usage of one of the joined results.

Things that could be optimized here are:

  • Removal of the `ORDER BY` clause when grouping (check ORM master, I think somebody already did that)
  • Trying to simplify the query by doing some serious hacking on the AST.

The problem I see here is that the chance to spawn random bugs because of the optimization is very high, and you'd have to rewrite `walkSelectStatement`

Comment by Marco Pivetta [ 01/Apr/13 ]

Marking as improvement

Comment by Sergey Gerdel [ 07/Apr/13 ]

Minor?
i have 100 sec for this query.
200k items are selected for temporary table. wtf?

OK. Programmers may be mistaken in parser
expect ORDER BY m0_.published DESC LIMIT 20 OFFSET 0) dctrn_result
Time: 0.001s

reality ORDER BY m0_.published DESC) dctrn_result LIMIT 20 OFFSET 0

Comment by Marco Pivetta [ 07/Apr/13 ]

Sergey Gerdel this problem does not introduce security issues and can be worked around by you while using your own pagination logic. It does not stop you from doing anything, that's why it's minor.

Comment by Sergey Gerdel [ 08/Apr/13 ]

ok)
i have already created my own paginator.
at last
please see how to fix this problem
https://github.com/Sergic/doctrine2/commit/2733c815387273d3bd199a68acb717e0cbc8ccfe

Comment by Tom Pryor [ 11/Oct/13 ]

I've also run into this problem which makes Doctrine's Paginator useless for large datasets. The actual query takes 0.002ms but the SELECT DISTINCT query doctrine executes takes over 30s because MySQL creates a temporary table with 200k+ records.

You don't need to remove the joined tables from the paginator query (I have conditions on the joined tables anyway), this has a negligible impact performance, but rather it is caused by SELECT DISTINCT and ORDER BY which no index configuration can solve. Rather, perhaps a flag could be added to the paginator to indicate my query does not fetch join any has many collections (i.e each row returned will be unique) negating the need for the SELECT DISTINCT. The pagination would only then need to perform the original query with LIMIT and OFFSET applied along with a separate COUNT query on the primary key, both of which are very fast as they'd use the indexes setup for the the original query.

Comment by Christophe Coevoet [ 11/Oct/13 ]

This flag already exists for the select query. See the second argument of the constructor.

For the count query, you should call $paginator->setUseOutputWalkers(false) to make it use a DQL AST walker instead of the SQL Output walker (the AST walker does not support counting on queries using HAVING which is why it is not selected by default)

Comment by Robert (Jamie) Munro [ 29/Nov/13 ]

I think this is more important than "minor", as I've experienced this when upgrading from 2.2. My site became unusably slow.

I can't easily work around it because I am using Symfony bundles that use this, I am not using this directly. None of the workarounds mentioned so far seem to have helped.





[DDC-2283] Paginator with orderBy in joined data retrieve bad result Created: 07/Feb/13  Updated: 26/Feb/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3.2
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: Jean-Philippe THEVENOUX Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: paginator


 Description   

entity A have many entity B

If DQL is something like "select A, B from A join B order by A.field1, B.field2"
Then Paginator retrieve different Id by a query like :
"select distinct a.id, a.field1, b.field2 from ( ....) order by a.field1 ASC , b.field2 ASC limit 15 offset 0"

so, if a entity A have 20 entity B (and these sub-entity have all a different b.field2) then there's only 1 A retrieved






[DDC-2236] SUM(..) with Pagination gives incorrect result Created: 11/Jan/13  Updated: 10/Feb/13

Status: In Progress
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: 2.2.3
Fix Version/s: None

Type: Documentation Priority: Minor
Reporter: Oleg Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: paginator
Environment:

Linux



 Description   

https://github.com/whiteoctober/Pagerfanta/issues/69

<?php
$query = $em->getRepository('M\E\Q')
->createQueryBuilder('q')
->select('q', 'SUM(q.price) AS amount')
->where('q.id IN(19, 20, 22)')
->groupBy('q.customer')
;

$pager = new Pagerfanta(new DoctrineORMAdapter($query));
$pager->setMaxPerPage(30);
$pager->setCurrentPage($request->query->get('page', 1));

$result = $pager->getCurrentPageResults();
print_r($result[0]['amount']); // 156.71 - Incorrect

$result = $query->getQuery()->getResult();
print_r($result[0]['amount']); // 553.47
?>

Sql for the above:

SELECT DISTINCT id0 FROM (SELECT q0_.id AS id0, SUM(q0_.price) AS sclr36 FROM Q q0_ WHERE q0_.id IN (19, 20, 22) GROUP BY q0_.customer_id) dctrn_result LIMIT 30 OFFSET 0
SELECT q0_.id AS id0, SUM(q0_.price) AS sclr36 FROM Q q0_ WHERE q0_.id IN (19, 20, 22) AND q0_.id IN ('19') GROUP BY q0_.customer_id
SELECT q0_.id AS id21, SUM(q0_.price) AS sclr36 FROM Q q0_ WHERE q0_.id IN (19, 20, 22) GROUP BY q0_.customer_id

Sql with fetchJoin = false (new DoctrineORMAdapter($query, false))

SELECT q0_.id AS id0, SUM(q0_.price) AS sclr36 FROM Quote q0_ WHERE q0_.id IN (19, 20, 22) GROUP BY q0_.customer_id LIMIT 30 OFFSET 0
SELECT q0_.id AS id0, SUM(q0_.price) AS sclr36 FROM Quote q0_ WHERE q0_.id IN (19, 20, 22) GROUP BY q0_.customer_id



 Comments   
Comment by Alexander [ 09/Feb/13 ]

Can you also test this with doctrine >= 2.3? The pagination code changed quite a lot.

Comment by Oleg [ 10/Feb/13 ]

Looks like no change

composer.json:
"doctrine/orm": "2.3.*",

php composer.phar update
Loading composer repositories with package information
Updating dependencies

  • Installing doctrine/common (2.3.0)
    Loading from cache
  • Installing doctrine/dbal (2.3.2)
    Loading from cache

then cleared cache but result is same
Here's the code

 
$query = $this->getDoctrine()->getEntityManager()->getRepository('MyBundle:Invoice')
  ->createQueryBuilder('q')
  ->select('q', 'SUM(q.amount) AS amount')
  ->groupBy('q.customer')
;
 
95 Connect	root@localhost on **
95 Query	SELECT DISTINCT id0 FROM (SELECT i0_.id AS id0, i0_.invoice_num AS invoice_num1, i0_.date AS date2, i0_.amount AS amount3, i0_.vat_amount AS vat_amount4, i0_.amount_paid AS amount_paid5, i0_.md5 AS md56, i0_.is_exported AS is_exported7, i0_.created AS created8, SUM(i0_.amount) AS sclr9 FROM Invoice i0_ GROUP BY i0_.customer_id) dctrn_result LIMIT 30 OFFSET 0
95 Query	SELECT i0_.id AS id0, i0_.invoice_num AS invoice_num1, i0_.date AS date2, i0_.amount AS amount3, i0_.vat_amount AS vat_amount4, i0_.amount_paid AS amount_paid5, i0_.md5 AS md56, i0_.is_exported AS is_exported7, i0_.created AS created8, SUM(i0_.amount) AS sclr9, i0_.customer_id AS customer_id10 FROM Invoice i0_ WHERE i0_.id IN ('2') GROUP BY i0_.customer_id
95 Query	SELECT i0_.id AS id0, i0_.invoice_num AS invoice_num1, i0_.date AS date2, i0_.amount AS amount3, i0_.vat_amount AS vat_amount4, i0_.amount_paid AS amount_paid5, i0_.md5 AS md56, i0_.is_exported AS is_exported7, i0_.created AS created8, SUM(i0_.amount) AS sclr9, i0_.customer_id AS customer_id10 FROM Invoice i0_ GROUP BY i0_.customer_id
130210 16:08:25	   95 Quit	

But I understand why that happens, it's due to group by and pagination nature.
The first query returns only one row with id "2", second query should be actually "..WHERE i0_.id IN ('2', '3', '4')"

If I do

$pager = new Pagerfanta(new DoctrineORMAdapter($query, false));

I get this sql

SELECT i0_.id AS id0, i0_.invoice_num AS invoice_num1, i0_.date AS date2, i0_.amount AS amount3, i0_.vat_amount AS vat_amount4, i0_.amount_paid AS amount_paid5, i0_.md5 AS md56, i0_.is_exported AS is_exported7, i0_.created AS created8, SUM(i0_.amount) AS sclr9, i0_.customer_id AS customer_id10 FROM Invoice i0_ LIMIT 30 OFFSET 0

I think it should be noted somewhere that if you do groupBy you should set fetchJoin to false?

Comment by Marco Pivetta [ 10/Feb/13 ]

Updating to Documentation issue.





[DDC-2213] Paginator does not work with composite primary key entity Created: 25/Dec/12  Updated: 15/Aug/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM, Tools
Affects Version/s: 2.3.1
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Major
Reporter: Stanislav Anisimov Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 3
Labels: composed, key, paginator
Environment:

php 5.4



 Description   

Paginator does not work with composed primary key.

"Single id is not allowed on composite primary key in entity" exception is thrown here
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/WhereInWalker.php#L90

Only first column values are fetched while retrieving primary keys here
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/Paginator.php#L173



 Comments   
Comment by Marco Pivetta [ 23/Jan/13 ]

Limitation was confused by issue reporter and considered bug

Comment by Austin Morris [ 15/Aug/13 ]

I'd like to add some additional information because the title and description are misleading.

Paginator does work with composite primary key entities. You just cannot use the Paginator when your query does a fetch join with a collection (a one-to-many or many-to-many association). See the documentation for a brief description:
http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/pagination.html

In order to use Paginator with a composite primary key entity, you need to instantiate the Paginator with the $fetchJoinCollection flag set to false (it defaults to true). Now, pagination will skip the WhereInWalker which throws an exception when using a composite primary key. Fetch joins with entities (one-to-one or many-to-one) will still work. You can even use a regular join with a collection.

The only "problem" is when your Paginator query calls for a fetch join to a collection. The work around for this is to use a regular join as mentioned above. The drawback is that your paginated entities will not be hydrated with the collection. The collection will have to be lazy-loaded when called upon.





[DDC-1958] pager produces wrong results on postgresql Created: 30/Jul/12  Updated: 06/Aug/13  Resolved: 12/Nov/12

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

Type: Bug Priority: Major
Reporter: Miha Vrhovnik Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: paginator
Environment:
  • Postgres 9.1, 9.2
  • PHP 5.4

Sub-Tasks:
Key
Summary
Type
Status
Assignee
DDC-2593 Same bug occurs in MariaDB 5.5 Sub-task Resolved Marco Pivetta  
DDC-2729 Same bug affects SQLServer2008Platform Sub-task Open Benjamin Eberlei  

 Description   

The query build by pager to get the subset of PKs to fetch produces wrong results on potgresql (and probably any database), that conforms to the SQL standard. The standard says, that if you wish to have the results in specific order, then you have to specify that by using an ORDER BY clause. If such a clause is not present the database can return the results in whatever order it sees fit.

Testcase fixtures:

CREATE TABLE test (
    id integer,
    name text
);

INSERT INTO test VALUES (1, 'c');
INSERT INTO test VALUES (2, 'a');
INSERT INTO test VALUES (3, 'e');
INSERT INTO test VALUES (4, 'b');
INSERT INTO test VALUES (5, 'd');
INSERT INTO test VALUES (6, 'a');
INSERT INTO test VALUES (7, 'g');
INSERT INTO test VALUES (8, 'h');
INSERT INTO test VALUES (9, 'e');
INSERT INTO test VALUES (10, 'j');

Passing f.e.

$qb = $this->repository
    ->createQueryBuilder('t')
    ->select('t')
    ->setFirstResult(0)
    ->setMaxResults(5)
    ->addOrderBy('t.name', 'ASC')

to pager produces SQL like this modified for readability

SELECT DISTINCT id FROM (
    SELECT id, name FROM test ORDER BY name
  ) dctrn_result
  LIMIT 5 OFFSET 0

Now there is nothing wrong with this modified query per se, but there is no ORDER BY clause in the outer query so according to the standard the DB can choose whatever order it seems fit. Now mysql chooses the same order, but postgresql does not and it's probably not the only DB doing so.

If you are interested in the results, this is the output I'm seeing:

  • postgresql: 8,4,1,5,3
  • mysql : 2,6,4,1,5

I and my coworker came to the standard compliant solution it was also tested on the dataset above on both postgresql and mysql and it produced equal results. We have found only one corner case this won't work and IMHO that can't be fixed. The problem is when you do a sort on a field from a table that is in 1:n relation to the main table.. e.g tables posts and tags, where one post can have a multiple tags and you want your results sorted by a tag.

Recipe for a correct query is:

  • remember the ORDER BY fields from original query and then remove them
  • wrap the original query with a DISTINCT query, but add the fields from ORDER BY to the SELECT part of that query and add the whole ORDER BY to the end of it, also add the PK to the order by clause, and add the LIMIT clause
  • wrap the resulting query into another query and select just the id.

so if I take the example from above the SQL should look like this:

SELECT id FROM (
  SELECT DISTINCT id, name FROM (
    SELECT id, name FROM test
  ) dctrn_result_inner
  ORDER BY name, id LIMIT 5 OFFSET 0
) dctrn_result


 Comments   
Comment by Jean-Philippe THEVENOUX [ 08/Nov/12 ]

I reproduce same problem with Postgres 7.4, Doctrine 2.3 whereas with doctrine 2.2, all is fine
Hope there'll a fix in next doctrine version

Comment by Raymond Kolbe [ 09/Apr/13 ]

http://www.doctrine-project.org/jira/browse/DDC-1800 This relates.

I just published a PR for an Oracle fix, but your solution appears to work for Oracle as well (issue is the same).

Comment by Bojidar Hristov [ 06/Aug/13 ]

Same bug occurs in MariaDB 5.5.

I commented out check for PostgreSQL and it works fine. Can you fix it for MariaDB too? Thanks





[DDC-1935] Not all identifier properties can be found in the ResultSetMapping: id Created: 19/Jul/12  Updated: 29/Jul/12  Resolved: 29/Jul/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: dquintard Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: paginator


 Description   

$qb1 = $em->createQueryBuilder()->select('c.id');
...
$query1 = $qb1->getQuery();

$doctrinePaginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query1);
$doctrinePaginator->count(); => generate error

But works fine with $query2 !

$qb2 = $em->createQueryBuilder()->select('c');
...
$query2 = $qb2->getQuery();

$doctrinePaginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query2);
$doctrinePaginator->count(); => no error



 Comments   
Comment by dquintard [ 20/Jul/12 ]

It seems that this issue is the same that:
http://www.doctrine-project.org/jira/browse/DDC-1927

Comment by Benjamin Eberlei [ 29/Jul/12 ]

Duplicate of DDC-1927





[DDC-1927] Pagination of a SELECT of specific fields results in a RuntimeException Created: 16/Jul/12  Updated: 23/Oct/13  Resolved: 25/Nov/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Zacharias Luiten Assignee: Benjamin Eberlei
Resolution: Can't Fix Votes: 0
Labels: paginator


 Description   

When paginating a DQL string which selects specific fields it results in the following error: PHP Fatal error: Uncaught exception 'RuntimeException' with message 'Not all identifier properties can be found in the ResultSetMapping: id'

NOT working: 'SELECT c.id, c.number FROM Application\Entity\Course c'
WORKING: 'SELECT c FROM Application\Entity\Course c'
WORKING: 'SELECT c, c.id, c.number FROM Application\Entity\Course c'

Setting hydration mode to scalar results makes no difference.

Gist to example code and stack trace: https://gist.github.com/d5cd6d0b0ac28e722dd7



 Comments   
Comment by Benjamin Eberlei [ 29/Jul/12 ]

First results: This is very complicated to support, the pagination was designed for entity results. I have to check this when I have more time.

Comment by dquintard [ 08/Aug/12 ]

Hi Benjamin,
I can change my code to select entity results (even for hundreds/thousands results).
But for now Pagination is not usable with ResultSetMapping::addScalarResult

Comment by Matt Pinkston [ 13/Nov/12 ]

The reason this error occurs is because the Paginator creates its own ResultSetMapping and relies on the SqlWalker to configure it (see Doctrine\ORM\Query\SqlWalker::walkSelectExpression).

Doctrine will interpret each field in the first example (SELECT c.id, c.number...) as a PathExpression and add it to the result set mapping as a scalar result. This makes it impossible for the paginator to reliably know which field can be considered an identifier.

A quick fix might be to re-write the query to use PartialObjectExpression: SELECT partial c.

{id, number}

...

(edited after a re-read and realization that a custom ResultSetMapping wouldn't cut it)

Comment by Benjamin Eberlei [ 25/Nov/12 ]

Allowing generic+complex pagination on scalar results is impossible for us, closing as can't fix.

Just use LIMITs yourself here or as suggested partial objects.

Comment by Stefano [ 18/Apr/13 ]

imho this pagination feature is quite useless if we are forced to fetch the complete Entity. Take for example a big table with a lot of data: extracting all the infos will take a lot of time... There should be a way to support the first query type

Comment by Busta Rhymes [ 10/Sep/13 ]

Did i miss something about Doctrine 2 ? What is the point if we can't use pagination? Selecting only several/needed fields is as standard practice as brushing your teeth every day. And pagination doesn't work when i select only specific fields (it throws 'Not all identifier properties can be found in the ResultSetMapping: id' error). I also thought that `$query->setFirstResult(20);` and `$query->setMaxResults(100);` works fine with joins but it turns out it doesn't work. It looks too complicated to integrate such common tool as pagination (I'm switching from CakePHP based ORM which works just fine with any kind of joins etc, no problems with pagination so far, but we are switching to Symfony 2 which goes with Doctrine 2 and actually i was recommended to use Doctrine 2, but i clearly see solid problems atm :/)

Comment by Zacharias Luiten [ 10/Sep/13 ]

@Busta Rhymes - Use partial entities (only the data of the fields you specify is fetched and set in the entity). As pagination is most of the time used just for displaying data, one is fine with that.

Comment by Thomas Godar [ 23/Oct/13 ]

I was pretty disappointed with this resolution. I fail to see how the tasks of a paginator fail to work with Query object foo yet work with Query object bar. They are the same objects. I would think a query is a query for the intent and purpose of the paginator, get a count, set limit and offset, give me back the page of results. All the more so if that Query has HydrationMode set to an array.

What does "generic+complex pagination" even mean?

Comment by Marco Pivetta [ 23/Oct/13 ]

As discussed in private, the problem is that doctrine needs a selected root entity in a DQL query in order to paginate the results.

That's a current limitation that cannot be worked around.

What could be done is giving a "meaning" to scalars being selected, so that they are upgraded to (for example) identifiers of a particular root entity.

That is still not worth it given the amount of bugs and increase in LOC that spawns from it.





[DDC-1918] Paginator gives strange results in the end of the resultset Created: 10/Jul/12  Updated: 29/Aug/12  Resolved: 29/Aug/12

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: Git Master
Fix Version/s: Git Master
Security Level: All

Type: Bug Priority: Major
Reporter: Alessandro Tagliapietra Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: paginator
Environment:

Mac OSX MAMP (apache + php 5.3.6 + mysql 5)



 Description   

Hello,
i've an image and a tag entities, image has a one to many with tags. I was using limit and offset and it was giving 7 images instead of 9 (limit is set to 9) due one image has 3 tags. So i started using paginator to resolve the join-fetch issue. It worked fine at the beginning of the resultset, the problem is at the end (i have 29 images), when i increase the offset.
These are the last 3 calls:

  • offset 27, distinct query is:

SELECT DISTINCT i0_.id AS id0, i0_.creation_time AS creation_time1 FROM Image i0_ LEFT JOIN Tag t1_ ON i0_.id = t1_.image WHERE i0_.owner = 1 ORDER BY i0_.creation_time DESC LIMIT 9 OFFSET 27

it returns 2 images, correct, so the final query is

SELECT i0_.id AS id0, i0_.name AS name1, i0_.status AS status2, i0_.last_processing AS last_processing3, i0_.creation_time AS creation_time4, i0_.height AS height5, i0_.width AS width6, i0_.layers AS layers7, i0_.foldersize AS foldersize8, i0_.sourcesize AS sourcesize9, i0_.title AS title10, i0_.description AS description11, i0_.originalUrl AS originalUrl12, i0_.private AS private13, i0_.watermark AS watermark14, i0_.favorite AS favorite15, t1_.id AS id16, t1_.tag AS tag17, i0_.owner AS owner18, t1_.image AS image19 FROM Image i0_ LEFT JOIN Tag t1_ ON i0_.id = t1_.image WHERE i0_.owner = 1 AND i0_.id IN (?, ?) ORDER BY i0_.creation_time DESC

with parameters the 2 images id, still correct.

  • offset 29, distinct query is:

SELECT DISTINCT i0_.id AS id0, i0_.creation_time AS creation_time1 FROM Image i0_ LEFT JOIN Tag t1_ ON i0_.id = t1_.image WHERE i0_.owner = 1 ORDER BY i0_.creation_time DESC LIMIT 9 OFFSET 29

which doesn't get any result, but it still goes on querying without id filter:

SELECT i0_.id AS id0, i0_.name AS name1, i0_.status AS status2, i0_.last_processing AS last_processing3, i0_.creation_time AS creation_time4, i0_.height AS height5, i0_.width AS width6, i0_.layers AS layers7, i0_.foldersize AS foldersize8, i0_.sourcesize AS sourcesize9, i0_.title AS title10, i0_.description AS description11, i0_.originalUrl AS originalUrl12, i0_.private AS private13, i0_.watermark AS watermark14, i0_.favorite AS favorite15, t1_.id AS id16, t1_.tag AS tag17, i0_.owner AS owner18, t1_.image AS image19 FROM Image i0_ LEFT JOIN Tag t1_ ON i0_.id = t1_.image WHERE i0_.owner = 1 ORDER BY i0_.creation_time DESC LIMIT 9 OFFSET 29

and it gets the last 2 images maybe due the fetch join like as i'm not using paginator.



 Comments   
Comment by Alessandro Tagliapietra [ 15/Aug/12 ]

No updates?

Comment by Benjamin Eberlei [ 15/Aug/12 ]

Sorry I am swamped with work at the moment and other important projects, I couldnt look into it yet.

Comment by Alessandro Tagliapietra [ 27/Aug/12 ]

Commenting the if this part:

Paginator.php
            // don't do this for an empty id array
            //if (count($ids) > 0) {
                $namespace = WhereInWalker::PAGINATOR_ID_ALIAS;

                $whereInQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, array('Doctrine\ORM\Tools\Pagination\WhereInWalker'));
                $whereInQuery->setHint(WhereInWalker::HINT_PAGINATOR_ID_COUNT, count($ids));
                $whereInQuery->setFirstResult(null)->setMaxResults(null);
                foreach ($ids as $i => $id) {
                    $i++;
                    $whereInQuery->setParameter("{$namespace}_{$i}", $id);
                }
            //}

make it works, for me is better, because if you don't have results to show as the result of the select distinct is empty you shouldn't have any result at all. Because if you don't add the "WHERE IN" query you get the results joined with no difference between using or not the paginator.

Comment by Benjamin Eberlei [ 29/Aug/12 ]

This is weird, an empty IN() normally produces an error. We could add a non matching condition instead, like 0 = 1.

Comment by Alessandro Tagliapietra [ 29/Aug/12 ]

Not really, in my case (mysql) it creates the following query:

SELECT i0_.id AS id0, i0_.name AS name1, i0_.status AS status2, i0_.last_processing AS last_processing3, i0_.creation_time AS creation_time4, i0_.height AS height5, i0_.width AS width6, i0_.layers AS layers7, i0_.foldersize AS foldersize8, i0_.sourcesize AS sourcesize9, i0_.title AS title10, i0_.description AS description11, i0_.originalUrl AS originalUrl12, i0_.private AS private13, i0_.watermark AS watermark14, i0_.favorite AS favorite15, t1_.id AS id16, t1_.tag AS tag17, i0_.owner AS owner18, t1_.image AS image19 FROM Image i0_ LEFT JOIN Tag t1_ ON i0_.id = t1_.image WHERE i0_.owner = 1 AND i0_.id IS NULL ORDER BY i0_.creation_time DESC

so it sets "AND i0_.id IS NULL" which solves the problem, the best solution for me is to directly return an empty set if the DISTINCT gives no results.

Comment by Alessandro Tagliapietra [ 29/Aug/12 ]

Solved with commit bc2476f342ceda98559b87a314806581cf969796





Generated at Fri Aug 01 11:55:01 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.