Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2890

Paginator generates invalid sql for some dql with setUseOutputWalkers(false) and $fetchJoinCollection = true

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.4.1
    • Fix Version/s: 2.5
    • Component/s: Tools
    • Security Level: All
    • Labels:
    • 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

        Activity

        Show
        Guilherme Blanco added a comment - As of https://github.com/doctrine/doctrine2/commit/be1cc14a9c8641774d614f788103cef4a5373bb1 issue is now fixed.

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Jiri Kavalik
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: