DDC-2890: Paginator generates invalid sql for some dql with setUseOutputWalkers(false) and $fetchJoinCollection = true #3604

New Issue

admin · 2026-01-22T14:23:35+01:00

admin commented

2026-01-22 14:23:35 +01:00

Originally created by @doctrinebot on GitHub (Jan 7, 2014).

Originally assigned to: @guilhermeblanco on GitHub.

Jira issue originally created by user jkavalik:

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

Originally created by @doctrinebot on GitHub (Jan 7, 2014). Originally assigned to: @guilhermeblanco on GitHub. Jira issue originally created by user jkavalik: 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: ``` sql SELECT Transaction FROM Transaction\Entity\Transaction Transaction ORDER BY Transaction.balance asc ``` SQL: ``` 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: ``` 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: ``` sql SELECT Transaction FROM Transaction\Entity\Transaction Transaction ORDER BY Transaction.type asc ``` SQL: ``` 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: ``` sql 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: ``` 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

admin added the Bug label 2026-01-22 14:23:35 +01:00

admin closed this issue

2026-01-22 14:23:35 +01:00

admin commented

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

@doctrinebot commented on GitHub (Apr 17, 2014):

Comment created by @guilhermeblanco:

As of be1cc14a9c issue is now fixed.

@doctrinebot commented on GitHub (Apr 17, 2014): Comment created by @guilhermeblanco: As of https://github.com/doctrine/doctrine2/commit/be1cc14a9c8641774d614f788103cef4a5373bb1 issue is now fixed.

admin commented

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

@doctrinebot commented on GitHub (Apr 17, 2014):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Apr 17, 2014): 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#3604