[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2221) MySQL temp table DDL and isolation

[Milosz Tylenda (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2221?page=comments#action_25570 ] 

Milosz Tylenda commented on HHH-2221:
-------------------------------------

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."


> MySQL temp table DDL and isolation
> ----------------------------------
>
>          Key: HHH-2221
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2221
>      Project: Hibernate3
>         Type: Bug

>   Components: core
>     Versions: 3.2.0.ga
>  Environment: INFO: Hibernate 3.2.0
> INFO: RDBMS: MySQL, version: 5.0.22
> INFO: JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.0.4 ( $Date: 2006-10-19 17:47:48 +0200 (Thu, 19 Oct 2006) $, $Revision: 5908 $ )
>     Reporter: Donnchadh O Donnabhain
>     Assignee: Steve Ebersole
>      Fix For: 3.2.2
>  Attachments: HHH-2221.patch, JoinedSubclassTest.java
>
>
> On MySQL, temporary table DDL operations do not cause implicit transaction commits, even though the driver metadata reports that DDL does cause implicit transaction commits.  The reason being that MySQL handles temp table DDL operations differently than non-temp DDL, which the JDBC metadata does not account for.
> Thus, need a way for the dialect to unequivocally force the temp table DDL to be performed in the current transaction regardless of what the driver reports...

-- 
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.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira