Why is "s.d IS NULL" being added to my where clause using DQL in Doctrine 1.1.2 (USING leftJoin)?

Question

I am using a MySQL database and Doctrine 1.1.2.

I am trying to write a query with a leftJoin and I am trying to understand the result as it has given me problems multiple times. After I have run a few different tests, it seems almost intermittent, which I don't believe it can be.

Below, I will paste in both the DQL to create the queries, and the output from them. These four are examples I created while trying to understand why sometiems the "s.id IS NULL" is added, and why sometimes the "s.id IN ('184158')". I would be fine if neither were ever added, but can someone explain to me why it is there, and maybe how I can tell DQL that I don't want it?

From some of the research I have done, I gather that it may be part of a bug fix (#1868) regarding if this query contains a subquery. But, as this has no subquery, I don't know how to push forward on testing that issue for sure.

Here is the code from bug fix #1868 in doctrine/lib/Doctrine/Query.php

// FIX #1868: If not ID under MySQL is found to be restricted, restrict pk column for null  
//            (which will lead to a return of 0 items)  
$limitSubquerySql = $this->_conn->quoteIdentifier($field)  
    . (( ! empty($subquery)) ? ' IN (' . $subquery . ')' : ' IS NULL')  
    . ((count($this->_sqlParts['where']) > 0) ? ' AND ' : '');

Please and thank you.

$query = Doctrine_Query::create()  
    ->select('s.id, p.id')  
    ->from('Submission s')  
    ->leftjoin('s.Products p ON s.product_id = p.id')  
    ->where('s.id = ?',$id)  
    ->limit(1);  
echo $query->getSqlQuery() . '< br />';  

$query = Doctrine_Query::create()  
    ->select('s.id, p.id, p.field1')  
    ->from('Submission s')  
    ->leftjoin('s.Products p ON s.product_id = p.id')  
    ->where('s.id = ?',$id)  
    ->limit(1);  
echo $query->getSqlQuery() . '< br />';  

$query = Doctrine_Query::create()  
    ->select('s.id, p.id, p.field1, p.field2')  
    ->from('Submission s')  
    ->leftjoin('s.Products p ON s.product_id = p.id')  
    ->where('s.id = ?',$id)  
    ->limit(1);  
echo $query->getSqlQuery() . '< br />';  

$query = Doctrine_Query::create()  
    ->select('s.id, p.id, p.field1, p.field2, p.field3')  
    ->from('Submission s')  
    ->leftjoin('s.Products p ON s.product_id = p.id')  
    ->where('s.id = ?',$id)  
    ->limit(1);  
echo $query->getSqlQuery() . '< br />';    

**-- OUTPUT --**  
**-- $id = 184158**  

SELECT s.id AS s__id, p.id AS p__id  
FROM submission s   
LEFT JOIN products p   
ON (s.prodid = p.id)   
WHERE **s.id IS NULL** AND (s.id = 184158) **<-- Notice the s.id IS NULL**  

SELECT s.id AS s__id, p.id AS p__id, p.field1 AS p__field1   
FROM submission s   
LEFT JOIN products p   
ON (s.prodid = p.id)   
WHERE **s.id IN ('184158')** AND (s.id = 184158) **<-- Notice the s.id IN ('184158')**  

SELECT s.id AS s__id, p.id AS p__id, p.field1 AS p__field1, p.field2 AS p__field2   
FROM submission s   
LEFT JOIN products p   
ON (s.prodid = p.id)   
WHERE **s.id IS NULL** AND (s.id = 184158) **<-- Notice the s.id IS NULL**  

SELECT s.id AS s__id, p.id AS p__id, p.field1 AS p__field1, p.field2 AS p__field2, p.field3 AS p__field3   
FROM submission s   
LEFT JOIN products p   
ON (s.prodid = p.id)   
WHERE **s.id IN ('184158')** AND (s.id = 184158) **<-- Notice the s.id IN ('184158')**

Not sure if this could be the cause of anything but maybe try dropping the explicit column on which your left-joining: ->leftJoin('s. Products p') ... Doctrine does over-thinking sometimes. There's always Doctrine RawSQL to bypass these strange cases as well. — Tom, Jun 09 '12 at 11:04
A note, I have noticed that when I remove the limit statement, it no longer adds the s.id IN () not the s.id IS NULL. I will research about this topic. Thank you Tom, that did not change the outcome. — Marco Cabrera, Jun 11 '12 at 14:49
I'm also seeing this, will do a bit of digging in Doctrine source later. Frustrating. — Martin Lyne, Nov 29 '12 at 14:35

score 2 · Answer 1 · answered Nov 29 '12 at 15:25

This occurs because of several reasons:

1) Doctrine deals with Objects not "rows", so when you add a Join it will immediately need more than a simple limit Say, you want a single (fetchOne) object of a table with a one-to-many relationship, these relations add to the row count in SQL so Doctrine does it in 2 stages.

table1.id table2.id
1,11
1,12
2,11
2,13
2,22
3,33

Assuming you asked for fetchOne then it will get 2 rows, but 1 object will be hydrated (for the "id=1" rows)

2) If your query finds nothing then the DQL will know nothing will match so it adds id = NULL, or if it finds something it adds IN (id-that-is-first)

I had the same issue, leading me here, and it turned out the query was just not returning anything. Hope this helps.

Why is "s.d IS NULL" being added to my where clause using DQL in Doctrine 1.1.2 (USING leftJoin)?

1 Answers1