Skip to content

Loading…

DDC-512: LEFT JOIN of extended null entity cause empty result [testcase included] #5020

Closed
doctrinebot opened this Issue · 6 comments

1 participant

@doctrinebot

Jira issue originally created by user else:

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 ShopData_Entity_StockItem extends Shop_Data_EntityItem {
/****
* @OneToOne(targetEntity="ShopData_EntityOrderItem",
mappedBy="stockItem")
*/
protected $orderItem;

...

}

So there's my query:

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

// print 0 even there're ShopData_EntityStockItem 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 deliveryInvoiceid8 FROM
ShopData_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

@doctrinebot

Comment created by else:

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

@doctrinebot

Comment created by @guilhermeblanco:

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
@doctrinebot

Comment created by romanb:

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

@doctrinebot

Comment created by romanb:

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", ...)

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added this to the 2.0-BETA1 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.