Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-512

LEFT JOIN of extended null entity cause empty result [testcase included]

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.0-BETA1
    • Fix Version/s: 2.0-BETA1
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      Dear developers,

      I'm not sure about propriety of my query but what i want to do is left
      join entity which is associeted by @OneToOne. Problem occur when
      associeted entity is NULL. Then i got empty result. I think it's
      because my associeted entity is extended so it cause in final SQL
      query inner joins which are not in subselect.

      class Shop_Data_Entity_StockItem extends Shop_Data_Entity_Item

      { /** * @OneToOne(targetEntity="Shop_Data_Entity_OrderItem", mappedBy="stockItem") */ protected $orderItem; ... }

      So there's my query:

      $q = $em->createQuery("select u from Shop_Data_Entity_StockItem u left
      join u.orderItem uu");
      echo $q->getSql();
      $result = $q->getResult();
      count($result[0]);

      // print 0 even there're Shop_Data_Entity_StockItem in database and
      without left join clause prints 2

      There's echo $q->getSql():

      SELECT s0_.ean AS ean0, s0_.title AS title1, s0_.description AS
      description2, s0_.vat AS vat3, s0_.id AS id4, s1_.bestBefore AS
      bestBefore5, s0_.discr AS discr6, s0_.price AS price7,
      s1_.deliveryInvoice_id AS deliveryInvoice_id8 FROM
      Shop_Data_Entity_StockItem s1_ INNER JOIN Shop_Data_Entity_Item s0_ ON
      s1_.id = s0_.id LEFT JOIN Shop_Data_Entity_OrderItem s2_ ON s1_.id =
      s2_.stockItem_id INNER JOIN Shop_Data_Entity_OfferItem s3_ ON s2_.id =
      s3_.id INNER JOIN Shop_Data_Entity_Item s4_ ON s2_.id = s4_.id

        Activity

        Hide
        Ondrej Sibrina added a comment -

        This test case is slightly different from example i wrote in description but shows same issue

        Show
        Ondrej Sibrina added a comment - This test case is slightly different from example i wrote in description but shows same issue
        Hide
        Guilherme Blanco added a comment -

        Your report exposes exactly the issue pointed on DDC-349.

        We should take a look how to fix this without having to update ALL unit tests that takes advantage of inheritance.

        Also, the SQL spec requires that all joins need to be specified before write the ON keyword.
        Example:

        Exception: [PDOException] SQLSTATE[HY000]: General error: 1 a JOIN clause is required before ON
        SELECT d0_.id AS id0, d0_.item AS item1 FROM DDC512Customer d0_ LEFT JOIN (DDC512OfferItem d1_ ON d0_.item = d1_.id INNER JOIN DDC512Item d2_ ON d1_.id = d2_.id)
        

        And in the situation of a inheritance:

        Exception: [PDOException] SQLSTATE[HY000]: General error: 1 a JOIN clause is required before ON             
        SELECT o0_.id AS id0, o0_.name AS name1, o3_.id AS id2, o3_.name AS name3, o0_.discr AS discr4, o0_.mother_id AS mother_id5, o3_.discr AS discr6, o3_.mother_id AS mother_id7 FROM OJTIC_Pet o0_ LEFT JOIN OJTIC_Cat o1_ ON o0_.id = o1_.id LEFT JOIN OJTIC_Dog o2_ ON o0_.id = o2_.id INNER JOIN (OJTIC_Pet o3_ ON o0_.id = o3_.mother_id LEFT JOIN OJTIC_Cat o4_ ON o3_.id = o4_.id LEFT JOIN OJTIC_Dog o5_ ON o3_.id = o5_.id) WHERE o0_.name = 'Poofy' ORDER BY o3_.name ASC
        
        Show
        Guilherme Blanco added a comment - Your report exposes exactly the issue pointed on DDC-349 . We should take a look how to fix this without having to update ALL unit tests that takes advantage of inheritance. Also, the SQL spec requires that all joins need to be specified before write the ON keyword. Example: Exception: [PDOException] SQLSTATE[HY000]: General error: 1 a JOIN clause is required before ON SELECT d0_.id AS id0, d0_.item AS item1 FROM DDC512Customer d0_ LEFT JOIN (DDC512OfferItem d1_ ON d0_.item = d1_.id INNER JOIN DDC512Item d2_ ON d1_.id = d2_.id) And in the situation of a inheritance: Exception: [PDOException] SQLSTATE[HY000]: General error: 1 a JOIN clause is required before ON SELECT o0_.id AS id0, o0_.name AS name1, o3_.id AS id2, o3_.name AS name3, o0_.discr AS discr4, o0_.mother_id AS mother_id5, o3_.discr AS discr6, o3_.mother_id AS mother_id7 FROM OJTIC_Pet o0_ LEFT JOIN OJTIC_Cat o1_ ON o0_.id = o1_.id LEFT JOIN OJTIC_Dog o2_ ON o0_.id = o2_.id INNER JOIN (OJTIC_Pet o3_ ON o0_.id = o3_.mother_id LEFT JOIN OJTIC_Cat o4_ ON o3_.id = o4_.id LEFT JOIN OJTIC_Dog o5_ ON o3_.id = o5_.id) WHERE o0_.name = 'Poofy' ORDER BY o3_.name ASC
        Hide
        Roman S. Borschel added a comment -

        I am aware of the problem and yes, nested joins for CTI can be a solution but its just 1 solution. The other one is to simply turn these CTI joins into left joins when they appear in the middle of a query (that is, not in the FROM clause).

        So, given a Class hierarchy like this:

        class Item
        class StockItem extends Item
        class OfferItem extends Item
        class OrderItem extends OfferItem
        
        StockItem <-onetoone-> OrderItem
        

        and a DQL like this:

        DQL: select s from StockItem s left join s.orderItem o ...
        

        We have 2 possible solutions.

        Nr. 1: Nested inner join

        SELECT ... FROM stockitem s1_
        INNER JOIN item s0_ ON s1_.id = s0_.id
        LEFT JOIN
            (orderitem s2_ INNER JOIN offeritem s3_ ON s2_.id = s3_.id
             INNER JOIN item s4_ ON s2_.id = s4_.id)
        ON s1_.id = s2_.stockItem_id
        

        Nr. 2: Just use left joins for parent tables for all CTI joins that are the result of a DQL join (This is what Hibernate does):

        SELECT ... FROM stockitem s1_
        INNER JOIN item s0_ ON s1_.id = s0_.id
        LEFT JOIN orderitem s2_ ON s1_.id = s2_.stockItem_id
        LEFT JOIN offeritem s3_ ON s2_.id = s3_.id
        LEFT JOIN item s4_ ON s2_.id = s4_.id
        

        According to DDC-349, most databases seem to support nested inner joins (Nr. 1) but nevertheless its not in the ANSI standard I think, so I am not sure we can rely on it.

        The Hibernate solution seems simpler but I still wonder whether they perform differently (Usually, inner joins are more performant than outer joins),

        Show
        Roman S. Borschel added a comment - I am aware of the problem and yes, nested joins for CTI can be a solution but its just 1 solution. The other one is to simply turn these CTI joins into left joins when they appear in the middle of a query (that is, not in the FROM clause). So, given a Class hierarchy like this: class Item class StockItem extends Item class OfferItem extends Item class OrderItem extends OfferItem StockItem <-onetoone-> OrderItem and a DQL like this: DQL: select s from StockItem s left join s.orderItem o ... We have 2 possible solutions. Nr. 1: Nested inner join SELECT ... FROM stockitem s1_ INNER JOIN item s0_ ON s1_.id = s0_.id LEFT JOIN (orderitem s2_ INNER JOIN offeritem s3_ ON s2_.id = s3_.id INNER JOIN item s4_ ON s2_.id = s4_.id) ON s1_.id = s2_.stockItem_id Nr. 2: Just use left joins for parent tables for all CTI joins that are the result of a DQL join (This is what Hibernate does): SELECT ... FROM stockitem s1_ INNER JOIN item s0_ ON s1_.id = s0_.id LEFT JOIN orderitem s2_ ON s1_.id = s2_.stockItem_id LEFT JOIN offeritem s3_ ON s2_.id = s3_.id LEFT JOIN item s4_ ON s2_.id = s4_.id According to DDC-349 , most databases seem to support nested inner joins (Nr. 1) but nevertheless its not in the ANSI standard I think, so I am not sure we can rely on it. The Hibernate solution seems simpler but I still wonder whether they perform differently (Usually, inner joins are more performant than outer joins),
        Hide
        Roman S. Borschel added a comment -

        Fixed in http://github.com/doctrine/doctrine2/commit/01c2c06bbf529d89c9741ea97702359509ea230a using the "hibernate-way".

        Please note that you currently should not name join columns the same as entity fields. See DDC-522. Better use @JoinColumn(name="item_id", ...)

        Show
        Roman S. Borschel added a comment - Fixed in http://github.com/doctrine/doctrine2/commit/01c2c06bbf529d89c9741ea97702359509ea230a using the "hibernate-way". Please note that you currently should not name join columns the same as entity fields. See DDC-522 . Better use @JoinColumn(name="item_id", ...)

          People

          • Assignee:
            Roman S. Borschel
            Reporter:
            Ondrej Sibrina
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: