DDC-2236: SUM(..) with Pagination gives incorrect result #2813

New Issue

admin · 2026-01-22T14:04:37+01:00

admin commented

2026-01-22 14:04:37 +01:00

Originally created by @doctrinebot on GitHub (Jan 11, 2013).

Originally assigned to: @ostrolucky on GitHub.

Jira issue originally created by user olegk:

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

Originally created by @doctrinebot on GitHub (Jan 11, 2013). Originally assigned to: @ostrolucky on GitHub. Jira issue originally created by user olegk: 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

admin added the Documentation label 2026-01-22 14:04:37 +01:00

admin closed this issue

2026-01-22 14:04:37 +01:00

admin commented

2026-01-22 14:04:38 +01:00

@doctrinebot commented on GitHub (Feb 9, 2013):

Comment created by @asm89:

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

@doctrinebot commented on GitHub (Feb 9, 2013): Comment created by @asm89: Can you also test this with doctrine >= 2.3? The pagination code changed quite a lot.

admin commented

2026-01-22 14:04:39 +01:00

@doctrinebot commented on GitHub (Feb 10, 2013):

Comment created by olegk:

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?

@doctrinebot commented on GitHub (Feb 10, 2013): Comment created by olegk: 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 ``` none $query = $this->getDoctrine()->getEntityManager()->getRepository('MyBundle:Invoice') ->createQueryBuilder('q') ->select('q', 'SUM(q.amount) AS amount') ->groupBy('q.customer') ; ``` ``` sql 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 ``` none $pager = new Pagerfanta(new DoctrineORMAdapter($query, false)); ``` I get this sql ``` 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?

admin commented

2026-01-22 14:04:39 +01:00

@doctrinebot commented on GitHub (Feb 10, 2013):

Comment created by @ocramius:

Updating to Documentation issue.

@doctrinebot commented on GitHub (Feb 10, 2013): Comment created by @ocramius: Updating to Documentation issue.

admin commented

2026-01-22 14:04:39 +01:00

@Ocramius commented on GitHub (Aug 5, 2018):

Handled in #7333

@Ocramius commented on GitHub (Aug 5, 2018): Handled in #7333

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#2813