[DDC-1256] Generated SQL error with DQL WITH and JOINED inheritance Created: 06/Jul/11  Updated: 18/Aug/14  Resolved: 01/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.0.3, 2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Justin Hendrickson Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 1
Labels: None

Ubuntu 11.04

Issue Links:
duplicates DDC-349 Add support for specifying precedence... Open
is duplicated by DDC-2131 Fetch join not working on class table... Resolved
is referenced by DDC-2869 [GH-886] [DDC-1256] Fix applying ON/W... Resolved


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;

Comment by Michael Ridgway [ 06/Jul/11 ]

I created a test for this: https://github.com/mridgway/doctrine2/commit/1bb26a46188f180270d723e395ee707443ebdda1

I'll see if I can figure this out.

Comment by Michael Ridgway [ 11/Jul/11 ]

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.

Comment by Benjamin Eberlei [ 12/Jul/11 ]


Comment by Benjamin Eberlei [ 12/Jul/11 ]

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.

Comment by Guilherme Blanco [ 14/Aug/11 ]

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.


Comment by Benjamin Eberlei [ 01/May/13 ]

Duplicate of DDC-349

Comment by Artur Eshenbrener [ 22/Dec/13 ]

I do not understand why this issue is resolved. It is still reproduces!
Look to the source code:

// 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.

Comment by Artur Eshenbrener [ 23/Dec/13 ]


Comment by Doctrine Bot [ 17/Feb/14 ]

A related Github Pull-Request [GH-886] was closed:

Generated at Fri Oct 09 06:39:59 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.