DDC-2917: Inheritance using joins generates invalid SQL when used in SELECT queries with GROUP BY on Postgresql #3633

New Issue

2026-01-22T14:24:18+01:00

admin commented

2026-01-22 14:24:18 +01:00

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

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user tom.pryor:

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

Originally created by @doctrinebot on GitHub (Jan 15, 2014). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user tom.pryor: 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 ```

admin added the Bug label 2026-01-22 14:24:18 +01:00

admin commented

2026-01-22 14:24:18 +01:00

@doctrinebot commented on GitHub (Aug 24, 2015):

Comment created by ambroisemaupate:

Same issue for me:

The following code works for mysql and crash on pgsql.

        $query = $this->createQueryBuilder('ns');
        $query->select('ns');
        $query->addSelect('log');
        $query->innerJoin('ns.logs', 'log');
        $query->setMaxResults($maxResult);
        $query->orderBy('log.datetime', 'DESC');
        $query->groupBy('ns.id');
        $query = $query->getQuery();

        try {
            return $query->getResult();
        } catch (NoResultException $e) {
            return null;
        }

An exception occurred while executing 'SELECT n0*.id AS id_0, n0_.title AS title_1, n0_.meta_title AS meta_title_2, n0_.meta_keywords AS meta_keywords_3, n0_.meta_description AS meta_description_4, n1_.content AS content_5, n3_.content AS content_6, l4_.id AS id_7, l4_.message AS message_8, l4_.level AS level_9, l4_.datetime AS datetime_10, l4_.client_ip AS client_ip_11, n0_.discr AS discr_12, n0_.node_id AS node_id_13, n0_.translation_id AS translation_id_14, l4_.user_id AS user_id_15, l4_.node_source_id AS node_source_id_16 FROM nodes_sources n0_ LEFT JOIN ns_basicblock n1_ ON n0_.id = n1_.id LEFT JOIN ns_neutral n2_ ON n0_.id = n2_.id LEFT JOIN ns_page n3_ ON n0_.id = n3_.id INNER JOIN log l4_ ON n0_.id = l4_.node_source_id GROUP BY n0_.id ORDER BY l4_.datetime DESC LIMIT 4': SQLSTATE[42803]: Grouping error: 7 ERROR: column "n1_.content" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...ds_3, n0_.meta_description AS meta_description_4, n1*.conten...

Seen on https://github.com/roadiz/roadiz/issues/169

@doctrinebot commented on GitHub (Aug 24, 2015): Comment created by ambroisemaupate: Same issue for me: The following code works for mysql and crash on pgsql. ``` none $query = $this->createQueryBuilder('ns'); $query->select('ns'); $query->addSelect('log'); $query->innerJoin('ns.logs', 'log'); $query->setMaxResults($maxResult); $query->orderBy('log.datetime', 'DESC'); $query->groupBy('ns.id'); $query = $query->getQuery(); try { return $query->getResult(); } catch (NoResultException $e) { return null; } ``` ``` An exception occurred while executing 'SELECT n0*.id AS id_0, n0_.title AS title_1, n0_.meta_title AS meta_title_2, n0_.meta_keywords AS meta_keywords_3, n0_.meta_description AS meta_description_4, n1_.content AS content_5, n3_.content AS content_6, l4_.id AS id_7, l4_.message AS message_8, l4_.level AS level_9, l4_.datetime AS datetime_10, l4_.client_ip AS client_ip_11, n0_.discr AS discr_12, n0_.node_id AS node_id_13, n0_.translation_id AS translation_id_14, l4_.user_id AS user_id_15, l4_.node_source_id AS node_source_id_16 FROM nodes_sources n0_ LEFT JOIN ns_basicblock n1_ ON n0_.id = n1_.id LEFT JOIN ns_neutral n2_ ON n0_.id = n2_.id LEFT JOIN ns_page n3_ ON n0_.id = n3_.id INNER JOIN log l4_ ON n0_.id = l4_.node_source_id GROUP BY n0_.id ORDER BY l4_.datetime DESC LIMIT 4': SQLSTATE[42803]: Grouping error: 7 ERROR: column "n1_.content" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...ds_3, n0_.meta_description AS meta_description_4, n1*.conten... ``` Seen on https://github.com/roadiz/roadiz/issues/169

admin commented

2026-01-22 14:24:19 +01:00

@dbykadorov commented on GitHub (Jul 27, 2016):

Hello! Does anybody knows workarround for this issue?

@dbykadorov commented on GitHub (Jul 27, 2016): Hello! Does anybody knows workarround for this issue?

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