If I’m following the code, it looks like the binds argument is passed through #select_all and eventually down to the adapter’s #exec_query, e.g., the MySQL adapter. So, the behavior could be different depending on the adapter and even DB version. That makes sense, because different databases handle prepared statements and bind variables differently.
And it looks like the documentation example of #find_by_sql, passing the array with “bind variables” into the sql arg, works because that sql arg is being passed to #sanitize_sql, which has its own implementation of replacing variables outside of/before the adapter. So, using this syntax isn’t really using bind variables, passed to the database. It’s more string interpolation and sanitation.
So, following the code, I was able to get a query executing with the Postgres adapter and actually passing the bind variables to Postgres.
Example 1, using binds arg:
Project.find_by_sql(‘select * from projects where client_id = $1 and region = $2’, [[nil, 123], [nil, ‘West’]])
(NOTE: You can use the column name in place of nil, but I get a warning when I do that.)
Results in this statements in the PG log:
LOG: execute a3: select * from projects where client_id = $1 and region = $2
DETAIL: parameters: $1 = ‘123’, $2 = ‘West’
Example 2, specifying the variables in the sql arg:
Project.find_by_sql([‘select * from projects where client_id = ? and region = ?’,123, ‘West’])
LOG: execute : select * from projects where client_id = 123 and region = ‘West’
So, you can see those are clearly different. In example 1, the statement is being prepared, and the bind variables are passed to Postgres. In example 2, the variables are being replaced by Rails via #sanitize_sql before the statement is passed on to the adapter, which results in an unnamed statement passed to Postgres with no bind variables. (I think, that’s how I’m interpreting it at least.)
I couldn’t get a true bind variable example working with MySQL, but I don’t have a convenient environment using recent versions of the adapter and MySQL server. I think this is related to Pat Shaughnessy’s post on prepared statements.
Following that logic, it’s not too surprising that standard model methods result in different behavior in PG and MySQL:
Project.find(1) using the Postgres adapter results in:
LOG: execute a4: SELECT “projects”.* FROM “projects” WHERE “projects”.“id” = $1 LIMIT 1
DETAIL: parameters: $1 = ‘1’
Project.find(1) using (my older version of) the MySQL adapter results in the MySQL equivalent of:
LOG: execute : SELECT “projects”.* FROM “projects” WHERE “projects”.“id” = 1 LIMIT 1
I haven’t really kept up with database internals enough to know how much of an improvement prepared statements yield. Years ago, prepared statements could give you a significant performance improvement, because the query parser can easily see two queries are the same, reuse copies of the execution plan, etc. My understanding is databases have gotten better about deriving similarities in queries and reusing plans, cached results, etc.
I don’t have an opinion (yet) on what should be done to improve the situation, but I hope that sheds a little light on what’s happening with the binds arg and why you might want to use it.