Code   o'clock

1 = 0

Recently, our CI system produced this cryptic message:

WHERE 1=0

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.

QueryMagic 1

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.

QueryMagic 2

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

Share post