Should find_by_<attr>(nil) make no queries if db has NOT NULL constraint?

Current behavior:

User.find_by_id(nil)
User Load (1.1ms) SELECT “users”.* FROM “users” WHERE “users”.“id” IS NULL LIMIT 1

User.find_by_id("")
User Load (0.6ms) SELECT “users”.* FROM “users” WHERE “users”.“id” = 0 LIMIT 1

``

I realize it’s hard to always be able to tell whether given parameter can exist or not, but above cases seem like pretty obvious, and also the most common cases (e.g. accepting blank param from request), or chaining queries where previous query returned nil.

Interestingly enough, User.find(nil) raises right away without queries, but not User.find_by_id(nil).

I noticed my code often ends up like:

User.find_by_id(id) if id.present?

``

This seems like unnecessary cruft that the app should in many cases know on its own, at least in following cases:

  • Looking up nil on column which has NOT NULL constraint.
  • Looking up blank string on integer db field (common when passing request param to query). This will be casted to 0, but unlike explicit find_by_id(0), which theoretically could exist in db via manual insertion, I think it’s safe to assume that anyone passing blank string to find_by_id and friends should expect it to be treated as nil and not 0.
    Could Rails check the db schema and know whether nil/blank values are possible in db? If not, it could short-circuit or use NullScope to avoid making unnecessary query.

Thanks!

Can’t this be mitigated by using the bang-variant? i.e. find_by_id! ?

The bang variant will raise on Not Found. But similar to non-bang version, it does not optimize unneeded queries:

User.find_by_id!(nil)

User Load (0.5ms) SELECT “users”.* FROM “users” WHERE “users”.“id” IS NULL LIMIT 1

ActiveRecord::RecordNotFound: ActiveRecord::RecordNotFound

User.find_by_id!(’’)

User Load (0.6ms) SELECT “users”.* FROM “users” WHERE “users”.“id” = 0 LIMIT 1
ActiveRecord::RecordNotFound: ActiveRecord::RecordNotFound

``

In both of above cases, I think DB query isn’t needed.