Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2452

Additional `WITH` condition in joins between JTI roots cause invalid SQL to be produced

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: Git Master
    • Fix Version/s: None
    • Component/s: DQL, ORM
    • Security Level: All
    • Labels:
    • Environment:
      irrelevant

      Description

      Given a simple Joined Table Inheritance like following:

      /**
       * @Entity @Table(name="foo") @InheritanceType("JOINED")
       * @DiscriminatorColumn(name="discr", type="string")
       * @DiscriminatorMap({"foo" = "DDC2452Foo", "bar" = "DDC2452Bar"})
       */
      class DDC2452Foo
      {
          /** @Id @Column(type="integer") @GeneratedValue */
          public $id;
      }
      
      /** @Entity @Table(name="bar") */
      class DDC2452Bar extends DDC2452Foo
      {
      }

      Following DQL

      SELECT foo1 FROM DDC2452Foo foo1 JOIN DDC2452Foo foo2 WITH 1=1

      Will produce broken SQL:

      SELECT
          f0_.id AS id0, f0_.discr AS discr1 
      FROM 
          foo f0_ 
      LEFT JOIN bar b1_ 
          ON f0_.id = b1_.id 
      LEFT JOIN foo f2_ 
      LEFT JOIN bar b3_ 
          ON f2_.id = b3_.id 
          ON (1 = 1)

      (please note the duplicate `ON` in the SQL)

      That is caused because of the SQL walker producing the JTI filter with already the `ON` clause in it.

      That happens because the JTI join conditions are added in https://github.com/doctrine/doctrine2/blob/2.4.0-BETA2/lib/Doctrine/ORM/Query/SqlWalker.php#L823-L825 (`walkRangeVariableDeclaration`), while the additional defined `WITH` conditions are considered in `walkJoinAssociationDeclaration` later on.

      Added a test case and fix at https://github.com/doctrine/doctrine2/pull/668

        Activity

        Marco Pivetta created issue -
        Marco Pivetta made changes -
        Field Original Value New Value
        Description Given a simple Joined Table Inheritance like following:

        {code}
        /**
         * @Entity @Table(name="foo") @InheritanceType("JOINED")
         * @DiscriminatorColumn(name="discr", type="string")
         * @DiscriminatorMap({"foo" = "DDCXXXXFoo", "bar" = "DDCXXXXBar"})
         */
        class DDCXXXXFoo
        {
            /** @Id @Column(type="integer") @GeneratedValue */
            public $id;
        }

        /** @Entity @Table(name="bar") */
        class DDCXXXXBar extends DDCXXXXFoo
        {
        }{code}

        Following DQL

        {code}SELECT foo1 FROM DDCXXXXFoo foo1 JOIN DDCXXXXFoo foo2 WITH 1=1{code}

        Will produce broken SQL:

        {code}SELECT
            f0_.id AS id0, f0_.discr AS discr1
        FROM
            foo f0_
        LEFT JOIN bar b1_
            ON f0_.id = b1_.id
        LEFT JOIN foo f2_
        LEFT JOIN bar b3_
            ON f2_.id = b3_.id
            ON (1 = 1){code}

        (please note the duplicate `ON` in the SQL)

        That is caused because of the SQL walker producing the JTI filter with already the `ON` clause in it.

        That happens because the JTI join conditions are added in https://github.com/doctrine/doctrine2/blob/2.4.0-BETA2/lib/Doctrine/ORM/Query/SqlWalker.php#L823-L825 (`walkRangeVariableDeclaration`), while the additional defined `WITH` conditions are considered in `walkJoinAssociationDeclaration` later on.
        Given a simple Joined Table Inheritance like following:

        {code}
        /**
         * @Entity @Table(name="foo") @InheritanceType("JOINED")
         * @DiscriminatorColumn(name="discr", type="string")
         * @DiscriminatorMap({"foo" = "DDC2452Foo", "bar" = "DDC2452Bar"})
         */
        class DDC2452Foo
        {
            /** @Id @Column(type="integer") @GeneratedValue */
            public $id;
        }

        /** @Entity @Table(name="bar") */
        class DDC2452Bar extends DDC2452Foo
        {
        }{code}

        Following DQL

        {code}SELECT foo1 FROM DDC2452Foo foo1 JOIN DDC2452Foo foo2 WITH 1=1{code}

        Will produce broken SQL:

        {code}SELECT
            f0_.id AS id0, f0_.discr AS discr1
        FROM
            foo f0_
        LEFT JOIN bar b1_
            ON f0_.id = b1_.id
        LEFT JOIN foo f2_
        LEFT JOIN bar b3_
            ON f2_.id = b3_.id
            ON (1 = 1){code}

        (please note the duplicate `ON` in the SQL)

        That is caused because of the SQL walker producing the JTI filter with already the `ON` clause in it.

        That happens because the JTI join conditions are added in https://github.com/doctrine/doctrine2/blob/2.4.0-BETA2/lib/Doctrine/ORM/Query/SqlWalker.php#L823-L825 (`walkRangeVariableDeclaration`), while the additional defined `WITH` conditions are considered in `walkJoinAssociationDeclaration` later on.

        Added a test case at https://github.com/doctrine/doctrine2/pull/668
        Marco Pivetta made changes -
        Description Given a simple Joined Table Inheritance like following:

        {code}
        /**
         * @Entity @Table(name="foo") @InheritanceType("JOINED")
         * @DiscriminatorColumn(name="discr", type="string")
         * @DiscriminatorMap({"foo" = "DDC2452Foo", "bar" = "DDC2452Bar"})
         */
        class DDC2452Foo
        {
            /** @Id @Column(type="integer") @GeneratedValue */
            public $id;
        }

        /** @Entity @Table(name="bar") */
        class DDC2452Bar extends DDC2452Foo
        {
        }{code}

        Following DQL

        {code}SELECT foo1 FROM DDC2452Foo foo1 JOIN DDC2452Foo foo2 WITH 1=1{code}

        Will produce broken SQL:

        {code}SELECT
            f0_.id AS id0, f0_.discr AS discr1
        FROM
            foo f0_
        LEFT JOIN bar b1_
            ON f0_.id = b1_.id
        LEFT JOIN foo f2_
        LEFT JOIN bar b3_
            ON f2_.id = b3_.id
            ON (1 = 1){code}

        (please note the duplicate `ON` in the SQL)

        That is caused because of the SQL walker producing the JTI filter with already the `ON` clause in it.

        That happens because the JTI join conditions are added in https://github.com/doctrine/doctrine2/blob/2.4.0-BETA2/lib/Doctrine/ORM/Query/SqlWalker.php#L823-L825 (`walkRangeVariableDeclaration`), while the additional defined `WITH` conditions are considered in `walkJoinAssociationDeclaration` later on.

        Added a test case at https://github.com/doctrine/doctrine2/pull/668
        Given a simple Joined Table Inheritance like following:

        {code}
        /**
         * @Entity @Table(name="foo") @InheritanceType("JOINED")
         * @DiscriminatorColumn(name="discr", type="string")
         * @DiscriminatorMap({"foo" = "DDC2452Foo", "bar" = "DDC2452Bar"})
         */
        class DDC2452Foo
        {
            /** @Id @Column(type="integer") @GeneratedValue */
            public $id;
        }

        /** @Entity @Table(name="bar") */
        class DDC2452Bar extends DDC2452Foo
        {
        }{code}

        Following DQL

        {code}SELECT foo1 FROM DDC2452Foo foo1 JOIN DDC2452Foo foo2 WITH 1=1{code}

        Will produce broken SQL:

        {code}SELECT
            f0_.id AS id0, f0_.discr AS discr1
        FROM
            foo f0_
        LEFT JOIN bar b1_
            ON f0_.id = b1_.id
        LEFT JOIN foo f2_
        LEFT JOIN bar b3_
            ON f2_.id = b3_.id
            ON (1 = 1){code}

        (please note the duplicate `ON` in the SQL)

        That is caused because of the SQL walker producing the JTI filter with already the `ON` clause in it.

        That happens because the JTI join conditions are added in https://github.com/doctrine/doctrine2/blob/2.4.0-BETA2/lib/Doctrine/ORM/Query/SqlWalker.php#L823-L825 (`walkRangeVariableDeclaration`), while the additional defined `WITH` conditions are considered in `walkJoinAssociationDeclaration` later on.

        Added a test case and fix at https://github.com/doctrine/doctrine2/pull/668
        Benjamin Eberlei made changes -
        Fix Version/s 2.5 [ 10522 ]
        Fix Version/s 2.4 [ 10321 ]
        Marco Pivetta made changes -
        Fix Version/s 2.5 [ 10522 ]

          People

          • Assignee:
            Marco Pivetta
            Reporter:
            Marco Pivetta
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: