openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sandeep Shrivastava (JIRA)" <>
Subject [jira] Commented: (OPENJPA-597) JPQL delete query with an alias for tablename and without any further clauses gives an error on MySQL
Date Mon, 12 May 2008 17:12:56 GMT


Sandeep Shrivastava commented on OPENJPA-597:

Yes it will work with all versions of mySQL.  The following snippet in the getDeleteTargets()
method generates:

For 4.1 and higher: DELETE t0 FROM DYN_DESC_PERSON t0 

if (allowsAliasInBulkClause) {
+            deleteTargets.append(tableAlias.substring(spaceIndex + 1));
+          } else {
+            deleteTargets.append(tableAlias.substring(0, spaceIndex));
+          }

The MySQLDictionary sets the allowsAliasInBulkClause propety to false for in the connectedConfiguration
method for mysql version earlier than 4.1, the default value for this property is true which
is valid for version 4.1 and higher.

> JPQL delete query with an alias for tablename and without any further clauses gives an
error on MySQL
> -----------------------------------------------------------------------------------------------------
>                 Key: OPENJPA-597
>                 URL:
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc
>    Affects Versions: 1.1.0
>         Environment: OpenJPA 1.1.0-SNAPSHOT
> version id: openjpa-1.1.0-SNAPSHOT-r422266:653008
> Apache svn revision: 422266:653008
> Windows XP
> os.version: 5.1
> os.arch: x86
> java.version: 1.6.0_05
> java.vendor: BEA Systems, Inc.
>            Reporter: Sandeep Shrivastava
>         Attachments: OPENJPA-597.patch
>   Original Estimate: 24h
>  Remaining Estimate: 24h
> If we have a JPQL query like this:
> int countDeleted = em.createQuery('Delete from Person o').executeUpdate();
> We get an exception on MySQL because the converted SQL with an alias gives a syntax error
> [testlogic] java.lang.Exception: org.apache.openjpa.lib.jdbc.ReportingSQLException: 
> You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version 
> for the right syntax to use near '' at line 1 {prepstmnt 2137108 DELETE FROM DYN_DESC_PERSON
t0 [reused=0]} [code=1064, state=42000]
> [testlogic] 	at org.apache.openjpa.util.Exceptions.replaceNestedThrowables(
> [testlogic] 	at org.apache.openjpa.persistence.PersistenceException.writeObject(
> For mySQL version 5.0 the MySQLDBDictionary defaults for supportsSubselect and allowsAliasInBulkClause
are both true. For these params the DBDictionary.toBulkOperation() generates a sql like DELETE
FROM DYN_DESC_PERSON t0 which does not work in mySQL. 
> The syntax for DELETE statements that use table aliases changed between MySQL 4.0 and
4.1. In MySQL 4.0, you should use the true table name to refer to any table from which rows
should be deleted: 
> DELETE test FROM test AS t1, test2 WHERE ...
> In MySQL 4.1, if you declare an alias for a table, you must use the alias when referring
to the table: 
> DELETE t1 FROM test AS t1, test2 WHERE ...

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message