You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Sandeep Shrivastava (JIRA)" <ji...@apache.org> on 2008/05/09 05:04:56 UTC
[jira] Created: (OPENJPA-597) JPQL delete query with an alias for
tablename and without any further clauses gives an error on MySQL
JPQL delete query with an alias for tablename and without any further clauses gives an error on MySQL
-----------------------------------------------------------------------------------------------------
Key: OPENJPA-597
URL: https://issues.apache.org/jira/browse/OPENJPA-597
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
os.name: Windows XP
os.version: 5.1
os.arch: x86
java.version: 1.6.0_05
java.vendor: BEA Systems, Inc.
Reporter: Sandeep Shrivastava
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(Exceptions.java:242)
[testlogic] at org.apache.openjpa.persistence.PersistenceException.writeObject(PersistenceException.java:100)
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.
[jira] Commented: (OPENJPA-597) JPQL delete query with an alias for
tablename and without any further clauses gives an error on MySQL
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-597?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12595624#action_12595624 ]
Michael Dick commented on OPENJPA-597:
--------------------------------------
Will the resulting SQL work for all versions of MySQL (3.0, 4.0, 4.1 and 5.0)? From the description it looks like we should generate different SQL for 4.0 and 4.1, but I don't see any code to do that in the patch.
If the resulting SQL will work with all supported versions of MySQL then I'm fine with that - if it will only work with 5.0 and 4.1 (for example) then I think we should be smart enough to detect which version we're dealing with and initialize MySQLDictionary appropriately.
> JPQL delete query with an alias for tablename and without any further clauses gives an error on MySQL
> -----------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-597
> URL: https://issues.apache.org/jira/browse/OPENJPA-597
> 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
> os.name: 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(Exceptions.java:242)
> [testlogic] at org.apache.openjpa.persistence.PersistenceException.writeObject(PersistenceException.java:100)
> 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.
[jira] Resolved: (OPENJPA-597) JPQL delete query with an alias for
tablename and without any further clauses gives an error on MySQL
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-597?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Linskey resolved OPENJPA-597.
-------------------------------------
Resolution: Fixed
Fix Version/s: 1.1.0
> JPQL delete query with an alias for tablename and without any further clauses gives an error on MySQL
> -----------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-597
> URL: https://issues.apache.org/jira/browse/OPENJPA-597
> 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
> os.name: Windows XP
> os.version: 5.1
> os.arch: x86
> java.version: 1.6.0_05
> java.vendor: BEA Systems, Inc.
> Reporter: Sandeep Shrivastava
> Fix For: 1.1.0
>
> 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(Exceptions.java:242)
> [testlogic] at org.apache.openjpa.persistence.PersistenceException.writeObject(PersistenceException.java:100)
> 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.
[jira] Commented: (OPENJPA-597) JPQL delete query with an alias for
tablename and without any further clauses gives an error on MySQL
Posted by "Sandeep Shrivastava (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-597?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12596135#action_12596135 ]
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
For earlier versions: DELETE DYN_DESC_PERSON FROM DYN_DESC_PERSON
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: https://issues.apache.org/jira/browse/OPENJPA-597
> 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
> os.name: 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(Exceptions.java:242)
> [testlogic] at org.apache.openjpa.persistence.PersistenceException.writeObject(PersistenceException.java:100)
> 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.
[jira] Updated: (OPENJPA-597) JPQL delete query with an alias for
tablename and without any further clauses gives an error on MySQL
Posted by "Sandeep Shrivastava (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-597?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sandeep Shrivastava updated OPENJPA-597:
----------------------------------------
Attachment: OPENJPA-597.patch
Patch to optionally include either the table names or the aliass as the delete targets in the DELETE sql that is generated based on the requiresTargetForDelete that defaults to false and set to true in the mysql dictionary.
> JPQL delete query with an alias for tablename and without any further clauses gives an error on MySQL
> -----------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-597
> URL: https://issues.apache.org/jira/browse/OPENJPA-597
> 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
> os.name: 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(Exceptions.java:242)
> [testlogic] at org.apache.openjpa.persistence.PersistenceException.writeObject(PersistenceException.java:100)
> 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.