Donnchadh O Donnabhain added a comment - 14/Nov/06 09:03 AM

Donnchadh O Donnabhain added a comment - 14/Nov/06 09:03 AM This issue came up on the hibernate forum too: http://forum.hibernate.org/viewtopic.php?t=965949

Milosz Tylenda added a comment - 18/Nov/06 01:20 PM

Milosz Tylenda added a comment - 18/Nov/06 01:20 PM This also shows as failed tests in BulkManipulationTest when run on MySQL. In AbstractStatementExecutor, can't we rely solely on dialect settings and ignore driver's isDataDefinitionImplicitCommit() (like in the patch)? I tested this change on HSQL, MySQL 5.0.24a, PostgreSQL 8.1 and DB2 9.1 and temporary tables were created correctly.

Donnchadh O Donnabhain added a comment - 20/Nov/06 08:53 AM

Donnchadh O Donnabhain added a comment - 20/Nov/06 08:53 AM I'm guessing that this may not work in oracle, at least with the current version of Oracle9Dialect and OracleDialect

Steve Ebersole added a comment - 05/Dec/06 02:12 PM

Steve Ebersole added a comment - 05/Dec/06 02:12 PM So I have what I believe to be appropriate changes to have this work on MySQL and still work on others. However, I do still get 2 failures that I cannot explain. For example:     delete from`User` where id not in ( select id from`User` user1_ ) causes: Caused by: java.sql.SQLException: You can't specify target table 'User' for update in FROM clause at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) I think it is trying to complain that you cannot do a delete from a table and restrict the deletions based on a select from that same table? Anyone know if this is valid on MySQL? Or is this something else?

Steve Ebersole added a comment - 05/Dec/06 03:19 PM

Steve Ebersole added a comment - 05/Dec/06 03:19 PM ORIGINAL DESCRIPTION: I've created the following test (in JoinedSubclassTest) which reporduced the problem:     public void testBulkUpdateJoinedSubclass() {         Session s = openSession();         Transaction t = s.beginTransaction();                  Employee mark = new Employee();         mark.setName("Mark");         mark.setTitle("internal sales");         mark.setSex('M');         mark.setAddress("buckhead");         mark.setZip("30305");         mark.setCountry("USA");                  s.save(mark);                  String hql = "update Employee employee set employee.sex=:newSex where employee.id in (:ids)";         Query query = s.createQuery(hql);         query.setCharacter("newSex", 'F');         List ids = new ArrayList();         ids.add(Long.valueOf(mark.getId()));         query.setParameterList("ids", ids);         query.executeUpdate();         s.delete(mark);         t.commit();         s.close();     }      and run it with the following properties: -Dhibernate.dialect=org.hibernate.dialect.MySQLDialect -Dhibernate.connection.driver_class=com.mysql.jdbc.Driver -Dhibernate.connection.url=jdbc:mysql://127.0.0.1/hibernateTest -Dhibernate.connection.username=username -Dhibernate.connection.password=password This produces the following error: org.hibernate.exception.SQLGrammarException: could not insert/select ids for bulk update at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:127) at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396) at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259) at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141) at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94) at org.hibernate.test.joinedsubclass.JoinedSubclassTest.testBulkUpdateJoinedSubclass(JoinedSubclassTest.java:131) 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:589) at junit.framework.TestCase.runTest(TestCase.java:164) at org.hibernate.test.TestCase.runTest(TestCase.java:247) at junit.framework.TestCase.runBare(TestCase.java:130) at org.hibernate.test.TestCase.runBare(TestCase.java:313) at junit.framework.TestResult$1.protect(TestResult.java:110) at junit.framework.TestResult.runProtected(TestResult.java:128) at junit.framework.TestResult.run(TestResult.java:113) at junit.framework.TestCase.run(TestCase.java:120) at junit.framework.TestSuite.runTest(TestSuite.java:228) at junit.framework.TestSuite.run(TestSuite.java:223) at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:35) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196) Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'hibernateTest.HT_JEmployee' doesn't exist at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) at com.mysql.jdbc.Connection.execSQL(Connection.java:3176) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303) at org.hibernate.hql.ast.exec.MultiTableUpdateExecutor.execute(MultiTableUpdateExecutor.java:118) ... 26 more This works fine in hibernate 3.1.3. The problem seems to be in AbstractStatementExecutor. It seems to assume that if a DDL statement, in general, results in a commit that this will also happen for a temporary table. In the case of MySQL, at least, this is not the case. The code in AbstractStatementExecutor goes and creates the temporary table in a new connection, which, in the case of MySQL, will not be visible in the current transaction. http://fisheye.jboss.com/browse/Hibernate/branches/Branch_3_2/Hibernate3/src/org/hibernate/hql/ast/exec/AbstractStatementExecutor.java?r1=9262&r2=9989

Milosz Tylenda added a comment - 06/Dec/06 02:13 AM

Milosz Tylenda added a comment - 06/Dec/06 02:13 AM Steve, these 2 failures are MySQL "features". Section "DELETE syntax" in their manual says: "Currently, you cannot delete from a table and select from the same table in a subquery." The similar restriction applies to UPDATE with subquery syntax: "Incorrectly used table in subquery: Error 1093 (ER_UPDATE_TABLE_USED) SQLSTATE = HY000 Message = "You can't specify target table 'x' for update in FROM clause" This error occurs in cases such as the following: UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1); You can use a subquery for assignment within an UPDATE statement because subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target."

Steve Ebersole added a comment - 06/Dec/06 07:06 AM

Steve Ebersole added a comment - 06/Dec/06 07:06 AM trunk / 3.2

Steve Ebersole added a comment - 06/Dec/06 07:06 AM

Steve Ebersole added a comment - 06/Dec/06 07:06 AM My local MySQL5 install shows these changes as fixing the problem. Please try from SVN and verify