DDC-1256: Generated SQL error with DQL WITH and JOINED inheritance #1582

New Issue

admin · 2026-01-22T13:18:54+01:00

admin commented

2026-01-22 13:18:54 +01:00

Originally created by @doctrinebot on GitHub (Jul 6, 2011).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user toxygene:

I've created an entity that has a one to one relationship to a class in an inheritance tree and I'm using class table inheritance in Doctrine. When I try to add a DQL WITH statement on a column in the super class to the join, the generated SQL incorrectly places the statement in the child classes JOIN ON section.

Here's the DQL:

SELECT p FROM Fampus*Entity*Photo p LEFT JOIN p.flag f WITH f.status='ok'

Here's the generated SQL:

SELECT p0*.id AS id0, p0_.name AS name1, p0_.path AS path2, p0_.type AS type3, p0_.reference_id AS reference_id4, p0_.view_count AS view_count5, p0_.created AS created6, p0_.modified AS modified7, p0_.event_id AS event_id8, p0_.user_id AS user_id9, p0_.school_id AS school_id10, p0_.flag_id AS flag_id11 FROM photos p0_ LEFT JOIN flaggedcontent_photo f1_ ON p0_.flag_id = f1_.id AND (f2_.status = 'ok') LEFT JOIN flaggedcontent f2_ ON f1_.id = f2*.id

Note that f2_.status = 'ok' is the correct statement, but it is in the wrong LEFT JOIN ON section.

Here are my entities (significantly clipped):

/****
 * @Table(name="photos")
*/
class Photo {
    /****
     * @OneToOne(targetEntity="FlaggedContent_Photo", mappedBy="photo")
     * @JoinColumn(nullable=true)
     */
    protected $flag;
}

/****
 * @DiscriminatorColumn(name="type")
 * @DiscriminatorMap({
 *     "photo" = "FlaggedContent_Photo"
 * })
 * @InheritanceType("JOINED")
 * @Table(name="flaggedcontent")
*/
abstract class FlaggedContent {
    /****
     * Database identifier
     *
     * @Id
     * @Column(type="integer")
     * @GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /****
     * Status
     *
     * @Column(type="text")
     * @var string
     */
    protected $status;
}

/****
 * @Entity
 * @Table(name="flaggedcontent_photo")
 */
class FlaggedContent_Photo extends FlaggedContent {
    /****
     * @OneToOne(targetEntity="Photo", inversedBy="flag")
     */
    protected $photo;
}

Originally created by @doctrinebot on GitHub (Jul 6, 2011). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user toxygene: I've created an entity that has a one to one relationship to a class in an inheritance tree and I'm using class table inheritance in Doctrine. When I try to add a DQL WITH statement on a column in the super class to the join, the generated SQL incorrectly places the statement in the child classes JOIN ON section. Here's the DQL: ``` SELECT p FROM Fampus*Entity*Photo p LEFT JOIN p.flag f WITH f.status='ok' ``` Here's the generated SQL: ``` SELECT p0*.id AS id0, p0_.name AS name1, p0_.path AS path2, p0_.type AS type3, p0_.reference_id AS reference_id4, p0_.view_count AS view_count5, p0_.created AS created6, p0_.modified AS modified7, p0_.event_id AS event_id8, p0_.user_id AS user_id9, p0_.school_id AS school_id10, p0_.flag_id AS flag_id11 FROM photos p0_ LEFT JOIN flaggedcontent_photo f1_ ON p0_.flag_id = f1_.id AND (f2_.status = 'ok') LEFT JOIN flaggedcontent f2_ ON f1_.id = f2*.id ``` Note that f2_.status = 'ok' is the correct statement, but it is in the wrong LEFT JOIN ON section. Here are my entities (significantly clipped): ``` /**** * @Table(name="photos") */ class Photo { /**** * @OneToOne(targetEntity="FlaggedContent_Photo", mappedBy="photo") * @JoinColumn(nullable=true) */ protected $flag; } /**** * @DiscriminatorColumn(name="type") * @DiscriminatorMap({ * "photo" = "FlaggedContent_Photo" * }) * @InheritanceType("JOINED") * @Table(name="flaggedcontent") */ abstract class FlaggedContent { /**** * Database identifier * * @Id * @Column(type="integer") * @GeneratedValue(strategy="AUTO") */ protected $id; /**** * Status * * @Column(type="text") * @var string */ protected $status; } /**** * @Entity * @Table(name="flaggedcontent_photo") */ class FlaggedContent_Photo extends FlaggedContent { /**** * @OneToOne(targetEntity="Photo", inversedBy="flag") */ protected $photo; } ```

admin added the Bug label 2026-01-22 13:18:54 +01:00

admin closed this issue

2026-01-22 13:18:56 +01:00

admin commented

2026-01-22 13:18:56 +01:00

@doctrinebot commented on GitHub (Jul 6, 2011):

duplicates DDC-349: Add support for specifying precedence in joins in DQL
is duplicated by DDC-2131: Fetch join not working on class table inheritance
is referenced by DDC-2869: [GH-886] [DDC-1256] Fix applying ON/WITH conditions to first join in Class Table Inheritance

@doctrinebot commented on GitHub (Jul 6, 2011): - duplicates [DDC-349: Add support for specifying precedence in joins in DQL](http://www.doctrine-project.org/jira/browse/DDC-349) - is duplicated by [DDC-2131: Fetch join not working on class table inheritance](http://www.doctrine-project.org/jira/browse/DDC-2131) - is referenced by [DDC-2869: [GH-886] [DDC-1256] Fix applying ON/WITH conditions to first join in Class Table Inheritance](http://www.doctrine-project.org/jira/browse/DDC-2869)

admin commented

2026-01-22 13:18:58 +01:00

@doctrinebot commented on GitHub (Jul 6, 2011):

Comment created by mridgway:

I created a test for this: 1bb26a4618

I'll see if I can figure this out.

@doctrinebot commented on GitHub (Jul 6, 2011): Comment created by mridgway: I created a test for this: https://github.com/mridgway/doctrine2/commit/1bb26a46188f180270d723e395ee707443ebdda1 I'll see if I can figure this out.

admin commented

2026-01-22 13:18:58 +01:00

@doctrinebot commented on GitHub (Jul 11, 2011):

Comment created by mridgway:

It doesn't seem that you would get the correct result if the WITH statement was on the 'flaggedcontent' table anyway. In order for this to work, the query would have to join 'flaggedcontent' first and then join to 'flaggedcontent_photo' based on f2_.id. While we can probably (but not easily) fix where the condition is placed, I don't think this will ever be able to give the expected results for ALL cases.

@doctrinebot commented on GitHub (Jul 11, 2011): Comment created by mridgway: It doesn't seem that you would get the correct result if the WITH statement was on the 'flaggedcontent' table anyway. In order for this to work, the query would have to join 'flaggedcontent' first and then join to 'flaggedcontent_photo' based on f2_.id. While we can probably (but not easily) fix where the condition is placed, I don't think this will ever be able to give the expected results for ALL cases.

admin commented

2026-01-22 13:18:59 +01:00

@doctrinebot commented on GitHub (Jul 12, 2011):

Comment created by @beberlei:

Formatting

@doctrinebot commented on GitHub (Jul 12, 2011): Comment created by @beberlei: Formatting

admin commented

2026-01-22 13:19:00 +01:00

@doctrinebot commented on GitHub (Jul 12, 2011):

Comment created by @beberlei:

This issue is unfixable, we tend towards throwing an exception in this case to notify developers that they cannot do this.

The only way to make it work is to move the status check to the WHERE clause.

@doctrinebot commented on GitHub (Jul 12, 2011): Comment created by @beberlei: This issue is unfixable, we tend towards throwing an exception in this case to notify developers that they cannot do this. The only way to make it work is to move the status check to the WHERE clause.

admin commented

2026-01-22 13:19:00 +01:00

@doctrinebot commented on GitHub (Aug 14, 2011):

Comment created by @guilhermeblanco:

This issue is fixable by applying what we call as nested joins when generating the SQL.

This issue report #DDC-349 (http://www.doctrine-project.org/jira/browse/DDC-349) suggest us to support it in DQL, but I think we can introduce this generation on SQL only.
It's not a trivial implementation, but it would solve a couple of other issue I was looking into.

Cheers,

@doctrinebot commented on GitHub (Aug 14, 2011): Comment created by @guilhermeblanco: This issue is fixable by applying what we call as nested joins when generating the SQL. This issue report #[DDC-349](http://www.doctrine-project.org/jira/browse/DDC-349) (http://www.doctrine-project.org/jira/browse/[DDC-349](http://www.doctrine-project.org/jira/browse/DDC-349)) suggest us to support it in DQL, but I think we can introduce this generation on SQL only. It's not a trivial implementation, but it would solve a couple of other issue I was looking into. Cheers,

admin commented

2026-01-22 13:19:00 +01:00

@doctrinebot commented on GitHub (May 1, 2013):

Comment created by @beberlei:

Duplicate of DDC-349

@doctrinebot commented on GitHub (May 1, 2013): Comment created by @beberlei: Duplicate of [DDC-349](http://www.doctrine-project.org/jira/browse/DDC-349)

admin commented

2026-01-22 13:19:01 +01:00

@doctrinebot commented on GitHub (May 1, 2013):

Issue was closed with resolution "Duplicate"

@doctrinebot commented on GitHub (May 1, 2013): Issue was closed with resolution "Duplicate"

admin commented

2026-01-22 13:19:02 +01:00

@doctrinebot commented on GitHub (Dec 22, 2013):

Comment created by strate:

I do not understand why this issue is resolved. It is still reproduces!
Look to the source code:
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Query/SqlWalker.php#L989

// Handle WITH clause
        if ($condExpr !== null) {
            // Phase 2 AST optimization: Skip processing of ConditionalExpression
            // if only one ConditionalTerm is defined
            $sql .= ' AND (' . $this->walkConditionalExpression($condExpr) . ')';
        }

        // FIXME: these should either be nested or all forced to be left joins (DDC-XXX)
        if ($targetClass->isInheritanceTypeJoined()) {
            $sql .= $this->_generateClassTableInheritanceJoins($targetClass, $joinedDqlAlias);
        }

As you can see, sql conditions added only to first join. And after that, class table inheritance joins are added. (As in this ticket summary)
I think the best fix is adding ON conditions to last join of class table inheritance, because only in last join all needed tables are joined. In condition user can access to all tables.

@doctrinebot commented on GitHub (Dec 22, 2013): Comment created by strate: I do not understand why this issue is resolved. It is still reproduces! Look to the source code: https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Query/SqlWalker.php#L989 ``` // Handle WITH clause if ($condExpr !== null) { // Phase 2 AST optimization: Skip processing of ConditionalExpression // if only one ConditionalTerm is defined $sql .= ' AND (' . $this->walkConditionalExpression($condExpr) . ')'; } // FIXME: these should either be nested or all forced to be left joins (DDC-XXX) if ($targetClass->isInheritanceTypeJoined()) { $sql .= $this->_generateClassTableInheritanceJoins($targetClass, $joinedDqlAlias); } ``` As you can see, sql conditions added only to first join. And after that, class table inheritance joins are added. (As in this ticket summary) I think the best fix is adding ON conditions to last join of class table inheritance, because only in last join all needed tables are joined. In condition user can access to all tables.

admin commented

2026-01-22 13:19:02 +01:00

@doctrinebot commented on GitHub (Dec 23, 2013):

Comment created by strate:

https://github.com/doctrine/doctrine2/pull/886

@doctrinebot commented on GitHub (Dec 23, 2013): Comment created by strate: https://github.com/doctrine/doctrine2/pull/886

admin commented

2026-01-22 13:19:02 +01:00

@doctrinebot commented on GitHub (Feb 17, 2014):

Comment created by @doctrinebot:

A related Github Pull-Request [GH-886] was closed:
https://github.com/doctrine/doctrine2/pull/886

@doctrinebot commented on GitHub (Feb 17, 2014): Comment created by @doctrinebot: A related Github Pull-Request [GH-886] was closed: https://github.com/doctrine/doctrine2/pull/886

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