Using 'end' as column name in Ruby on Rails (MySQL)

I had an model with an "end" column (datetime format), only to discover that Heroku crashes and burns with illogical Active Record errors whenever I attempted to reference the column in a query. I spent two hours trying to debug the extremely simple query, after which point I renamed the column to "end_at"开发者_运维技巧 and all of my problems disappeared.

Has anybody else experienced this issue? I'm curious of the reasoning behind this and hope that we can help others avoid the same mistake. A similar question has been asked before, but a clear answer was not presented.

BEGIN and END are reserved words in Oracle and SQL Server, but not sure why MySQL doesn't consider them as such.

However that PGError would appear to indicate that the database engine itself (and not any Ruby-related runtime) has indeed rejected the query because of the "end".

Reserved words (and names containing spaces) can be used if quoted - perhaps Active Record didn't quote the identifiers in the SQL which was generated.

I would look at the log in MySQL (http://dev.mysql.com/doc/refman/5.5/en/query-log.html) and see the statements generated.

And since the PGError means PostGreSQL and you mentioned Heroku (PostgreSQL 8.3) - I think this is because END is indeed a reserved word in PostgreSQL: http://www.postgresql.org/docs/8.3/static/sql-keywords-appendix.html

http://www.petefreitag.com/tools/sql_reserved_words_checker/?word=end

PostgresQL (which Heroku uses) reserves END as a keyword, so it is giving you a syntax error because your syntax is incorrect.

There are two options for fixing it:

If Heroku is breaking because ActiveRecord is not quoting column names, you can rewrite every query that uses that model to explicitly quote the "end" column so PostgresQL doesn't blow up.

Whether or not ActiveRecord quotes that column name by default, renaming the column something more descriptive (end_time, end_date, etc) and also not a reserved word in both the language you are writing the app in as well as a reserved word in the SQL engine you are using.