Rails generates query with reserved word for field name


#1

For some reason, rails is generating this SQL:

SELECT FIRST 1 * FROM questions WHERE (parent_id IS NULL) ORDER BY
position DESC

  1. There is no column named position referenced in my code.

  2. POSITION is a reserved word in SQL92. It is the name of a string
    function that returns the offset of one string within another. I
    believe that this is a bug in Rails. Either position should be quoted
    or the property it is trying to use should be renamed since no SQL92
    compliant database would honor this query (DB2, Oracle, postgres,
    firebird, etc).

I have scanned all files in my project for the word “position”, and the
only places it appears are in reference to HTML element positions.

Here is the Questions code:

class Question < ActiveRecord::Base
require ‘presentations’
require ‘question’

include UUIDHelper

has_many :answers, :order => :seq
belongs_to :presentation, :class_name => “Presentations”, :foreign_key
=> :presentation_id
belongs_to :parent, :class_name => “Quiz”
acts_as_list :scope => :parent_id

TODO: auto-increment for seq

end

create table questions
(
ID char(36) not null primary key,
PARENT_ID char(36) not null,
presentation_id char(36) not null,
seq integer not null,

foreign key (PARENT_ID) references quizzes(ID),
foreign key (presentation_id) references presentations(ID)
);
create unique ascending index quest_seq on questions (PARENT_ID,SEQ);


#2

David J. wrote:

For some reason, rails is generating this SQL:

SELECT FIRST 1 * FROM questions WHERE (parent_id IS NULL) ORDER BY
position DESC

  1. There is no column named position referenced in my code.
    position is used for acts_as_list for ordering list members. You’re
    right, though, it should be quoted. Don’t know if that helps at all…