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

Saturday, 23 December 2006

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
    Priority: Critical
 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.

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

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

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