# Missing values in LEFT OUTER JOIN using find\_by\_sql (possible bug?)

**URL:** <https://www.ruby-forum.com/t/missing-values-in-left-outer-join-using-find-by-sql-possible-bug/124646>\
**Category:** Rails\
**Created:** [December 31, 2007, 2:38pm UTC](https://www.ruby-forum.com/t/missing-values-in-left-outer-join-using-find-by-sql-possible-bug/124646 "2007-12-31T14:38:39Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [December 31, 2007, 2:38pm UTC](https://www.ruby-forum.com/t/missing-values-in-left-outer-join-using-find-by-sql-possible-bug/124646/1 "2007-12-31T14:38:39Z")

</div>

This is related to an earlier post " left outer join change in 1.2.1?"  
by Benjamin Ritcey.

I need to use a LEFT OUTER JOIN for its classic purpose:  
joining two two tables such that tuples are returned containing  
attribute values  
of the left hand table even if there is no join on the right hand  
table.

From the MySQL console, the following works as desired:

mysql\> SELECT afnu\_mca\_questions._, afnu\_mca\_responses._  
-\> FROM afnu\_mca\_questions LEFT OUTER JOIN afnu\_mca\_responses  
-\> ON afnu\_mca\_questions.id =  
afnu\_mca\_responses.afnu\_mca\_question\_id  
-\> WHERE afnu\_mca\_questions.afnu\_assessment\_id = 1 AND  
-\> (afnu\_mca\_responses.exam\_id = 1 OR afnu\_mca\_responses.id  
IS NULL);

Attribute values for afnu\_mca\_questions.\* (left hand) are returned  
even if  
afnu\_mca\_responses.\* is NULL.

Reducing the attribute values that are projected to save space, I get  
the following in MySQL:  
mysql\> SELECT afnu\_mca\_questions.id, afnu\_mca\_responses.id  
-\> FROM afnu\_mca\_questions LEFT OUTER JOIN afnu\_mca\_responses  
-\> ON afnu\_mca\_questions.id =  
afnu\_mca\_responses.afnu\_mca\_question\_id  
-\> WHERE afnu\_mca\_questions.afnu\_assessment\_id = 1 AND  
-\> (afnu\_mca\_responses.exam\_id = 1 OR afnu\_mca\_responses.id IS  
NULL);  
±—±-----+  
| id | id |  
±—±-----+  
| 1 | 1 |  
| 4 | NULL |  
±—±-----+  
2 rows in set (0.00 sec)

Given the above query converted for use in find\_by\_sql, I have:

@all\_question\_responses =  
AfnuMcaQuestion.find\_by\_sql(["SELECT afnu\_mca\_questions.id, " +  
" afnu\_mca\_responses.id " +  
"FROM afnu\_mca\_questions LEFT OUTER  
JOIN afnu\_mca\_responses " +  
"ON afnu\_mca\_questions.id =  
afnu\_mca\_responses.afnu\_mca\_question\_id " +  
"WHERE  
afnu\_mca\_questions.afnu\_assessment\_id = ? AND " +  
“(afnu\_mca\_responses.exam\_id = ? OR  
afnu\_mca\_responses.id IS NULL)”,  
@afnu\_assessment\_id, @exam\_id]  
)

The results of this query are the following:

## Object:

- !ruby/object:AfnuMcaQuestion  
attributes:  
id: “1”
- !ruby/object:AfnuMcaQuestion  
attributes:  
id:

The attribute value for the second “id” should be “4”.

* * *

Why is this happening? Is this a bug in find\_by\_sql?

Thanks,

WJM

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [January 1, 2008, 4:50am UTC](https://www.ruby-forum.com/t/missing-values-in-left-outer-join-using-find-by-sql-possible-bug/124646/2 "2008-01-01T04:50:32Z")

</div>

removed\_email\_address@domain.invalid wrote:

> afnu\_mca\_responses.id IS NULL)",
> 
> - !ruby/object:AfnuMcaQuestion  
> attributes:  
> id:
> 
> The attribute value for the second “id” should be “4”.
> 
> Why is this happening? Is this a bug in find\_by\_sql?

The responses id is overwriting the questions id. Instead use

SELECT afnu\_mca\_questions.id, afnu\_mca\_responses.id as response\_id …

–  
We develop, watch us RoR, in numbers too big to ignore.