Bug 578686: ParameterExpression in ORDER BY clause doesn't register
Signed-off-by: Will Dazey <dazeydev.3@gmail.com>
diff --git a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java
index 1cf20de..8c3ca28 100644
--- a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java
+++ b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/databaseaccess/DatabasePlatform.java
@@ -1,6 +1,6 @@
/*
* Copyright (c) 1998, 2022 Oracle and/or its affiliates. All rights reserved.
- * Copyright (c) 2019 IBM Corporation. All rights reserved.
+ * Copyright (c) 2019, 2022 IBM Corporation. All rights reserved.
*
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v. 2.0 which is available at
@@ -2409,6 +2409,15 @@
return false;
}
+ /**
+ * Used to determine if the platform supports untyped parameters, as ordinal variables, within the Order By clause
+ * <p>
+ * On by default. Only platforms without support added should disable this configuration.
+ */
+ public boolean supportsOrderByParameters() {
+ return true;
+ }
+
public boolean supportsDeleteOnCascade() {
return supportsForeignKeyConstraints();
}
diff --git a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/expressions/SQLSelectStatement.java b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/expressions/SQLSelectStatement.java
index b4edcfd..4fa9a39 100644
--- a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/expressions/SQLSelectStatement.java
+++ b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/internal/expressions/SQLSelectStatement.java
@@ -1,5 +1,6 @@
/*
* Copyright (c) 1998, 2022 Oracle and/or its affiliates. All rights reserved.
+ * Copyright (c) 2022 IBM Corporation. All rights reserved.
*
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v. 2.0 which is available at
@@ -762,6 +763,13 @@
for (Iterator<Expression> expressionsEnum = getOrderByExpressions().iterator(); expressionsEnum.hasNext();) {
Expression expression = expressionsEnum.next();
+
+ if(!printer.getPlatform().supportsOrderByParameters()) {
+ if(expression.isParameterExpression() || expression.isConstantExpression()) {
+ printer.getCall().setUsesBinding(false);
+ }
+ }
+
expression.printSQL(printer);
if (expressionsEnum.hasNext()) {
diff --git a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/DB2Platform.java b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/DB2Platform.java
index 1f256d2..d6c385a 100644
--- a/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/DB2Platform.java
+++ b/foundation/org.eclipse.persistence.core/src/main/java/org/eclipse/persistence/platform/database/DB2Platform.java
@@ -1,6 +1,6 @@
/*
- * Copyright (c) 1998, 2021 Oracle and/or its affiliates. All rights reserved.
- * Copyright (c) 1998, 2021 IBM Corporation. All rights reserved.
+ * Copyright (c) 1998, 2022 Oracle and/or its affiliates. All rights reserved.
+ * Copyright (c) 1998, 2022 IBM Corporation. All rights reserved.
*
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v. 2.0 which is available at
@@ -816,6 +816,11 @@
return true;
}
+ @Override
+ public boolean supportsOrderByParameters() {
+ return false;
+ }
+
/**
* INTERNAL: DB2 added SEQUENCE support as of (I believe) v8.
*/
diff --git a/jpa/eclipselink.jpa.test.jse/src/it/java/org/eclipse/persistence/jpa/test/query/TestQueryOrderBy.java b/jpa/eclipselink.jpa.test.jse/src/it/java/org/eclipse/persistence/jpa/test/query/TestQueryOrderBy.java
new file mode 100644
index 0000000..87e0363
--- /dev/null
+++ b/jpa/eclipselink.jpa.test.jse/src/it/java/org/eclipse/persistence/jpa/test/query/TestQueryOrderBy.java
@@ -0,0 +1,256 @@
+/*
+ * Copyright (c) 2022 Oracle and/or its affiliates. All rights reserved.
+ * Copyright (c) 2022 IBM Corporation. All rights reserved.
+ *
+ * This program and the accompanying materials are made available under the
+ * terms of the Eclipse Public License v. 2.0 which is available at
+ * http://www.eclipse.org/legal/epl-2.0,
+ * or the Eclipse Distribution License v. 1.0 which is available at
+ * http://www.eclipse.org/org/documents/edl-v10.php.
+ *
+ * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
+ */
+// Contributors:
+// IBM - Bug 417259: Add support for Parameters in CriteriaBuilder in HAVING clause
+package org.eclipse.persistence.jpa.test.query;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNotNull;
+
+import java.util.List;
+import jakarta.persistence.EntityManager;
+import jakarta.persistence.EntityManagerFactory;
+import jakarta.persistence.TypedQuery;
+import jakarta.persistence.criteria.CriteriaBuilder;
+import jakarta.persistence.criteria.CriteriaQuery;
+import jakarta.persistence.criteria.ParameterExpression;
+import jakarta.persistence.criteria.Root;
+
+import org.eclipse.persistence.jpa.test.framework.DDLGen;
+import org.eclipse.persistence.jpa.test.framework.Emf;
+import org.eclipse.persistence.jpa.test.framework.EmfRunner;
+import org.eclipse.persistence.jpa.test.framework.Property;
+import org.eclipse.persistence.jpa.test.query.model.EntityTbl01;
+import org.eclipse.persistence.jpa.test.query.model.EntityTbl01_;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+
+@RunWith(EmfRunner.class)
+public class TestQueryOrderBy {
+ @Emf(createTables = DDLGen.DROP_CREATE, classes = { EntityTbl01.class },
+ properties = { @Property(name="eclipselink.logging.level", value="FINE")})
+ private EntityManagerFactory emf;
+
+ @Emf(name = "BindLiteralsPersistenceUnit", classes = { EntityTbl01.class },
+ properties = {
+ @Property(name="eclipselink.target-database-properties", value="shouldBindLiterals=true"),
+ @Property(name="eclipselink.logging.level", value="FINE")})
+ private EntityManagerFactory emf2;
+
+ private static boolean POPULATED = false;
+
+ @Test
+ public void testQueryOrderByLiterals1() {
+ if (emf == null)
+ return;
+
+ if(!POPULATED)
+ populate();
+
+ EntityManager em = emf.createEntityManager();
+
+ try {
+ TypedQuery<Integer> query = em.createQuery(""
+ + "SELECT t.itemInteger1 FROM EntityTbl01 t ORDER BY 1", Integer.class);
+
+ List<Integer> dto01 = query.getResultList();
+ assertNotNull(dto01);
+ assertEquals(4, dto01.size());
+ assertEquals(new Integer(29), dto01.get(1));
+
+ query = em.createQuery(""
+ + "SELECT t.itemInteger1 FROM EntityTbl01 t ORDER BY 1 ASC", Integer.class);
+
+ dto01 = query.getResultList();
+ assertNotNull(dto01);
+ assertEquals(4, dto01.size());
+ assertEquals(new Integer(29), dto01.get(1));
+
+ // equivalent CriteriaBuilder
+ CriteriaBuilder cb = em.getCriteriaBuilder();
+ CriteriaQuery<Integer> cquery = cb.createQuery(Integer.class);
+ Root<EntityTbl01> root = cquery.from(EntityTbl01.class);
+ cquery.multiselect(root.get(EntityTbl01_.itemInteger1));
+
+ cquery.orderBy(cb.asc(cb.literal(1)));
+
+ query = em.createQuery(cquery);
+ dto01 = query.getResultList();
+ assertNotNull(dto01);
+ assertEquals(4, dto01.size());
+ assertEquals(new Integer(29), dto01.get(1));
+ } finally {
+ if (em.getTransaction().isActive()) {
+ em.getTransaction().rollback();
+ }
+ if(em.isOpen()) {
+ em.close();
+ }
+ }
+ }
+
+ @Test
+ public void testQueryOrderByLiterals2() {
+ if (emf2 == null)
+ return;
+
+ if(!POPULATED)
+ populate();
+
+ EntityManager em = emf2.createEntityManager();
+
+ try {
+ TypedQuery<Integer> query = em.createQuery(""
+ + "SELECT t.itemInteger1 FROM EntityTbl01 t ORDER BY 1", Integer.class);
+
+ List<Integer> dto01 = query.getResultList();
+ assertNotNull(dto01);
+ assertEquals(4, dto01.size());
+ assertEquals(new Integer(29), dto01.get(1));
+
+ query = em.createQuery(""
+ + "SELECT t.itemInteger1 FROM EntityTbl01 t ORDER BY 1 ASC", Integer.class);
+
+ dto01 = query.getResultList();
+ assertNotNull(dto01);
+ assertEquals(4, dto01.size());
+ assertEquals(new Integer(29), dto01.get(1));
+
+ // equivalent CriteriaBuilder
+ CriteriaBuilder cb = em.getCriteriaBuilder();
+ CriteriaQuery<Integer> cquery = cb.createQuery(Integer.class);
+ Root<EntityTbl01> root = cquery.from(EntityTbl01.class);
+ cquery.multiselect(root.get(EntityTbl01_.itemInteger1));
+
+ cquery.orderBy(cb.asc(cb.literal(1)));
+
+ query = em.createQuery(cquery);
+ dto01 = query.getResultList();
+ assertNotNull(dto01);
+ assertEquals(4, dto01.size());
+ assertEquals(new Integer(29), dto01.get(1));
+ } finally {
+ if (em.getTransaction().isActive()) {
+ em.getTransaction().rollback();
+ }
+ if(em.isOpen()) {
+ em.close();
+ }
+ }
+ }
+
+ @Test
+ public void testQueryOrderByParameters1() {
+ if (emf == null)
+ return;
+
+ if(!POPULATED)
+ populate();
+
+ EntityManager em = emf.createEntityManager();
+
+ try {
+ TypedQuery<Integer> query = em.createQuery(""
+ + "SELECT t.itemInteger1 FROM EntityTbl01 t ORDER BY ?1", Integer.class);
+ query.setParameter(1, 1);
+
+ List<Integer> dto01 = query.getResultList();
+ assertNotNull(dto01);
+ assertEquals(4, dto01.size());
+ assertEquals(new Integer(29), dto01.get(1));
+
+ query = em.createQuery(""
+ + "SELECT t.itemInteger1 FROM EntityTbl01 t ORDER BY ?1 ASC", Integer.class);
+ query.setParameter(1, 1);
+
+ dto01 = query.getResultList();
+ assertNotNull(dto01);
+ assertEquals(4, dto01.size());
+ assertEquals(new Integer(29), dto01.get(1));
+
+ // equivalent CriteriaBuilder
+ CriteriaBuilder cb = em.getCriteriaBuilder();
+ CriteriaQuery<Integer> cquery = cb.createQuery(Integer.class);
+ Root<EntityTbl01> root = cquery.from(EntityTbl01.class);
+ cquery.multiselect(root.get(EntityTbl01_.itemInteger1));
+
+ ParameterExpression<Integer> checkParam1 = cb.parameter(Integer.class);
+ cquery.orderBy(cb.asc(checkParam1));
+
+ query = em.createQuery(cquery);
+ query.setParameter(checkParam1, 1);
+ dto01 = query.getResultList();
+ assertNotNull(dto01);
+ assertEquals(4, dto01.size());
+ assertEquals(new Integer(29), dto01.get(1));
+ } finally {
+ if (em.getTransaction().isActive()) {
+ em.getTransaction().rollback();
+ }
+ if(em.isOpen()) {
+ em.close();
+ }
+ }
+ }
+
+ private void populate() {
+ EntityManager em = emf.createEntityManager();
+ try {
+ em.getTransaction().begin();
+
+ EntityTbl01 tbl1 = new EntityTbl01();
+ tbl1.setKeyString("Key14");
+ tbl1.setItemString1("A");
+ tbl1.setItemString2(null);
+ tbl1.setItemString3("C");
+ tbl1.setItemString4("D");
+ tbl1.setItemInteger1(45);
+ em.persist(tbl1);
+
+ EntityTbl01 tbl2 = new EntityTbl01();
+ tbl2.setKeyString("Key15");
+ tbl2.setItemString1("A");
+ tbl2.setItemString2("B");
+ tbl2.setItemString3("C");
+ tbl2.setItemString4(null);
+ tbl2.setItemInteger1(83);
+ em.persist(tbl2);
+
+ EntityTbl01 tbl3 = new EntityTbl01();
+ tbl3.setKeyString("Key16");
+ tbl3.setItemString1(null);
+ tbl3.setItemString2("B");
+ tbl3.setItemString3("C");
+ tbl3.setItemString4("D");
+ tbl3.setItemInteger1(17);
+ em.persist(tbl3);
+
+ EntityTbl01 tbl4 = new EntityTbl01();
+ tbl4.setKeyString("Key17");
+ tbl4.setItemString1("A");
+ tbl4.setItemString2("B");
+ tbl4.setItemString3("C");
+ tbl4.setItemString4(null);
+ tbl4.setItemInteger1(29);
+ em.persist(tbl4);
+
+ em.getTransaction().commit();
+
+ POPULATED = true;
+ } finally {
+ if(em.isOpen()) {
+ em.close();
+ }
+ }
+ }
+}
diff --git a/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/CommonAbstractCriteriaImpl.java b/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/CommonAbstractCriteriaImpl.java
index 9ed8762..00e8c62 100644
--- a/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/CommonAbstractCriteriaImpl.java
+++ b/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/CommonAbstractCriteriaImpl.java
@@ -1,5 +1,6 @@
/*
- * Copyright (c) 2011, 2021 Oracle and/or its affiliates. All rights reserved.
+ * Copyright (c) 2011, 2022 Oracle and/or its affiliates. All rights reserved.
+ * Copyright (c) 2022 IBM Corporation. All rights reserved.
*
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v. 2.0 which is available at
@@ -21,6 +22,7 @@
import jakarta.persistence.criteria.CommonAbstractCriteria;
import jakarta.persistence.criteria.Expression;
+import jakarta.persistence.criteria.Order;
import jakarta.persistence.criteria.ParameterExpression;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;
@@ -173,6 +175,10 @@
((InternalSelection) predicate).findRootAndParameters(this);
}
+ protected void findRootAndParameters(Order order) {
+ ((OrderImpl) order).findRootAndParameters(this);
+ }
+
protected abstract org.eclipse.persistence.expressions.Expression getBaseExpression();
public void addParameter(ParameterExpression<?> parameter) {
diff --git a/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/CriteriaQueryImpl.java b/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/CriteriaQueryImpl.java
index 438ae74..e82a452 100644
--- a/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/CriteriaQueryImpl.java
+++ b/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/CriteriaQueryImpl.java
@@ -1,6 +1,6 @@
/*
- * Copyright (c) 2011, 2021 Oracle and/or its affiliates. All rights reserved.
- * Copyright (c) 2021 IBM Corporation. All rights reserved.
+ * Copyright (c) 2011, 2022 Oracle and/or its affiliates. All rights reserved.
+ * Copyright (c) 2021, 2022 IBM Corporation. All rights reserved.
*
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v. 2.0 which is available at
@@ -363,6 +363,7 @@
public CriteriaQuery<T> orderBy(Order... o) {
this.orderBy = new ArrayList<>();
for (Order order : o) {
+ findRootAndParameters(order);
this.orderBy.add(order);
}
return this;
@@ -382,6 +383,9 @@
*/
@Override
public CriteriaQuery<T> orderBy(List<Order> o) {
+ for (Order order : o) {
+ findRootAndParameters(order);
+ }
this.orderBy = o;
return this;
}
diff --git a/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/OrderImpl.java b/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/OrderImpl.java
index 98fb52a..0b5a456 100644
--- a/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/OrderImpl.java
+++ b/jpa/org.eclipse.persistence.jpa/src/main/java/org/eclipse/persistence/internal/jpa/querydef/OrderImpl.java
@@ -49,4 +49,10 @@
return new OrderImpl(this.expression, false);
}
+ public void findRootAndParameters(CommonAbstractCriteriaImpl query) {
+ if(expression != null) {
+ ((InternalSelection)expression).findRootAndParameters(query);
+ }
+ }
+
}
