Uploaded image for project: 'Hibernate ORM'
  1. Hibernate ORM
  2. HHH-8581

Batch update versioned with where clause generates bad SQL

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Incomplete
    • Affects Version/s: 4.2.6
    • Fix Version/s: None
    • Component/s: query-hql
    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true

      Description

      Doing a batch update such as update versioned Animal set weight = 69 where weight = 0 generates a SQLGrammarException.

      org.hibernate.exception.SQLGrammarException: could not prepare statement
      	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
      	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
      	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
      	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:188)
      	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:91)
      	at org.hibernate.hql.spi.TableBasedUpdateHandlerImpl.execute(TableBasedUpdateHandlerImpl.java:163)
      	at org.hibernate.hql.internal.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:65)
      	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:415)
      	at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:282)
      	at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1290)
      	at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:116)
      	at org.hibernate.test.batch.joinedInheritence.BatchVersionedUpdateOfJoinedHierarchyTest.testUpdateVersionedWithWhereClause(BatchVersionedUpdateOfJoinedHierarchyTest.java:42)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
      	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
      	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
      	at org.hibernate.testing.junit4.ExtendedFrameworkMethod.invokeExplosively(ExtendedFrameworkMethod.java:63)
      	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
      	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
      	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:30)
      	at org.junit.internal.runners.statements.FailOnTimeout$StatementThread.run(FailOnTimeout.java:62)
      Caused by: org.h2.jdbc.JdbcSQLException: Table "ANIMAL0_" not found; SQL statement:
      update Animal set animal0_.version=animal0_.version+1, weight=69 where (id) IN (select id from HT_Animal) [42102-145]
      	at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
      	at org.h2.message.DbException.get(DbException.java:167)
      	at org.h2.message.DbException.get(DbException.java:144)
      	at org.h2.command.Parser.readTableColumn(Parser.java:632)
      	at org.h2.command.Parser.parseUpdate(Parser.java:664)
      	at org.h2.command.Parser.parsePrepared(Parser.java:413)
      	at org.h2.command.Parser.parse(Parser.java:274)
      	at org.h2.command.Parser.parse(Parser.java:246)
      	at org.h2.command.Parser.prepare(Parser.java:200)
      	at org.h2.command.Parser.prepareCommand(Parser.java:213)
      	at org.h2.engine.Session.prepareLocal(Session.java:423)
      	at org.h2.engine.Session.prepareCommand(Session.java:373)
      	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1056)
      	at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)
      	at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:233)
      	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:98)
      	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)
      	... 20 more
      

        Attachments

          Activity

          Hide
          awallgren Anders Wallgren added a comment -

          Test case is available as a pull request at https://github.com/hibernate/hibernate-orm/pull/605

          Show
          awallgren Anders Wallgren added a comment - Test case is available as a pull request at https://github.com/hibernate/hibernate-orm/pull/605
          Hide
          awallgren Anders Wallgren added a comment -

          The entity in question should be using JOINED inheritance. SINGLE_TABLE works fine.

          Show
          awallgren Anders Wallgren added a comment - The entity in question should be using JOINED inheritance. SINGLE_TABLE works fine.
          Hide
          awallgren Anders Wallgren added a comment -

          I traced it as far as org.hibernate.hql.internal.ast.tree.FromElementType#toColumns where this code block is executed when there's a where clause, which is what adds the table alias to the version fragment:

          if ( isManipulationQuery() && isMultiTable() && inWhereClause() ) {
          	// the actual where-clause will end up being ripped out the update/delete and used in
          	// a select to populate the temp table, so its ok to use the table alias to qualify the table refs
          	// and safer to do so to protect from same-named columns
          	return propertyMapping.toColumns( tableAlias, path );
          }
          
          Show
          awallgren Anders Wallgren added a comment - I traced it as far as org.hibernate.hql.internal.ast.tree.FromElementType#toColumns where this code block is executed when there's a where clause, which is what adds the table alias to the version fragment: if ( isManipulationQuery() && isMultiTable() && inWhereClause() ) { // the actual where-clause will end up being ripped out the update/delete and used in // a select to populate the temp table, so its ok to use the table alias to qualify the table refs // and safer to do so to protect from same-named columns return propertyMapping.toColumns( tableAlias, path ); }
          Hide
          awallgren Anders Wallgren added a comment -

          Looks like org.hibernate.hql.internal.ast.HqlSqlWalker#postProcessDML might need similar logic regarding table alias use with multi-table updates, since it does this:

          // Make #@%$^#^&# sure no alias is applied to the table name
          fromElement.setText( persister.getTableName() );
          
          Show
          awallgren Anders Wallgren added a comment - Looks like org.hibernate.hql.internal.ast.HqlSqlWalker#postProcessDML might need similar logic regarding table alias use with multi-table updates, since it does this: // Make #@%$^#^&# sure no alias is applied to the table name fromElement.setText( persister.getTableName() );
          Hide
          steve Steve Ebersole added a comment -

          This bug report does not indicate that the reported issue affects version 5.x. Versions prior to 5.x are no longer maintained. It would be a great help to the Hibernate team and community for someone to verify that the reported issue still affects version 5.x. If so, please add the 5.x version that you verified with to the list of affected-versions and attach the (preferably SSCCE) test case you used to do the verification to the report; from there the issues will be looked at during our triage meetings.

          For details, see http://in.relation.to/2015/10/27/great-jira-cleanup-2015/

          Show
          steve Steve Ebersole added a comment - This bug report does not indicate that the reported issue affects version 5.x. Versions prior to 5.x are no longer maintained. It would be a great help to the Hibernate team and community for someone to verify that the reported issue still affects version 5.x. If so, please add the 5.x version that you verified with to the list of affected-versions and attach the (preferably SSCCE) test case you used to do the verification to the report; from there the issues will be looked at during our triage meetings. For details, see http://in.relation.to/2015/10/27/great-jira-cleanup-2015/
          Hide
          steve Steve Ebersole added a comment -

          As part of verifying that this issue affects 5.0, please just set the "Affects version". Leave the "verify-affects-5.0" label and leave the issue in "Awaiting Response" status; these are critical for us to be able to track these verifications and triage them. Thanks.

          Show
          steve Steve Ebersole added a comment - As part of verifying that this issue affects 5.0, please just set the "Affects version". Leave the "verify-affects-5.0" label and leave the issue in "Awaiting Response" status; these are critical for us to be able to track these verifications and triage them. Thanks.
          Hide
          awallgren Anders Wallgren added a comment -

          This bug report does not indicate that the reported issue affects version 5.x. Versions prior to 5.x are no longer maintained.

          Perhaps the lack of such an indicator is because this bug was filed over two years ago?

          We have long since patched around it and are not likely to spend any resources further investigating it.

          (As an aside, the amount of work required in migrating to 5.0 makes it unlikely that we'll do so any time soon – frankly I don't see the benefits for us at this time.)

          Show
          awallgren Anders Wallgren added a comment - This bug report does not indicate that the reported issue affects version 5.x. Versions prior to 5.x are no longer maintained. Perhaps the lack of such an indicator is because this bug was filed over two years ago? We have long since patched around it and are not likely to spend any resources further investigating it. (As an aside, the amount of work required in migrating to 5.0 makes it unlikely that we'll do so any time soon – frankly I don't see the benefits for us at this time.)
          Hide
          steve Steve Ebersole added a comment -

          No worries. If/when you get around to it feel free to re-open if its still an issue. Thanks for replying.

          Show
          steve Steve Ebersole added a comment - No worries. If/when you get around to it feel free to re-open if its still an issue. Thanks for replying.

            People

            • Votes:
              5 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: