rails/rails — Issue #4977: StatementInvalid error Rails 3.2.1, MySQL2 and MySQL ANSI_QUOTES
Author: cbailster
Created: 2012-02-09 19:36:34+00:00
State: open

I've just tried upgrading from 3.1.1 to 3.2.1 and have discovered that any db query that requires use of an id column are now failing when I have mysql configured to use [ANSI_QUOTES](http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ansi_quotes) (i.e. "id" is the name of a column 'id' is a 2 character string - normally under mysql both are the same)

e.g. Using console

irb(main):001:0> d = Doctor.find(27531)

Generates the following SQL:

Doctor Load (0.7ms) SELECT `entities`.* FROM `entities` WHERE `entities`.`type` IN ('Doctor') AND `entities`.`"id"` = 27531 LIMIT 1

Which causes the following error:

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'entities."id"' in 'where clause': SELECT `entities`.* FROM `entities` WHERE `entities`.`type` IN ('Doctor') AND `entities`.`"id"` = 27531 LIMIT 1

Stack trace is as follows:

from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:233:in `query'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:233:in `block in execute'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
from /usr/lib/ruby/gems/1.9.1/gems/activesupport-3.2.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:233:in `execute'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:214:in `execute'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:218:in `exec_query'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:228:in `select'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract/database_statements.rb:16:in `select_all'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/querying.rb:38:in `block in find_by_sql'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/explain.rb:40:in `logging_query_plan'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/querying.rb:37:in `find_by_sql'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation.rb:170:in `exec_queries'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation.rb:159:in `block in to_a'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/explain.rb:40:in `logging_query_plan'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation.rb:158:in `to_a'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation/finder_methods.rb:377:in `find_first'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation/finder_methods.rb:122:in `first'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation/finder_methods.rb:335:in `find_one'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation/finder_methods.rb:311:in `find_with_ids'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/relation/finder_methods.rb:107:in `find'
from /usr/lib/ruby/gems/1.9.1/gems/activerecord-3.2.1/lib/active_record/querying.rb:5:in `find'
from (irb):1
from /usr/lib/ruby/gems/1.9.1/gems/railties-3.2.1/lib/rails/commands/console.rb:47:in `start'
from /usr/lib/ruby/gems/1.9.1/gems/railties-3.2.1/lib/rails/commands/console.rb:8:in `start'
from /usr/lib/ruby/gems/1.9.1/gems/railties-3.2.1/lib/rails/commands.rb:41:in `'
from script/rails:6:in `require'
from script/rails:6:in `

Turn off ANSI_QUOTES and the error goes away. Queries that don't use an ID column are unaffected, as are tables where the primary key is overridden using `set_primary_key`