Query builder yields invalid SQL with JOINED inheritance type on PostgreSQL #5552

New Issue

admin · 2026-01-22T15:11:08+01:00

admin commented

2026-01-22 15:11:08 +01:00

Originally created by @stesie on GitHub (May 22, 2017).

Originally assigned to: @Ocramius on GitHub.

Given a simple set of entities (adapted to a simpler domain here):

a Post belongs to an Author which derives from User
the inheritance is mapped by JOINED type
yet the relation between Post and Author is not mapped (for reasons, as the real case is less simple)

<?php
namespace Entity;

/**
 * @Entity @Table(name="posts")
 */
class Post
{
    /** @Id @GeneratedValue @Column(type="integer") */
    public $id;

    /** @Column(type="integer") */
    public $authorId;

    /** @Column(length=100) */
    public $title;

    /** @Column(type="text") */
    public $text;
}

<?php
namespace Entity;

/**
 * @Entity @Table(name="authors")
 */
class Author extends User
{
    /** @Column(length=50) */
    public $displayName;
}

<?php
namespace Entity;

/**
 * @Entity @Table(name="users")
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="discr", type="string")
 * @DiscriminatorMap({"author" = "Author"})
 */
abstract class User
{
    /** @Id @GeneratedValue @Column(type="integer") */
    public $id;

    /** @Column(length=50) */
    public $username;

    /** @Column(length=100) */
    public $password;
}

If I then instanciate and use a QueryBuilder like this:

$qb = $entityManager->createQueryBuilder();

$qb
    ->select('p', 'a')
    ->from('Entity\\Post', 'p')
    ->innerJoin('Entity\\Author', 'a', 'WITH', 'p.authorId = a.id')
    ;

it generates the following SQL

SELECT ... FROM posts p0_ INNER JOIN authors a2_ INNER JOIN users u1_ ON a2_.id = u1_.id AND (p0_.authorId = u1_.id)

Which is valid for MySQL, however is not valid for PostgreSQL (tried with 9.6)

PostgreSQL seems to require an ON clause with each and every INNER JOIN (interestingly as opposed to LEFT JOIN). This is, the following query works with it:

SELECT ... FROM posts p0_ INNER JOIN users u1_ ON (p0_.authorId = u1_.id) INNER JOIN authors a2_ ON (a2_.id = u1_.id) ;

Pushing all conditions to WHERE also works on PostgreSQL:

SELECT ... FROM posts p0_ , authors a2_ , users u1_ WHERE (a2_.id = u1_.id)  AND  (p0_.authorId = u1_.id);

I haven't yet dug deeper, mainly as I never dived into Doctrine ORM code ... please let me know if you agree on this being a bug, ... I so far have no idea where it goes wrong, but let me know if I can support you, maybe by providing test code or start digging ...

Originally created by @stesie on GitHub (May 22, 2017). Originally assigned to: @Ocramius on GitHub. Given a simple set of entities (adapted to a simpler domain here): * a `Post` belongs to an `Author` which derives from `User` * the inheritance is mapped by JOINED type * yet the relation between `Post` and `Author` is not mapped (for reasons, as the real case is less simple) ```php <?php namespace Entity; /** * @Entity @Table(name="posts") */ class Post { /** @Id @GeneratedValue @Column(type="integer") */ public $id; /** @Column(type="integer") */ public $authorId; /** @Column(length=100) */ public $title; /** @Column(type="text") */ public $text; } ``` ```php <?php namespace Entity; /** * @Entity @Table(name="authors") */ class Author extends User { /** @Column(length=50) */ public $displayName; } ``` ```php <?php namespace Entity; /** * @Entity @Table(name="users") * @InheritanceType("JOINED") * @DiscriminatorColumn(name="discr", type="string") * @DiscriminatorMap({"author" = "Author"}) */ abstract class User { /** @Id @GeneratedValue @Column(type="integer") */ public $id; /** @Column(length=50) */ public $username; /** @Column(length=100) */ public $password; } ``` If I then instanciate and use a QueryBuilder like this: ```php $qb = $entityManager->createQueryBuilder(); $qb ->select('p', 'a') ->from('Entity\\Post', 'p') ->innerJoin('Entity\\Author', 'a', 'WITH', 'p.authorId = a.id') ; ``` it generates the following SQL ```sql SELECT ... FROM posts p0_ INNER JOIN authors a2_ INNER JOIN users u1_ ON a2_.id = u1_.id AND (p0_.authorId = u1_.id) ``` Which is valid for MySQL, however is *not* valid for PostgreSQL (tried with 9.6) PostgreSQL seems to require an `ON` clause with each and every `INNER JOIN` (interestingly as opposed to `LEFT JOIN`). This is, the following query works with it: ```sql SELECT ... FROM posts p0_ INNER JOIN users u1_ ON (p0_.authorId = u1_.id) INNER JOIN authors a2_ ON (a2_.id = u1_.id) ; ``` Pushing all conditions to `WHERE` also works on PostgreSQL: ```sql SELECT ... FROM posts p0_ , authors a2_ , users u1_ WHERE (a2_.id = u1_.id) AND (p0_.authorId = u1_.id); ``` I haven't yet dug deeper, mainly as I never dived into Doctrine ORM code ... please let me know if you agree on this being a bug, ... I so far have no idea where it goes wrong, but let me know if I can support you, maybe by providing test code or start digging ...

admin added the Bug label 2026-01-22 15:11:08 +01:00

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