Uploaded image for project: '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
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All

      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

        Hide
        ambroisemaupate Ambroise Maupate added a comment - - edited

        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

        Show
        ambroisemaupate Ambroise Maupate added a comment - - edited 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

          People

          • Assignee:
            beberlei Benjamin Eberlei
            Reporter:
            tom.pryor Tom Pryor
          • Votes:
            2 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated: