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?

ActiveRecord::QueryMagic

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.

Conclusion

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