1 = 0
By Chris Weis
Recently, our CI system produced this cryptic message:
Couldn't find Deployment with 'id'=1 [WHERE 1=0]
What was that supposed to mean? Especially frustrating as the system Worked On My Machine™.
I was puzzled. What could ever prompt ActiveRecord to add
WHERE 1=0 to a query? I discussed the incident with my colleagues, and we theorized that something may be using that
WHERE clause as a resource-efficient way to find whether a table exists. That wasn’t it at all and it wouldn’t have made sense at this particular place in the code anyway, but it put me on the right track: What does ActiveRecord do with my query anyway?
You might already know these examples of how
where() conditions get translated into SQL.
The examples above are quite common to most Ruby on Rails users, but even then, they already hide a lot of magic. Depending on what you pass as a hash value to
where, it picks ` =
, IN ()
or IS` as an operation.
There is one more rare edge case: passing an array to
where that turns out to be empty.
Looking at the examples above, we might expect it to be turned into
WHERE field IN (). This would be a reasonable assumption. However, empty sets are not legal SQL syntax, at least in MySQL. So ActiveRecord handles this case differently: it replaces
IN () with
WHERE 1=0, which has the intended result, but it did confuse us.
Magic is great, but with great magic comes great chance of confusion. Know your magic :)
Originally published at https://www.codeoclock.com/2016/01/05/WHERE-1=0/ on January 5, 2016