aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorLukas Jungmann2014-04-30 17:59:58 (EDT)
committerTomas Kraus2014-05-05 09:34:48 (EDT)
commited6587974b6874e765ed74127f319036e762ad04 (patch)
tree804950837a6a76983f4d5a484fab599aca4cfb98
parent1673abb10758122f3c61c2c586d6212b6fdd5ad0 (diff)
downloadeclipselink.runtime-ed6587974b6874e765ed74127f319036e762ad04.zip
eclipselink.runtime-ed6587974b6874e765ed74127f319036e762ad04.tar.gz
eclipselink.runtime-ed6587974b6874e765ed74127f319036e762ad04.tar.bz2
Bug#380101: Invalid MySQL SQL syntax in query with LIMIT and FOR UPDATE
Signed-off-by: Lukas Jungmann <lukas.jungmann@oracle.com>
-rw-r--r--foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java40
-rw-r--r--foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/expressions/SQLSelectStatement.java72
-rw-r--r--foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database/MySQLPlatform.java71
-rw-r--r--jpa/eclipselink.jpa.test/src/org/eclipse/persistence/testing/tests/jpa/jpql/AdvancedQueryTestSuite.java118
4 files changed, 197 insertions, 104 deletions
diff --git a/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java b/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java
index d0624b0..1508d43 100644
--- a/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java
+++ b/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java
@@ -1,8 +1,8 @@
-/*******************************************************************************
- * Copyright (c) 1998, 2013 Oracle and/or its affiliates. All rights reserved.
- * This program and the accompanying materials are made available under the
- * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
- * which accompanies this distribution.
+/*******************************************************************************
+ * Copyright (c) 1998, 2014 Oracle and/or its affiliates. All rights reserved.
+ * This program and the accompanying materials are made available under the
+ * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
+ * which accompanies this distribution.
* The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
* and the Eclipse Distribution License is available at
* http://www.eclipse.org/org/documents/edl-v10.php.
@@ -15,12 +15,14 @@
* Dies Koper (Fujitsu) - added methods to create/drop indices
* Vikram Bhatia - added method for releasing temporary LOBs after conversion
* 09/09/2011-2.3.1 Guy Pelletier
- * - 356197: Add new VPD type to MultitenantType
- * 02/04/2013-2.5 Guy Pelletier
- * - 389090: JPA 2.1 DDL Generation Support
- ******************************************************************************/
-package org.eclipse.persistence.internal.databaseaccess;
-
+ * - 356197: Add new VPD type to MultitenantType
+ * 02/04/2013-2.5 Guy Pelletier
+ * - 389090: JPA 2.1 DDL Generation Support
+ * 04/30/2014-2.6 Lukas Jungmann
+ * - 380101: Invalid MySQL SQL syntax in query with LIMIT and FOR UPDATE
+ ******************************************************************************/
+package org.eclipse.persistence.internal.databaseaccess;
+
// javase imports
import java.io.ByteArrayInputStream;
import java.io.CharArrayReader;
@@ -2179,12 +2181,16 @@ public class DatabasePlatform extends DatasourcePlatform {
* e.g. EXECUTE PROCEDURE MyStoredProc(myvariable = ?)
*/
public boolean shouldPrintStoredProcedureArgumentNameInCall(){
- return true;
- }
-
- public boolean shouldTrimStrings() {
- return shouldTrimStrings;
- }
+ return true;
+ }
+
+ public boolean shouldPrintForUpdateClause() {
+ return true;
+ }
+
+ public boolean shouldTrimStrings() {
+ return shouldTrimStrings;
+ }
public boolean shouldUseCustomModifyForCall(DatabaseField field) {
return (field.getSqlType() == Types.STRUCT &&
diff --git a/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/expressions/SQLSelectStatement.java b/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/expressions/SQLSelectStatement.java
index bd2d86f..6a1d36e 100644
--- a/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/expressions/SQLSelectStatement.java
+++ b/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/internal/expressions/SQLSelectStatement.java
@@ -8,12 +8,14 @@
* http://www.eclipse.org/org/documents/edl-v10.php.
*
* Contributors:
- * Oracle - initial API and implementation from Oracle TopLink
- * 20/11/2012-2.5 Guy Pelletier
- * - 394524: Invalid query key [...] in expression
- ******************************************************************************/
-package org.eclipse.persistence.internal.expressions;
-
+ * Oracle - initial API and implementation from Oracle TopLink
+ * 20/11/2012-2.5 Guy Pelletier
+ * - 394524: Invalid query key [...] in expression
+ * 04/30/2014-2.6 Lukas Jungmann
+ * - 380101: Invalid MySQL SQL syntax in query with LIMIT and FOR UPDATE
+ ******************************************************************************/
+package org.eclipse.persistence.internal.expressions;
+
import java.io.*;
import java.util.*;
import org.eclipse.persistence.exceptions.*;
@@ -503,13 +505,15 @@ public class SQLSelectStatement extends SQLStatement {
// Print outer joins
boolean firstTable = true;
- List<DatabaseTable> outerJoinedAliases = new ArrayList(4); // Must keep track of tables used for outer join so no normal join is given
-
- // prepare to lock tables if required
- boolean shouldPrintUpdateClause = !printer.getPlatform().shouldPrintLockingClauseAfterWhereClause() && (getForUpdateClause() != null);
- Collection aliasesOfTablesToBeLocked = null;
- boolean shouldPrintUpdateClauseForAllTables = false;
- if (shouldPrintUpdateClause) {
+ List<DatabaseTable> outerJoinedAliases = new ArrayList(4); // Must keep track of tables used for outer join so no normal join is given
+
+ // prepare to lock tables if required
+ boolean shouldPrintUpdateClause = printer.getPlatform().shouldPrintForUpdateClause()
+ && !printer.getPlatform().shouldPrintLockingClauseAfterWhereClause()
+ && (getForUpdateClause() != null);
+ Collection aliasesOfTablesToBeLocked = null;
+ boolean shouldPrintUpdateClauseForAllTables = false;
+ if (shouldPrintUpdateClause) {
aliasesOfTablesToBeLocked = getForUpdateClause().getAliasesOfTablesToBeLocked(this);
shouldPrintUpdateClauseForAllTables = aliasesOfTablesToBeLocked.size() == getTableAliases().size();
}
@@ -732,12 +736,22 @@ public class SQLSelectStatement extends SQLStatement {
expression.printSQL(printer);
printer.printString(")");
}
- }
-
- /**
- * INTERNAL: Alias the tables in all of our nodes.
- */
- public void assignAliases(Vector allExpressions) {
+ }
+
+ /**
+ * This method will append the for update clause to the end of the
+ * select statement.
+ */
+ public void appendForUpdateClause(ExpressionSQLPrinter printer) {
+ if (getForUpdateClause() != null) {
+ getForUpdateClause().printSQL(printer, this);
+ }
+ }
+
+ /**
+ * INTERNAL: Alias the tables in all of our nodes.
+ */
+ public void assignAliases(Vector allExpressions) {
// For sub-selects all statements must share aliasing information.
// For CR#2627019
currentAliasNumber = getCurrentAliasNumber();
@@ -1697,17 +1711,15 @@ public class SQLSelectStatement extends SQLStatement {
}
if (hasOrderByExpressions()) {
- appendOrderClauseToWriter(printer);
- }
-
- if(printer.getPlatform().shouldPrintLockingClauseAfterWhereClause()) {
- // For pessimistic locking.
- if (getForUpdateClause() != null) {
- getForUpdateClause().printSQL(printer, this);
- }
- }
-
- if (hasUnionExpressions()) {
+ appendOrderClauseToWriter(printer);
+ }
+
+ if(printer.getPlatform().shouldPrintLockingClauseAfterWhereClause() && printer.getPlatform().shouldPrintForUpdateClause()) {
+ // For pessimistic locking.
+ appendForUpdateClause(printer);
+ }
+
+ if (hasUnionExpressions()) {
appendUnionClauseToWriter(printer);
}
diff --git a/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database/MySQLPlatform.java b/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database/MySQLPlatform.java
index a0c66f4..36cad6f 100644
--- a/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database/MySQLPlatform.java
+++ b/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database/MySQLPlatform.java
@@ -1,8 +1,8 @@
-/*******************************************************************************
- * Copyright (c) 1998, 2013 Oracle and/or its affiliates. All rights reserved.
- * This program and the accompanying materials are made available under the
- * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
- * which accompanies this distribution.
+/*******************************************************************************
+ * Copyright (c) 1998, 2014 Oracle and/or its affiliates. All rights reserved.
+ * This program and the accompanying materials are made available under the
+ * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
+ * which accompanies this distribution.
* The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
* and the Eclipse Distribution License is available at
* http://www.eclipse.org/org/documents/edl-v10.php.
@@ -11,12 +11,14 @@
* Oracle - initial API and implementation from Oracle TopLink
* Zoltan NAGY & tware - added implementation of updateMaxRowsForQuery
* 09/14/2011-2.3.1 Guy Pelletier
- * - 357533: Allow DDL queries to execute even when Multitenant entities are part of the PU
- * 02/04/2013-2.5 Guy Pelletier
- * - 389090: JPA 2.1 DDL Generation Support
- ******************************************************************************/
-package org.eclipse.persistence.platform.database;
-
+ * - 357533: Allow DDL queries to execute even when Multitenant entities are part of the PU
+ * 02/04/2013-2.5 Guy Pelletier
+ * - 389090: JPA 2.1 DDL Generation Support
+ * 04/30/2014-2.6 Lukas Jungmann
+ * - 380101: Invalid MySQL SQL syntax in query with LIMIT and FOR UPDATE
+ ******************************************************************************/
+package org.eclipse.persistence.platform.database;
+
import java.io.*;
import java.util.*;
@@ -555,12 +557,21 @@ public class MySQLPlatform extends DatabasePlatform {
public boolean shouldPrintStoredProcedureArgumentNameInCall(){
return false;
}
-
- /**
- * INTERNAL:
- * MySQL uses ' to allow identifier to have spaces.
- * @deprecated
- * @see getStartDelimiter()
+
+ /**
+ * INTERNAL:
+ * MySQL FOR UPDATE clause has to be the last
+ */
+ @Override
+ public boolean shouldPrintForUpdateClause(){
+ return false;
+ }
+
+ /**
+ * INTERNAL:
+ * MySQL uses ' to allow identifier to have spaces.
+ * @deprecated
+ * @see getStartDelimiter()
* @see getEndDelimiter()
*/
@Override
@@ -668,20 +679,22 @@ public class MySQLPlatform extends DatabasePlatform {
max = statement.getQuery().getMaxRows();
firstRow = statement.getQuery().getFirstResult();
}
-
- if (max <= 0 || !(this.shouldUseRownumFiltering())) {
- super.printSQLSelectStatement(call, printer, statement);
- return;
- }
- statement.setUseUniqueFieldAliases(true);
+
+ if (max <= 0 || !(this.shouldUseRownumFiltering())) {
+ super.printSQLSelectStatement(call, printer, statement);
+ statement.appendForUpdateClause(printer);
+ return;
+ }
+ statement.setUseUniqueFieldAliases(true);
call.setFields(statement.printSQL(printer));
printer.printString(LIMIT);
- printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD);
- printer.printString(", ");
- printer.printParameter(DatabaseCall.MAXROW_FIELD);
- call.setIgnoreFirstRowSetting(true);
- call.setIgnoreMaxResultsSetting(true);
- }
+ printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD);
+ printer.printString(", ");
+ printer.printParameter(DatabaseCall.MAXROW_FIELD);
+ statement.appendForUpdateClause(printer);
+ call.setIgnoreFirstRowSetting(true);
+ call.setIgnoreMaxResultsSetting(true);
+ }
/**
* Used for stored procedure creation: MySQL platforms need brackets around arguments declaration even if no arguments exist.
diff --git a/jpa/eclipselink.jpa.test/src/org/eclipse/persistence/testing/tests/jpa/jpql/AdvancedQueryTestSuite.java b/jpa/eclipselink.jpa.test/src/org/eclipse/persistence/testing/tests/jpa/jpql/AdvancedQueryTestSuite.java
index 495ca51..9717aa1 100644
--- a/jpa/eclipselink.jpa.test/src/org/eclipse/persistence/testing/tests/jpa/jpql/AdvancedQueryTestSuite.java
+++ b/jpa/eclipselink.jpa.test/src/org/eclipse/persistence/testing/tests/jpa/jpql/AdvancedQueryTestSuite.java
@@ -1,8 +1,8 @@
-/*******************************************************************************
- * Copyright (c) 1998, 2013 Oracle and/or its affiliates. All rights reserved.
- * This program and the accompanying materials are made available under the
- * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
- * which accompanies this distribution.
+/*******************************************************************************
+ * Copyright (c) 1998, 2014 Oracle and/or its affiliates. All rights reserved.
+ * This program and the accompanying materials are made available under the
+ * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
+ * which accompanies this distribution.
* The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
* and the Eclipse Distribution License is available at
* http://www.eclipse.org/org/documents/edl-v10.php.
@@ -32,12 +32,13 @@ import javax.persistence.RollbackException;
import junit.framework.Test;
import junit.framework.TestSuite;
-
-import org.eclipse.persistence.annotations.BatchFetchType;
-import org.eclipse.persistence.config.CacheUsage;
-import org.eclipse.persistence.config.QueryHints;
-import org.eclipse.persistence.config.QueryType;
-import org.eclipse.persistence.config.ResultSetConcurrency;
+
+import org.eclipse.persistence.annotations.BatchFetchType;
+import org.eclipse.persistence.config.CacheUsage;
+import org.eclipse.persistence.config.PessimisticLock;
+import org.eclipse.persistence.config.QueryHints;
+import org.eclipse.persistence.config.QueryType;
+import org.eclipse.persistence.config.ResultSetConcurrency;
import org.eclipse.persistence.config.ResultSetType;
import org.eclipse.persistence.config.ResultType;
import org.eclipse.persistence.descriptors.invalidation.DailyCacheInvalidationPolicy;
@@ -46,13 +47,12 @@ import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.jpa.JpaQuery;
import org.eclipse.persistence.queries.Cursor;
import org.eclipse.persistence.queries.ReadQuery;
-import org.eclipse.persistence.queries.ScrollableCursor;
-import org.eclipse.persistence.sessions.DatabaseSession;
-import org.eclipse.persistence.sessions.server.ServerSession;
-
-import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;
-import org.eclipse.persistence.testing.framework.QuerySQLTracker;
-import org.eclipse.persistence.testing.models.jpa.inheritance.Engineer;
+import org.eclipse.persistence.queries.ScrollableCursor;
+import org.eclipse.persistence.sessions.DatabaseSession;
+import org.eclipse.persistence.sessions.server.ServerSession;
+import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;
+import org.eclipse.persistence.testing.framework.QuerySQLTracker;
+import org.eclipse.persistence.testing.models.jpa.inheritance.Engineer;
import org.eclipse.persistence.testing.models.jpa.inheritance.InheritancePopulator;
import org.eclipse.persistence.testing.models.jpa.inheritance.InheritanceTableCreator;
import org.eclipse.persistence.testing.models.jpa.advanced.Buyer;
@@ -116,12 +116,13 @@ public class AdvancedQueryTestSuite extends JUnitTestCase {
suite.addTest(new AdvancedQueryTestSuite("testQueryOPTIMISTICLock"));
suite.addTest(new AdvancedQueryTestSuite("testQueryOPTIMISTIC_FORCE_INCREMENTLock"));
suite.addTest(new AdvancedQueryTestSuite("testQueryPESSIMISTIC_READLock"));
- suite.addTest(new AdvancedQueryTestSuite("testQueryPESSIMISTIC_WRITELock"));
- suite.addTest(new AdvancedQueryTestSuite("testQueryPESSIMISTIC_READ_TIMEOUTLock"));
- suite.addTest(new AdvancedQueryTestSuite("testQueryPESSIMISTIC_WRITE_TIMEOUTLock"));
- suite.addTest(new AdvancedQueryTestSuite("testObjectResultType"));
- suite.addTest(new AdvancedQueryTestSuite("testNativeResultType"));
- suite.addTest(new AdvancedQueryTestSuite("testCursors"));
+ suite.addTest(new AdvancedQueryTestSuite("testQueryPESSIMISTIC_WRITELock"));
+ suite.addTest(new AdvancedQueryTestSuite("testQueryPESSIMISTIC_READ_TIMEOUTLock"));
+ suite.addTest(new AdvancedQueryTestSuite("testQueryPESSIMISTIC_WRITE_TIMEOUTLock"));
+ suite.addTest(new AdvancedQueryTestSuite("testQueryPESSIMISTICLockWithLimit"));
+ suite.addTest(new AdvancedQueryTestSuite("testObjectResultType"));
+ suite.addTest(new AdvancedQueryTestSuite("testNativeResultType"));
+ suite.addTest(new AdvancedQueryTestSuite("testCursors"));
suite.addTest(new AdvancedQueryTestSuite("testFetchGroups"));
suite.addTest(new AdvancedQueryTestSuite("testMultipleNamedJoinFetchs"));
suite.addTest(new AdvancedQueryTestSuite("testNativeQueryTransactions"));
@@ -2703,7 +2704,68 @@ public class AdvancedQueryTestSuite extends JUnitTestCase {
reset.setLastName(lastName);
}
commitTransaction(em);
- closeEntityManagerAndTransaction(em);
- }
- }
-}
+ closeEntityManagerAndTransaction(em);
+ }
+ }
+
+ public void testQueryPESSIMISTICLockWithLimit() throws InterruptedException {
+ clearCache();
+ EntityManager em = createEntityManager();
+ beginTransaction(em);
+ try {
+ Query query = em.createQuery("Select e from Employee e");
+ query.setHint(QueryHints.PESSIMISTIC_LOCK, PessimisticLock.Lock);
+ query.setFirstResult(5);
+ query.setMaxResults(2);
+ List<Employee> results = query.getResultList();
+ final Employee e = results.get(0);
+ final String name = e.getFirstName();
+ if (results.size() > 2) {
+ fail("Should have only returned 2 objects but was: " + results.size());
+ }
+ clearCache();
+
+ final EntityManager em2 = createEntityManager();
+ try {
+ // P2 (Non-repeatable read)
+ Runnable runnable = new Runnable() {
+ @Override
+ public void run() {
+ try {
+ beginTransaction(em2);
+ Query query2 = em2.createQuery("select e from Employee e where e.id = :id");
+ query2.setParameter("id", e.getId());
+ query2.setHint(QueryHints.PESSIMISTIC_LOCK_TIMEOUT, 5);
+ Employee emp = (Employee) query2.getSingleResult(); // might wait for lock to be released
+ emp.setFirstName("Trouba");
+ commitTransaction(em2); // might wait for lock to be released
+ } catch (javax.persistence.RollbackException ex) {
+ if (ex.getMessage().indexOf("org.eclipse.persistence.exceptions.DatabaseException") == -1) {
+ ex.printStackTrace();
+ fail("it's not the right exception:" + ex);
+ }
+ }
+ }
+ };
+
+ Thread t2 = new Thread(runnable);
+ t2.start();
+ Thread.sleep(1000); // allow t2 to attempt update
+ em.refresh(e);
+ assertTrue("pessimistic lock failed: parallel transaction modified locked entity (non-repeatable read)", name.equals(e.getFirstName()));
+ rollbackTransaction(em); // release lock
+ t2.join(); // wait until t2 finished
+ } finally {
+ if (isTransactionActive(em2)) {
+ rollbackTransaction(em2);
+ }
+ closeEntityManager(em2);
+ }
+ } finally {
+ if (isTransactionActive(em)) {
+ rollbackTransaction(em);
+ }
+ closeEntityManager(em);
+ }
+ }
+}