doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-23 22:42:18 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

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

New Issue
Closed
opened 2026-01-22 15:11:10 +01:00 by admin · 2 comments
admin commented 2026-01-22 15:11:10 +01:00
Owner
Copy Link

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:10 +01:00
admin closed this issue 2026-01-22 15:11:10 +01:00
admin commented 2026-01-22 15:11:11 +01:00
Author
Owner
Copy Link

@stesie commented on GitHub (May 23, 2017):

Well, so I had another look at this issue myself, and the SQL that probably should be generated is

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

... mind the parentheses around the CTI JOIN. SqlWalker::walkJoinAssociationDeclaration already has similar syntax around line 1043:

        if ($targetClass->isInheritanceTypeJoined()) {
            $ctiJoins = $this->_generateClassTableInheritanceJoins($targetClass, $joinedDqlAlias);
            // If we have WITH condition, we need to build nested joins for target class table and cti joins
            if ($withCondition) {
                $sql .= '(' . $targetTableJoin['table'] . $ctiJoins . ') ON ' . $targetTableJoin['condition'];
            } else {
                $sql .= $targetTableJoin['table'] . ' ON ' . $targetTableJoin['condition'] . $ctiJoins;
            }
        } else {
            $sql .= $targetTableJoin['table'] . ' ON ' . $targetTableJoin['condition'];
        }

If I apply this also to the code path through walkJoin and walkRangeVariableDeclaration like this:

diff --git a/lib/Doctrine/ORM/Query/SqlWalker.php b/lib/Doctrine/ORM/Query/SqlWalker.php
index b39280b39..98c30c195 100644
--- a/lib/Doctrine/ORM/Query/SqlWalker.php
+++ b/lib/Doctrine/ORM/Query/SqlWalker.php
@@ -890,7 +890,7 @@ public function walkRangeVariableDeclaration($rangeVariableDeclaration)
         );
 
         if ($class->isInheritanceTypeJoined()) {
-            $sql .= $this->_generateClassTableInheritanceJoins($class, $dqlAlias);
+            $sql = '(' . $sql . $this->_generateClassTableInheritanceJoins($class, $dqlAlias) . ')';
         }
 
         return $sql;
@@ -1136,10 +1136,6 @@ public function walkJoin($join)
                     $conditions[] = '(' . $this->walkConditionalExpression($join->conditionalExpression) . ')';
                 }
 
-                $condExprConjunction = ($class->isInheritanceTypeJoined() && $joinType != AST\Join::JOIN_TYPE_LEFT && $joinType != AST\Join::JOIN_TYPE_LEFTOUTER)
-                    ? ' AND '
-                    : ' ON ';
-
                 $sql .= $this->walkRangeVariableDeclaration($joinDeclaration);
 
                 // Apply remaining inheritance restrictions
@@ -1157,7 +1153,7 @@ public function walkJoin($join)
                 }
 
                 if ($conditions) {
-                    $sql .= $condExprConjunction . implode(' AND ', $conditions);
+                    $sql .= ' ON ' . implode(' AND ', $conditions);
                 }
 
                 break;

... the sql statement from above is produced. And it works fine with PostgreSQL as well as MySQL.

Let me know if that's the way to go, then I'll happily provide a pull request...

@stesie commented on GitHub (May 23, 2017): Well, so I had another look at this issue myself, and the SQL that probably should be generated is ```sql SELECT ... FROM posts p0_ INNER JOIN (authors a2_ INNER JOIN users u1_ ON a2_.id = u1_.id) ON (p0_.authorId = u1_.id) ``` ... mind the parentheses around the CTI JOIN. `SqlWalker::walkJoinAssociationDeclaration` already has similar syntax around line 1043: ```php if ($targetClass->isInheritanceTypeJoined()) { $ctiJoins = $this->_generateClassTableInheritanceJoins($targetClass, $joinedDqlAlias); // If we have WITH condition, we need to build nested joins for target class table and cti joins if ($withCondition) { $sql .= '(' . $targetTableJoin['table'] . $ctiJoins . ') ON ' . $targetTableJoin['condition']; } else { $sql .= $targetTableJoin['table'] . ' ON ' . $targetTableJoin['condition'] . $ctiJoins; } } else { $sql .= $targetTableJoin['table'] . ' ON ' . $targetTableJoin['condition']; } ``` If I apply this also to the code path through `walkJoin` and `walkRangeVariableDeclaration` like this: ```diff diff --git a/lib/Doctrine/ORM/Query/SqlWalker.php b/lib/Doctrine/ORM/Query/SqlWalker.php index b39280b39..98c30c195 100644 --- a/lib/Doctrine/ORM/Query/SqlWalker.php +++ b/lib/Doctrine/ORM/Query/SqlWalker.php @@ -890,7 +890,7 @@ public function walkRangeVariableDeclaration($rangeVariableDeclaration) ); if ($class->isInheritanceTypeJoined()) { - $sql .= $this->_generateClassTableInheritanceJoins($class, $dqlAlias); + $sql = '(' . $sql . $this->_generateClassTableInheritanceJoins($class, $dqlAlias) . ')'; } return $sql; @@ -1136,10 +1136,6 @@ public function walkJoin($join) $conditions[] = '(' . $this->walkConditionalExpression($join->conditionalExpression) . ')'; } - $condExprConjunction = ($class->isInheritanceTypeJoined() && $joinType != AST\Join::JOIN_TYPE_LEFT && $joinType != AST\Join::JOIN_TYPE_LEFTOUTER) - ? ' AND ' - : ' ON '; - $sql .= $this->walkRangeVariableDeclaration($joinDeclaration); // Apply remaining inheritance restrictions @@ -1157,7 +1153,7 @@ public function walkJoin($join) } if ($conditions) { - $sql .= $condExprConjunction . implode(' AND ', $conditions); + $sql .= ' ON ' . implode(' AND ', $conditions); } break; ``` ... the sql statement from above is produced. And it works fine with PostgreSQL as well as MySQL. Let me know if that's the way to go, then I'll happily provide a pull request...
admin commented 2026-01-22 15:11:11 +01:00
Author
Owner
Copy Link

@Deltachaos commented on GitHub (Nov 15, 2017):

This change is causing unnecessary bracets if there is no other JOIN in the brackets. This is invalid syntax (at least for SQL Server). Please have a look at the fix #6812

@Deltachaos commented on GitHub (Nov 15, 2017): This change is causing unnecessary bracets if there is no other JOIN in the brackets. This is invalid syntax (at least for SQL Server). Please have a look at the fix #6812
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5554
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?