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

New Issue

admin · 2026-01-22T12:45:25+01:00

admin commented

2026-01-22 12:45:25 +01:00

Originally created by @doctrinebot on GitHub (Apr 12, 2010).

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

Originally created by @doctrinebot on GitHub (Apr 12, 2010). 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 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

admin added the Bug label 2026-01-22 12:45:25 +01:00

admin closed this issue

2026-01-22 12:45:25 +01:00

admin commented

2026-01-22 12:45:26 +01:00

@doctrinebot commented on GitHub (Apr 12, 2010):

Comment created by else:

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

@doctrinebot commented on GitHub (Apr 12, 2010): Comment created by else: This test case is slightly different from example i wrote in description but shows same issue

admin commented

2026-01-22 12:45:26 +01:00

@doctrinebot commented on GitHub (Apr 12, 2010):

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 commented on GitHub (Apr 12, 2010): Comment created by @guilhermeblanco: Your report exposes exactly the issue pointed on [DDC-349](http://www.doctrine-project.org/jira/browse/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 ```

admin commented

2026-01-22 12:45:26 +01:00

@doctrinebot commented on GitHub (Apr 13, 2010):

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 commented on GitHub (Apr 13, 2010): 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](http://www.doctrine-project.org/jira/browse/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),

admin commented

2026-01-22 12:45:27 +01:00

@doctrinebot commented on GitHub (Apr 15, 2010):

Comment created by romanb:

Fixed in 01c2c06bbf 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 commented on GitHub (Apr 15, 2010): 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](http://www.doctrine-project.org/jira/browse/DDC-522). Better use @JoinColumn(name="item_id", ...)

admin commented

2026-01-22 12:45:27 +01:00

@doctrinebot commented on GitHub (Apr 15, 2010):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Apr 15, 2010): Issue was closed with resolution "Fixed"

admin commented

2026-01-22 12:45:28 +01:00

@doctrinebot commented on GitHub (Dec 13, 2015):

Imported 1 attachments from Jira into https://gist.github.com/1f3ea54f624b9fac5e82

10566_DDC512Test.php

@doctrinebot commented on GitHub (Dec 13, 2015): Imported 1 attachments from Jira into https://gist.github.com/1f3ea54f624b9fac5e82 - [10566_DDC512Test.php](https://gist.github.com/1f3ea54f624b9fac5e82#file-10566_DDC512Test-php)

admin referenced this issue

2026-01-22 16:00:09 +01:00

[PR #641] [MERGED] Added yml example in ordered-associations.rst #8490

admin referenced this issue

2026-01-22 16:00:10 +01:00

[PR #641] Added yml example in ordered-associations.rst #8493

Sign in to join this conversation.