[hibernate-issues] [Hibernate-JIRA] Resolved: (ANN-515) Fields are not correctly quoted in @OneToMany relationships when specified

Fields are not correctly quoted in @OneToMany relationships when specified -------------------------------------------------------------------------- Key: ANN-515 URL: http://opensource.atlassian.com/projects/hibernate/browse/ANN-515 Project: Hibernate Annotations Type: Bug

Versions: 3.2.1 Environment: Any platform, any database using Hibernate 3.2.1, HB Annotations 3.2.1, and EntityManager 3.2.1. Tested in the context of JPA as persistence engine in Glassfish V1 UR1 (b14). Should be trivial to re-create in JBoss AS using attached project. Was tested with MSSQL 2000, PostgreSQL 8.1, and Progress 10.1A. Attached project configured for MSSQL 2000. Reporter: Vincent Jenks Assignee: Emmanuel Bernard Priority: Critical Fix For: 3.2.2 Attachments: HibernateQuoteBug.zip When tables have hyphenated names, the database requires standard SQL syntax using double-quotes (or some other form of escape) to execute queries w/o exceptions. Hibernate Annotations 3.2.1 cannot handle this scenario properly when used in JPA for @OneToMany relationships. The obverse side of the join does not get quoted, nor do the foreign keys within the select clause. This causes a org.hibernate.exception.SQLGrammarException to be thrown. A simple example, taken from the attached Netbeans 5.5/Glassfish project is as follows. The entities: [code] @Entity @Table(name="`car-lot`") public class CarLot implements Serializable { @Id @GeneratedValue(strategy=GenerationType.AUTO) @Column(name="`lot-id`", nullable=false) private Integer lotId; @Column(name="`lot-name`", nullable=false) private String lotName; @Column(name="`lot-location`", nullable=false) private String lotLocation; @OneToMany(mappedBy="carLot", fetch=FetchType.LAZY, cascade=CascadeType.ALL) private List<Car> cars; ............................ } @Entity @Table(name="`cars-for-sale`") public class Car implements Serializable { @Id @GeneratedValue(strategy=GenerationType.AUTO) @Column(name="`car-id`", nullable=false) private Integer carId; @Column(name="`car-make`", nullable=false) private String carMake; @Column(name="`car-model`", nullable=false) private String carModel; @Column(name="`year-manufactured`", nullable=false) @Temporal(TemporalType.TIMESTAMP) private Date yearManufactured; @ManyToOne @JoinColumn(name="`lot-id`") private CarLot carLot; ............................ } [/code] Queried like so: [code] @Stateless public class CarBean implements CarLocal { @PersistenceContext private EntityManager em; public List<CarLot> getAllLotsAndCars() { return this.em.createQuery("select cl from CarLot cl left join fetch cl.cars").getResultList(); } } [/code] Results in the following SQL being generated: [code] Hibernate: /* select cl from CarLot cl left join fetch cl.cars */ select carlot0_.[lot-id] as lot1_16_0_, cars1_.[car-id] as car1_15_1_, carlot0_.[lot-name] as lot2_16_0_, carlot0_.[lot-location] as lot3_16_0_, cars1_.[car-make] as car2_15_1_, cars1_.[car-model] as car3_15_1_, cars1_.[year-manufactured] as year4_15_1_, cars1_.[lot-id] as lot5_15_1_, cars1_.lot-id as lot5_0__, --lot-id FK is not [bracketed]!! cars1_.[car-id] as car1_0__ from [car-lot] carlot0_ left outer join [cars-for-sale] cars1_ on carlot0_.[lot-id]=cars1_.lot-id --lot-id FK is not [bracketed]!! [/code] The entire stack trace in this test: [code] EJB5018: An exception was thrown during an ejb invocation on [CarBean] javax.ejb.EJBException at com.sun.ejb.containers.BaseContainer.processSystemException(BaseContainer.java:3730) at com.sun.ejb.containers.BaseContainer.completeNewTx(BaseContainer.java:3630) at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:3431) at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1247) at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:192) at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:118) at $Proxy54.getAllLotsAndCars(Unknown Source) at hqb.servlet.CarServlet.doGet(CarServlet.java:32) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:397) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:278) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:536) at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:240) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:179) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566) at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:73) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:182) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566) at com.sun.enterprise.web.VirtualServerPipeline.invoke(VirtualServerPipeline.java:120) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:939) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:137) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:536) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:939) at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:239) at com.sun.enterprise.web.connector.grizzly.ProcessorTask.invokeAdapter(ProcessorTask.java:667) at com.sun.enterprise.web.connector.grizzly.ProcessorTask.processNonBlocked(ProcessorTask.java:574) at com.sun.enterprise.web.connector.grizzly.ProcessorTask.process(ProcessorTask.java:844) at com.sun.enterprise.web.connector.grizzly.ReadTask.executeProcessorTask(ReadTask.java:287) at com.sun.enterprise.web.connector.grizzly.ReadTask.doTask(ReadTask.java:212) at com.sun.enterprise.web.connector.grizzly.TaskBase.run(TaskBase.java:252) at com.sun.enterprise.web.connector.grizzly.WorkerThread.run(WorkerThread.java:75) Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73) at hqb.session.CarBean.getAllLotsAndCars(CarBean.java:56) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1050) at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:165) at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2766) at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3847) at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:184) ... 29 more Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2214) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095) at org.hibernate.loader.Loader.list(Loader.java:2090) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:388) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64) ... 39 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'lot'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) at org.hibernate.loader.Loader.getResultSet(Loader.java:1778) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2211) ... 47 more StandardWrapperValve[CarServlet]: Servlet.service() for servlet CarServlet threw exception javax.ejb.EJBException at com.sun.ejb.containers.BaseContainer.processSystemException(BaseContainer.java:3730) at com.sun.ejb.containers.BaseContainer.completeNewTx(BaseContainer.java:3630) at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:3431) at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1247) at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:192) at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:118) at $Proxy54.getAllLotsAndCars(Unknown Source) at hqb.servlet.CarServlet.doGet(CarServlet.java:32) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:397) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:278) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:536) at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:240) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:179) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566) at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:73) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:182) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566) at com.sun.enterprise.web.VirtualServerPipeline.invoke(VirtualServerPipeline.java:120) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:939) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:137) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:566) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:536) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:939) at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:239) at com.sun.enterprise.web.connector.grizzly.ProcessorTask.invokeAdapter(ProcessorTask.java:667) at com.sun.enterprise.web.connector.grizzly.ProcessorTask.processNonBlocked(ProcessorTask.java:574) at com.sun.enterprise.web.connector.grizzly.ProcessorTask.process(ProcessorTask.java:844) at com.sun.enterprise.web.connector.grizzly.ReadTask.executeProcessorTask(ReadTask.java:287) at com.sun.enterprise.web.connector.grizzly.ReadTask.doTask(ReadTask.java:212) at com.sun.enterprise.web.connector.grizzly.TaskBase.run(TaskBase.java:252) at com.sun.enterprise.web.connector.grizzly.WorkerThread.run(WorkerThread.java:75) Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73) at hqb.session.CarBean.getAllLotsAndCars(CarBean.java:56) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1050) at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:165) at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2766) at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3847) at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:184) ... 29 more Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2214) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095) at org.hibernate.loader.Loader.list(Loader.java:2090) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:388) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64) ... 39 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'lot'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getPrepExecResponse(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) at org.hibernate.loader.Loader.getResultSet(Loader.java:1778) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2211) ... 47 more [/code] Some other things I have tested & tried but did not work as a work-around: 1. Replacing the tick (`) characters with escaped quotes (\") in table & field names in the entities. 2. EAGER loading the child collection of Car entities and removing the "left join fetch" clause from the JPQL. ...when eagerly loaded, the additional auto-generated queries are broken, e.g. [code] Hibernate: /* load one-to-many hqb.model.CarLot.cars */ select cars0_.lot-id as lot5_1_, --oops! cars0_.[car-id] as car1_1_, cars0_.[car-id] as car1_23_0_, cars0_.[car-make] as car2_23_0_, cars0_.[car-model] as car3_23_0_, cars0_.[year-manufactured] as year4_23_0_, cars0_.[lot-id] as lot5_23_0_ from [cars-for-sale] cars0_ where cars0_.lot-id=? --oops! [/code] The attached zip file is a very simple Netbeans 5.5 Enterprise project targeted for Glassfish...however it would be easy enough to throw the sources into an eclipse webtools project (or whatever) and test it against JBoss or anything else.