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 <https://github.com/rails/rails/blob/83821e2c4a32b17b153fcbcb247dec6e32f47284/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb#L253>. 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 <https://github.com/rails/rails/blob/1d43458c148f9532a81b92ee3a247da4f1c0b7ad/activerecord/lib/active_record/sanitization.rb#L91>, 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']) Results in: LOG: execute <unnamed>: 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 <http://patshaughnessy.net/2011/10/22/show-some-love-for-prepared-statements-in-rails-3-1> . 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 <unnamed>: 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. Cheers, Al On Fri, Jun 26, 2015 at 3:01 AM, Matias Korhonen <korhonen.m...@gmail.com> wrote: > There's also no test case (as far as I can find > <https://github.com/rails/rails/blob/3e36db4406beea32772b1db1e9a16cc1e8aea14c/activerecord/test/cases/finder_test.rb#L248-L265>) > that would cover passing the binds args to #find_by_sql > > Anyone know what it's supposed to be used for? > > > > On Thursday, 25 June 2015 18:48:10 UTC+3, T.J. Schuck wrote: >> >> The `bind` argument was added as `bind_values` all the way back in 2010, >> with no change to the documentation: >> https://github.com/rails/rails/commit/cc468d3ec81d6f1298fca91c0549584b36dafcc6 >> >> So the docs are probably just wrong — I’m sure a documentation PR would >> be quickly merged. >> >> -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Core" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to rubyonrails-core+unsubscr...@googlegroups.com. > To post to this group, send email to rubyonrails-core@googlegroups.com. > Visit this group at http://groups.google.com/group/rubyonrails-core. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-core+unsubscr...@googlegroups.com. To post to this group, send email to rubyonrails-core@googlegroups.com. Visit this group at http://groups.google.com/group/rubyonrails-core. For more options, visit https://groups.google.com/d/optout.
