After upgrading from Rails 5.1 to Rails 5.2, only for Postgres (using the pg gem, but not with MySql) the implementation of queries like Foo.where(id: [1, 2, 3]) changed from SELECT "foos".* FROM "foos" WHERE "foos"."id" IN (1, 2, 3) to SELECT "foos".* FROM "foos" WHERE "foos"."id" IN ($1, $2, $3) [["id", 1], ["id", 2], ["id", 3]].
This is not an unreasonable change, except that PG has a limit of ~65K parameters in a parameterized query whereas the only limit to the number of values in an IN clause is the total query string size, This will cause breaking behavior in a production system if we go through with the upgrade to 5.2.
Does anyone know if it's possible to revert to the old behavior without downgrading Rails? Barring that any pointers to any documentation on this change?
Thanks.
Ouch, that's an unfortunate edge case.
It's not specifically documented, because it's just another step on a general process of bind-ification of all query parameters.
By best recommendation would be Don't Do That: if those IDs are coming from the database, use a subquery instead of bouncing them through the application layer. (I've seen a lot of apps that force ID lists when the subquery equivalent is both less code and more DB-efficient.) If they come from elsewhere and you have no other choice, I'd try to arrange the query in a way that allowed chunking them into sets of 50k or something.
There's no way of requesting the old behaviour... if you want that, you'll probably have to write your own query fragment builder:
# Obviously be very careful when doing this; we're deliberately avoiding
# the feature that normally protects against SQL injection
foos.where("id IN (#{[1, 2, 3].map(&:to_i).join(",")})")
You don't have to go quite that raw -- there's the slightly safer
foos.where(Foo.sanitize_sql(["id IN (?)", [1, 2, 3]]))
... but while that will work now, it's entirely possible that method might start using binds in a future (6.0 or later) release.