Backticks wrapping SQL values in polymorphic associations

Rails
1

Hopefully the subject line didn’t scare you. That said…

I have a simple polymorphic relationship (is that possible?):

class Part

has_many :attachments, :as => :attachable

end

class Attachment

belongs_to :attachable, :polymorphic => true

end

Calling Part.find(:first).attachments yields this SQL error:

ActiveRecord::StatementInvalid:
Mysql::Error: Unknown column ‘Part’ in ‘where clause’:
SELECT * FROM attachments WHERE
(attachments.attachable_id = 1375 AND
attachments.attachable_type = Part)

If I change the backticks to single quotes it runs fine in MySQL.

But… why are there backticks? Is that per design? Is there something
wrong with my MySQL install? (MySQL version 5.0.37, InnoDB format)

Any ideas?

2

On 10 Oct 2007, at 00:31, Daniel W. wrote:

wrong with my MySQL install? (MySQL version 5.0.37, InnoDB format)

It’s not normal - backticks are for quoting column or table names and
so on, whereas this is just a string. I’ve seen another email or
thing on irc about this. It would be great to get to the bottom of it.

Fred

3

Frederick C. wrote:

It’s not normal - backticks are for quoting column or table names and
so on, whereas this is just a string. I’ve seen another email or
thing on irc about this. It would be great to get to the bottom of it.

Aye! I see this method that places backticks around column names…

def quote_column_name(name) #:nodoc:
“#{name}”
end

Inside…
active_record-1.15.2/lib/active_record/connection_adpaters/mysql_adapter.rb

I also have 1.15.3 and the method is the same. So that’s where it
happens, but what’s more important is who is asking it to happen. And
that, I dunno… it’d be easier to check with TextMate, but alas, I am
at work and have been cursed with Vista…

Anywho, a hack (a really ugly one) I thought of, but have not tried,
would be to…

def quote_column_name(name) #:nodoc:
name.constantize
“’#{name}’”
rescue
“#{name}”
end

It’s flawed in that if you have a column name that matches any class in
Ruby it will constantize properly and then, well, your table name won’t
be backticked. (It’s probably flawed in other ways I don’t see.)

I suppose though… that if you follow Rails convention you won’t have
uppercased column names (as this seems to be happening only with
polymorphic associations, and thus the value of name would follow Ruby
constant semantics, e.g. Part, Attachment, etc.). Ironic that I, for the
first time in two years, am working with an application that does not
fully respect those conventions. Damn me.

4

On Oct 10, 2007, at 10:16 , Daniel W. wrote:

Anywho, a hack (a really ugly one) I thought of, but have not tried,
would be to…

def quote_column_name(name) #:nodoc:
name.constantize
“’#{name}’”
rescue
“#{name}”
end

Wait: is what you’re saying here is that MySQL supports (in various
versions) either backticks or single-quotes for identifiers? I
thought the SQL standard was to use double-quotes for identifiers,
single-quotes for string literals.

Michael G.
grzm seespotcode net

5

On Oct 10, 2007, at 12:42 , Daniel W. wrote:

I assume you’re talking about MySQL identifiers, and if you are, I
have
to stare at you blankly for I’m not sure what an identifier in MySQL
would be.

I do know that MySQL “prefers” that table and column names be
backticked.

Identifiers are things such as table and column names, rather than
literal strings or language keywords like CREATE or TABLE. My
understanding is the MySQL uses backticks to quote identifiers. (Not
having used MySQL, I can only speak from what I’ve seen. As I
mentioned before, the SQL standard specifies that double-quotes
should be used for identifiers.)

Actually, I think you’ve misidentified where the problem occurs. If
there is a problem in the ActiveRecord code, I expect the error is
somewhere in the code for polymorphic association, possibly calling
quote_column_name when it shouldn’t. The quote_column_name method in
and of itself appears to be doing exactly what it should.

The first thing I’d check I think is to see if there’s any MySQL-
specific polymorphic attribute code. From there I’d look in
ActiveRecord itself.

Have you tried this in edge?

Michael G.
grzm seespotcode net

6

Michael G. wrote:

def quote_column_name(name) #:nodoc:
name.constantize
“’#{name}’”
rescue
“#{name}”
end

Wait: is what you’re saying here is that MySQL supports (in various
versions) either backticks or single-quotes for identifiers? I
thought the SQL standard was to use double-quotes for identifiers,
single-quotes for string literals.

Uh, I didn’t mean to say that, if that’s what I said.

I assume you’re talking about MySQL identifiers, and if you are, I have
to stare at you blankly for I’m not sure what an identifier in MySQL
would be.

I do know that MySQL “prefers” that table and column names be
backticked.

I’m not talking about MySQL though – I’m saying that Active Record is
incorrectly using backticks where quotes should be used. I’m saying that
Active Record thinks my Part model-as-string (thus ‘Part’) in a
polymorphic association is a column name and is backticking it instead
of quoting it.

I dunno what the SQL standard is. I hate the stuff and try to stay away
from it as much as possible.