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

Open
doctrinebot opened this Issue Jan 15, 2014 · 2 comments

3 participants

@doctrinebot

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

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 roadiz/roadiz#169

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
@dbykadorov
dbykadorov commented Jul 27, 2016 edited

Hello! Does anybody knows workarround for this issue?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment