org.hibernate.exception.SQLGrammarException: could not execute update query

Hibernate JIRA | John Trimble | 7 years ago
  1. 0

    In an INSERT, aliases defined in the top most FROM clause incorrectly have their references in subqueries replaced by the table name. For example, a query such as: HQL: insert into Foo (id, someInt) select bar.id as id, bar.someInt as someInt from Bar bar where exists ( select innerbar from Bar innerbar where innerbar.id=bar.id ) becomes, HSQLDB SQL: insert into Foo ( id, someInt ) select aliastable0_.id as col_0_0_, aliastable0_.someInt as col_1_0_ from Bar aliastable0_ where exists (select id from Bar aliastable1_ where aliastable1_.id=Bar.id) Note the use of 'Bar.id' instead of 'aliastable0_.id' resulting in the following exception: org.hibernate.exception.SQLGrammarException: could not execute update query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:87) at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:398) at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259) at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1142) at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94) ... Caused by: java.sql.SQLException: Column not found: BAR.ID in statement [insert into Foo ( id, someInt ) select aliastable0_.id as col_0_0_, aliastable0_.someInt as col_1_0_ from Bar aliastable0_ where exists (select id from Bar aliastable1_ where aliastable1_.id=Bar.id)] at org.hsqldb.jdbc.Util.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:528) at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:95) at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:88) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:65) ... 27 more This substitution is not always inappropriate, for example DELETE requires the replacement: HQL: delete from Bar bar where exists ( select innerbar from Bar innerbar where bar.id=innerbar.id ) HSQLDB SQL: delete from Bar where exists ( select id from Bar insertinto1_ where Bar.id=insertinto1_.id) However, I cannot think of a case where this behavior would be necessary for an INSERT, but perhaps there is one I haven't considered. Judging from the logs, it looks as though this may have worked at one point but broke due to a fix for http://opensource.atlassian.com/projects/hibernate/browse/HHH-1419. I have attached a patch for this issue, which includes an added test for it. The fix causes aliases in an INSERT to be handled in the same manner as for a SELECT. Tested on HSQLDB and MySQL with all tests passing. The issue exists in both the http://anonsvn.jboss.org/repos/hibernate/core/branches/Branch_3_2/ and http://anonsvn.jboss.org/repos/hibernate/core/branches/Branch_3_3/ branches (both of which I've included patches for), and may exist in the http://anonsvn.jboss.org/repos/hibernate/core/trunk/ as well, but I couldn't get it to build for me. Let me know if there are any issues with the patch--especially with regard to unintended side effects--and I'll gladly look into resolving them.

    Hibernate JIRA | 7 years ago | John Trimble
    org.hibernate.exception.SQLGrammarException: could not execute update query
  2. 0

    In an INSERT, aliases defined in the top most FROM clause incorrectly have their references in subqueries replaced by the table name. For example, a query such as: HQL: insert into Foo (id, someInt) select bar.id as id, bar.someInt as someInt from Bar bar where exists ( select innerbar from Bar innerbar where innerbar.id=bar.id ) becomes, HSQLDB SQL: insert into Foo ( id, someInt ) select aliastable0_.id as col_0_0_, aliastable0_.someInt as col_1_0_ from Bar aliastable0_ where exists (select id from Bar aliastable1_ where aliastable1_.id=Bar.id) Note the use of 'Bar.id' instead of 'aliastable0_.id' resulting in the following exception: org.hibernate.exception.SQLGrammarException: could not execute update query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:87) at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:398) at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259) at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1142) at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94) ... Caused by: java.sql.SQLException: Column not found: BAR.ID in statement [insert into Foo ( id, someInt ) select aliastable0_.id as col_0_0_, aliastable0_.someInt as col_1_0_ from Bar aliastable0_ where exists (select id from Bar aliastable1_ where aliastable1_.id=Bar.id)] at org.hsqldb.jdbc.Util.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:528) at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:95) at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:88) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:65) ... 27 more This substitution is not always inappropriate, for example DELETE requires the replacement: HQL: delete from Bar bar where exists ( select innerbar from Bar innerbar where bar.id=innerbar.id ) HSQLDB SQL: delete from Bar where exists ( select id from Bar insertinto1_ where Bar.id=insertinto1_.id) However, I cannot think of a case where this behavior would be necessary for an INSERT, but perhaps there is one I haven't considered. Judging from the logs, it looks as though this may have worked at one point but broke due to a fix for http://opensource.atlassian.com/projects/hibernate/browse/HHH-1419. I have attached a patch for this issue, which includes an added test for it. The fix causes aliases in an INSERT to be handled in the same manner as for a SELECT. Tested on HSQLDB and MySQL with all tests passing. The issue exists in both the http://anonsvn.jboss.org/repos/hibernate/core/branches/Branch_3_2/ and http://anonsvn.jboss.org/repos/hibernate/core/branches/Branch_3_3/ branches (both of which I've included patches for), and may exist in the http://anonsvn.jboss.org/repos/hibernate/core/trunk/ as well, but I couldn't get it to build for me. Let me know if there are any issues with the patch--especially with regard to unintended side effects--and I'll gladly look into resolving them.

    Hibernate JIRA | 7 years ago | John Trimble
    org.hibernate.exception.SQLGrammarException: could not execute update query
  3. 0

    Understanding Hibernate: JDBCExceptionReporter | Invalid parameter index

    Stack Overflow | 4 years ago | newSpringer
    org.hibernate.exception.SQLGrammarException: could not update: [com.company.project.PSDBusinessAssociate#18962]
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    srid(geometry) function does not exist: Java hibernate

    Stack Overflow | 3 years ago | primer_cuervo
    org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
  6. 0

    SQLGrammarException: could not get next sequence value (Object Relational Mapping forum at JavaRanch)

    coderanch.com | 1 year ago
    org.hibernate.exception.SQLGrammarException: could not get next sequence value

    1 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. org.hibernate.exception.SQLGrammarException

      could not execute update query

      at org.hibernate.exception.SQLStateConverter.convert()
    2. Hibernate
      JDBCExceptionHelper.convert
      1. org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
      2. org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
      2 frames
    3. Hibernate
      BasicExecutor.execute
      1. org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:87)
      1 frame
    4. Hibernate HQL/JP-QL Parser
      QueryTranslatorImpl.executeUpdate
      1. org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:398)
      1 frame
    5. Hibernate
      QueryImpl.executeUpdate
      1. org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
      2. org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1142)
      3. org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
      3 frames