Doctrine 2 - ORM

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

Details

  • Type: Bug Bug
  • Status: Closed 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

Ascending order - Click to sort in descending order
Hide
Permalink
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
Permalink
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
Permalink
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
Permalink
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
Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: