EclipseLink (JPA 2.0) generates an erronous query for MySQL

[jaanus kaljuvee]

Hello,

I'm having problems with EclipseLink (JPA 2.0) and MySQL 5.1.37 (MySQL connector 5.1.10)

I have a java code in EJB3:

em.createQuery(
      "SELECT t FROM Task t WHERE t.starting BETWEEN :start AND :end")
        .setParameter("start", start, TemporalType.TIMESTAMP)
        .setParameter("end", end, TemporalType.TIMESTAMP)
      .getResultList();

which produces me a 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 'starting, type, _id_usr_to, _id_usr_from FROM task WHERE (starting BETWEEN '2009'

Apparently a following query is being sent to MySQL:

SELECT starting, type, _id_usr_to, _id_usr_from
FROM task
WHERE (starting
BETWEEN '2009-10-29 15:37:53'
AND '2009-10-29 15:37:55')

Which MySQL rejects.
I changed the query manually to include the table qualifier and tested it directly on MySQL:

SELECT starting, type, _id_usr_to, _id_usr_from
FROM task
WHERE (task.starting
BETWEEN '2009-10-29 15:37:53'
AND '2009-10-29 15:37:55')

...and MySQL likes it. Now please help me get JPA to automatically use the table qualifier in the generated MySQL query.

Any help would be greatly appreciated.

[jaanus kaljuvee]

I found out that starting is a reserved MySQL keyword, hence the MySQL syntax error.

[James Sutherland]

Database reserved words need to be quoted if used. i.e. in your @Column set the name to "\"starting\"".

In JPA 2.0 you can also set all columns to be delimited in the orm.xml, using the <delimited-identifiers> element under <persistence-unit-defaults>.

[jaanus kaljuvee]

Thanks for the suggestions.
Since I want to generate entity beans automatically in netbeans, I decided to just rename the column. Using quotes looks more like a hack to me and might cause some problems in the future.