The following subtlety(?!) of the under the hood SQL magic of
ActiveRecord has, on many occasions, made me waste hours staring
blankly at SQL exceptions while wrongly blaming the ruby SQL bindings.
Ahem.
Anyway, let us assume that I have a Post model with some attributes
whose names are reserved keywords of my preferred database flavour (I
know, but just bare with me... It is technically allowed with proper
quoting. Only DB tables must not be named after keywords I believe).
All the following "find with conditions" will fail very nicely, thank
you. But for the non-hardcore-DBA type person the reason might be
difficult to spot, as there are tons of keywords that will cause it.
Shall I write tests? A patch? Tighten up the Regexp?
I don't think you should go with a regexp. AR finder supports an alternate syntax for what you did above:
:conditions => { :distinct => "boom", :create => "boom" }
When you pass it a hash, it escapes column names.
Yeah, I use the above syntax for simple queries. Unfortunately it is not as useful as the SQL fragment as it only performs a Boolean AND with the arguments.
Guessing what could be a column name inside a query fragment string is, IMO, not something AR should do.
Fair enough. While I was writing my patch I couldn't think of an SQL fragment complicated enough to break the RegExp. Then again, my applications so far have only required simple SQL.
Still, I think that the documentation could a bit more explicit.
Something like, "Be aware that AR, will pass the SQL fragment as is to underlying database, after variable substitution. As such, you should take care to quote column names in the fragment so that they don't clash with database keywords, producing a syntax error".
Or, at the very least, the examples could use a properly quoted SQL fragment, and maybe explain why it is thus.
