[jboss-jira] [JBoss JIRA] Created: (EJBTHREE-976) EJB3 does not properly escape SQL keywords in postgres, fails to create tables

[Ariane van der Steldt (JIRA)]

EJB3 does not properly escape SQL keywords in postgres, fails to create tables
------------------------------------------------------------------------------

                 Key: EJBTHREE-976
                 URL: http://jira.jboss.com/jira/browse/EJBTHREE-976
             Project: EJB 3.0
          Issue Type: Bug
          Components: EJB3 Extensions
    Affects Versions: AS 4.2.0 GA
            Reporter: Ariane van der Steldt


EJB3 does not properly escape SQL keywords in postgres, for example a table called 'group' (which is a keyword in group by). This problem is known at JBoss and a work around, using backticks, exists: http://jira.jboss.org/jira/browse/EJBTHREE-637?page=all

However this work around is unsatisfactory and fails in numerous ways:
- it is in-elegant
- it is in violation with the EJB spec (JSR 220) which imples that column and table names are names, rather than SQL escaped names
- it triggers bugs in JBoss, where column names consisting of concatenations (because of a ManyTo... relation); example: an entity E refering to an entity A with primary key column 'group' will generate queries like: select ... from E inner join A on (E.A_`group` = A."group")
  This also happens with automatic columns in ManyToMany join tables.
- using backticks is not portable: many, if not all, other application servers will assume they have to escape the column and table names, causing @Column(name="`backtick`") to have a completely different meaning on different application servers.
- triggering this bug in while performing table creation, may result in columns, constraints or even tables not being created, due to transactions failing because of invalid sql (could not create column A_`group`).

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira