Why is it a syntax error?

Question

Simple query like this.

MyModel.where('student_id = :id AND from <= :date AND to >= :date', {:id => student.id, :date => day.to_s(:db)})

Returns an exception

=> ActiveRecord::JDBCError: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from <= '2017-02-28 00:00:00.000' AND to >= '2017-02-28 00:00:00.000')'

And I don't get why.

@Raptor shoot. This is what I was thinking of. But my table has a field `from`. What should I do then? — Viktor, Mar 06 '17 at 08:06
Simple: Rename it. Avoid using reserved words in column name as well as table name. — Raptor, Mar 06 '17 at 08:07
@Raptor sure, but we have 500 000 people in this DB already, so I would like to move the path of least resistance :D — Viktor, Mar 06 '17 at 08:09
Oops, then adding backtick is the only solution, or you have to rename the DB column as well as all related codes at your next maintenance window. — Raptor, Mar 06 '17 at 08:11

score 4 · Accepted Answer · answered Mar 06 '17 at 08:07

4

From is a reserved word you should use backtics when you have a column name from (better if you don't use column names based on reserved words)

MyModel.where('student_id = :id 
                  AND `from` <= :date 
                  AND `to` >= :date', {:id => student.id, :date => day.to_s(:db)})

answered Mar 06 '17 at 08:07

ScaisEdge

131,976
10
91
107

1

I don't see a point in giving the answer to this question, since *identical* problems were solved here (SO) hundreds of times. Users like you (experienced + you have golded mysql badge) should rather close these questions as duplicates. – Andrey Deineko Mar 06 '17 at 08:30

Why is it a syntax error?

1 Answers1