DDC-1918: Paginator gives strange results in the end of the resultset #2419

New Issue

admin · 2026-01-22T13:52:23+01:00

admin commented

2026-01-22 13:52:23 +01:00

Originally created by @doctrinebot on GitHub (Jul 10, 2012).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user alex88:

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.

Originally created by @doctrinebot on GitHub (Jul 10, 2012). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user alex88: 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.

admin added the Bug label 2026-01-22 13:52:23 +01:00

admin closed this issue

2026-01-22 13:52:24 +01:00

admin commented

2026-01-22 13:52:25 +01:00

@doctrinebot commented on GitHub (Aug 15, 2012):

Comment created by alex88:

No updates?

@doctrinebot commented on GitHub (Aug 15, 2012): Comment created by alex88: No updates?

admin commented

2026-01-22 13:52:26 +01:00

@doctrinebot commented on GitHub (Aug 15, 2012):

Comment created by @beberlei:

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

@doctrinebot commented on GitHub (Aug 15, 2012): Comment created by @beberlei: Sorry I am swamped with work at the moment and other important projects, I couldnt look into it yet.

admin commented

2026-01-22 13:52:26 +01:00

@doctrinebot commented on GitHub (Aug 27, 2012):

Comment created by alex88:

Commenting the if this part:

            // 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<ins></ins>;
                    $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.

@doctrinebot commented on GitHub (Aug 27, 2012): Comment created by alex88: Commenting the if this part: ``` // 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<ins></ins>; $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.

admin commented

2026-01-22 13:52:27 +01:00

@doctrinebot commented on GitHub (Aug 29, 2012):

Comment created by @beberlei:

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

@doctrinebot commented on GitHub (Aug 29, 2012): Comment created by @beberlei: This is weird, an empty IN() normally produces an error. We could add a non matching condition instead, like 0 = 1.

admin commented

2026-01-22 13:52:27 +01:00

@doctrinebot commented on GitHub (Aug 29, 2012):

Comment created by alex88:

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.

@doctrinebot commented on GitHub (Aug 29, 2012): Comment created by alex88: 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.

admin commented

2026-01-22 13:52:28 +01:00

@doctrinebot commented on GitHub (Aug 29, 2012):

Comment created by alex88:

Solved with commit bc2476f342

@doctrinebot commented on GitHub (Aug 29, 2012): Comment created by alex88: Solved with commit bc2476f342ceda98559b87a314806581cf969796

admin commented

2026-01-22 13:52:28 +01:00

@doctrinebot commented on GitHub (Aug 29, 2012):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Aug 29, 2012): Issue was closed with resolution "Fixed"

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#2419