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

Closed
doctrinebot opened this Issue Jan 7, 2014 · 2 comments

2 participants

@doctrinebot

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
@doctrinebot

Comment created by @guilhermeblanco:

As of be1cc14 issue is now fixed.

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added this to the 2.5 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment