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

[ http://jira.jboss.com/jira/browse/EJBTHREE-976?page=all ] Ariane van der Steldt updated EJBTHREE-976: ------------------------------------------- Description: 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`). Log fragment where it fails to create constraints: 2007-05-31 15:48:46,031 DEBUG [org.hibernate.tool.hbm2ddl.SchemaUpdate] alter table UserRight_`group` add constraint FK2116E4D388434F01 foreign key (rights_id) references UserRight 2007-05-31 15:48:46,032 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] Unsuccessful: alter table UserRight_`group` add constraint FK2116E4D388434F01 foreign key (rights_id) references UserRight 2007-05-31 15:48:46,032 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] ERROR: syntax error at or near "`" 2007-05-31 15:48:46,032 DEBUG [org.hibernate.tool.hbm2ddl.SchemaUpdate] alter table UserRight_`group` add constraint FK2116E4D39449EA67 foreign key (userGroups_id) references "group" 2007-05-31 15:48:46,033 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] Unsuccessful: alter table UserRight_`group` add constraint FK2116E4D39449EA67 foreign key (userGroups_id) references "group" 2007-05-31 15:48:46,033 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] ERROR: syntax error at or near "`" 2007-05-31 15:48:46,057 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] schema update complete was: 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`). Added log fragment. -- 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