Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2917

Inheritance using joins generates invalid SQL when used in SELECT queries with GROUP BY on Postgresql

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None

      Description

      Say you have an entity with InheritanceType("JOINED"), some child entities defined in the DiscriminatorMap which include additional columns and are using PostgreSQL then try and execute a DQL query as below:

      SELECT parententity FROM MyStuff\Entity\ParentEntity parententity GROUP BY parententity
      

      Note: This is simplified for ease of reproduction, obviously the GROUP BY is pointless but in my actual query, which is significantly larger, the GROUP BY is actually useful.

      PostgreSQL will give the following error:

      SQLSTATE[42803]: Grouping error: 7 ERROR:  column "f2_.my_additional_column" must appear in the GROUP BY clause or be used in an aggregate function
      

      Where f2_ is an alias in the actual SQL for one of the child entities.

      This is because the SELECT in the actual SQL attempts to fetch the columns from the joined child entities but does not include them in the GROUP BY clause.

      Bad SQL generated:

      SELECT f0_.id AS id0, <f0_.* columns>, f2_.my_additional_column AS my_additional_column6
      FROM parententitys f0_
      LEFT JOIN childentity f2_ ON f0_.id = f2_.id
      GROUP BY f0_.id, <f0_.* columns>
      

      The SQL should be:

      SELECT f0_.id AS id0, <f0_.* columns>, f2_.my_additional_column AS my_additional_column6
      FROM parententitys f0_
      LEFT JOIN childentity f2_ ON f0_.id = f2_.id
      GROUP BY f0_.id, <f0_.* columns>, f2_.my_additional_column
      

      N.b later versions of PostgreSQL don't require the GROUP BY on all the columns, just the primary key. Therefore the following would be sufficient:

      SELECT f0_.id AS id0, <f0_.* columns>, f2_.my_additional_column AS my_additional_column6
      FROM parententitys f0_
      LEFT JOIN childentity f2_ ON f0_.id = f2_.id
      GROUP BY f0_.id, f2_.id
      

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Tom Pryor
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: