Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1256

Generated SQL error with DQL WITH and JOINED inheritance

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Duplicate
    • Affects Version/s: 2.0.3, 2.3
    • Fix Version/s: None
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None
    • Environment:
      Ubuntu 11.04

      Description

      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;
      }
      

        Issue Links

          Activity

          Justin Hendrickson created issue -
          Hide
          Michael Ridgway added a comment -

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

          I'll see if I can figure this out.

          Show
          Michael Ridgway added a comment - I created a test for this: https://github.com/mridgway/doctrine2/commit/1bb26a46188f180270d723e395ee707443ebdda1 I'll see if I can figure this out.
          Hide
          Michael Ridgway added a comment -

          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.

          Show
          Michael Ridgway added a comment - 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.
          Hide
          Benjamin Eberlei added a comment -

          Formatting

          Show
          Benjamin Eberlei added a comment - Formatting
          Benjamin Eberlei made changes -
          Field Original Value New Value
          Description 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;
          }
          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:

          {code}
          SELECT p FROM Fampus_Entity_Photo p LEFT JOIN p.flag f WITH f.status='ok'
          {code}

          Here's the generated SQL:

          {code}
          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
          {code}

          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):

          {code}
          /**
           * @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;
          }
          {code}
          Hide
          Benjamin Eberlei added a comment -

          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.

          Show
          Benjamin Eberlei added a comment - 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.
          Benjamin Eberlei made changes -
          Priority Major [ 3 ] Minor [ 4 ]
          Hide
          Guilherme Blanco added a comment -

          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,

          Show
          Guilherme Blanco added a comment - 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,
          Benjamin Eberlei made changes -
          Workflow jira [ 12797 ] jira-feedback [ 13946 ]
          Benjamin Eberlei made changes -
          Workflow jira-feedback [ 13946 ] jira-feedback2 [ 15810 ]
          Benjamin Eberlei made changes -
          Workflow jira-feedback2 [ 15810 ] jira-feedback3 [ 18066 ]
          Benjamin Eberlei made changes -
          Link This issue is duplicated by DDC-2131 [ DDC-2131 ]
          Igor Bekrenyov made changes -
          Status Open [ 1 ] Awaiting Feedback [ 10000 ]
          Igor Bekrenyov made changes -
          Affects Version/s 2.3 [ 10185 ]
          Benjamin Eberlei made changes -
          Status Awaiting Feedback [ 10000 ] Open [ 1 ]
          Hide
          Benjamin Eberlei added a comment -

          Duplicate of DDC-349

          Show
          Benjamin Eberlei added a comment - Duplicate of DDC-349
          Benjamin Eberlei made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Duplicate [ 3 ]
          Benjamin Eberlei made changes -
          Link This issue duplicates DDC-349 [ DDC-349 ]
          Hide
          Artur Eshenbrener added a comment -

          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.

          Show
          Artur Eshenbrener added a comment - 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.
          Show
          Artur Eshenbrener added a comment - https://github.com/doctrine/doctrine2/pull/886
          Hide
          Doctrine Bot added a comment -

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

          Show
          Doctrine Bot added a comment - A related Github Pull-Request [GH-886] was closed: https://github.com/doctrine/doctrine2/pull/886

          This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

          • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-1256, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

            People

            • Assignee:
              Benjamin Eberlei
              Reporter:
              Justin Hendrickson
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: