openjpa-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ppod...@apache.org
Subject svn commit: r785341 [1/3] - in /openjpa/trunk: openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/ openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/ openjpa-persistence/src/main/java/org/apache/openjpa/persiste...
Date Tue, 16 Jun 2009 18:56:58 GMT
Author: ppoddar
Date: Tue Jun 16 18:56:57 2009
New Revision: 785341

URL: http://svn.apache.org/viewvc?rev=785341&view=rev
Log:
OPENJPA-1013: Joins, key/value, parameters, embedded 

Added:
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java   (with props)
Modified:
    openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/CoalesceExpression.java
    openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/NullIfExpression.java
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Account.java
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Course.java
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CreditCard_.java
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CriteriaTest.java
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Employee_.java
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJoinCondition.java
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestMetaModelTypesafeCriteria.java
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestTypeSafeCondExpression.java
    openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestTypesafeCriteria.java
    openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/CriteriaBuilder.java
    openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/CriteriaExpressionBuilder.java
    openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/CriteriaQueryImpl.java
    openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/Expressions.java
    openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/Joins.java
    openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/ParameterImpl.java
    openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/PathImpl.java
    openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/criteria/SubqueryImpl.java
    openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/meta/Members.java
    openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/meta/MetamodelImpl.java
    openjpa/trunk/openjpa-persistence/src/main/java/org/apache/openjpa/persistence/meta/Types.java

Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/CoalesceExpression.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/CoalesceExpression.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/CoalesceExpression.java (original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/CoalesceExpression.java Tue Jun 16 18:56:57 2009
@@ -95,8 +95,7 @@
         SQLBuffer buf, int index) {
         CoalesceExpState cstate = (CoalesceExpState) state;
         
-        buf.append(" COALESCE ");
-        buf.append("(");
+        buf.append(" COALESCE("); // MySQL does not like space before bracket
 
         for (int i = 0; i < _vals.length; i++) {
             if (i > 0)

Modified: openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/NullIfExpression.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/NullIfExpression.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/NullIfExpression.java (original)
+++ openjpa/trunk/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/NullIfExpression.java Tue Jun 16 18:56:57 2009
@@ -82,8 +82,7 @@
         SQLBuffer buf, int index) {
         BinaryOpExpState bstate = (BinaryOpExpState) state;
         
-        buf.append(" NULLIF ");
-        buf.append("(");
+        buf.append(" NULLIF("); // MySQL does not like space before bracket
 
         _val1.appendTo(sel, ctx, bstate.state1, buf, 0);
         buf.append(",");

Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Account.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Account.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Account.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Account.java Tue Jun 16 18:56:57 2009
@@ -66,7 +66,7 @@
         return balance;
     }
 
-    public void setBalancey(int balance) {
+    public void setBalance(int balance) {
         this.balance = balance;
     }
 

Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Course.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Course.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Course.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Course.java Tue Jun 16 18:56:57 2009
@@ -24,7 +24,7 @@
 import javax.persistence.GeneratedValue;
 import javax.persistence.Id;
 import javax.persistence.OneToMany;
-import javax.persistence.OrderBy;
+import javax.persistence.OrderColumn;
 import javax.persistence.Table;
 
 @Entity
@@ -38,7 +38,7 @@
     private String name;
 
     @OneToMany
-    @OrderBy
+    @OrderColumn
     private List<Student> studentWaitList;
 
     public long getId() {

Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CreditCard_.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CreditCard_.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CreditCard_.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CreditCard_.java Tue Jun 16 18:56:57 2009
@@ -12,6 +12,5 @@
 public class CreditCard_ {
     public static volatile Attribute<CreditCard,Customer> customer;
     public static volatile Attribute<CreditCard,Long> id;
-    public static volatile List<CreditCard,TransactionHistory> 
-    transactionHistory;
+    public static volatile List<CreditCard,TransactionHistory> transactionHistory;
 }

Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CriteriaTest.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CriteriaTest.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CriteriaTest.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/CriteriaTest.java Tue Jun 16 18:56:57 2009
@@ -117,11 +117,14 @@
      * configuration values in the form key,value,key,value...
      */
     protected void createNamedEMF(Class<?>... types) {
-        Map map = new HashMap();
+        Map<Object,Object> map = new HashMap<Object,Object>();
         map.put("openjpa.jdbc.SynchronizeMappings",
                 "buildSchema(ForeignKeys=true," 
               + "SchemaAction='add,deleteTableContents')");
+        map.put("openjpa.jdbc.QuerySQLCache", "false");
         map.put("openjpa.DynamicEnhancementAgent", "false");
+        map.put("openjpa.RuntimeUnenhancedClasses", "unsupported");
+        map.put("openjpa.Compatibility", "QuotedNumbersInQueries=true");
         map.put("openjpa.jdbc.JDBCListeners", 
                 new JDBCListener[] { new Listener() });
         
@@ -223,10 +226,10 @@
             e.printStackTrace();
             sqls[0] = new ArrayList<String>();
             sqls[0].add(extractSQL(e));
-            fail("JPQL :" + jpql + "\r\nSQL  :" + sqls[0]);
+            fail("Wrong SQL for JPQL :" + jpql + "\r\nSQL  :" + sqls[0]);
         } catch (Exception e) {
             e.printStackTrace();
-            fail("JPQL :" + jpql);
+            fail("Wrong JPQL :" + jpql);
         }
         
         sqls[0] = new ArrayList<String>(sql);
@@ -244,6 +247,23 @@
 
         return true;
     }
+    
+    List<String> executeJPQL(String jpql, Map<?,Object> params) {
+        sql.clear();
+        Query q  = em.createQuery(jpql);
+        if (params != null) {
+            for (Object key : params.keySet()) {
+                if (key instanceof String)
+                    q.setParameter(key.toString(), params.get(key));
+                else if (key instanceof String)
+                    q.setParameter(key.toString(), params.get(key));
+                else
+                    throw new RuntimeException("Bad Parameter key " + key);
+            }
+        }
+        
+        return sql;
+    }
 
     String extractSQL(PersistenceException e) {
         Throwable t = e.getCause();
@@ -287,8 +307,12 @@
      * Affirms if the test case or the test method is annotated with 
      * @AllowFailure. Method level annotation has higher precedence than Class
      * level annotation.
+     * 
+     * Set -DIgnoreAllowFailure=true to ignore this directive altogether.
      */
     protected AllowFailure getAllowFailure() {
+        if (Boolean.getBoolean("IgnoreAllowFailure"))
+            return null;
         try {
             Method runMethod = getClass().getMethod(getName(), (Class[])null);
             AllowFailure anno = runMethod.getAnnotation(AllowFailure.class);

Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Employee_.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Employee_.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Employee_.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/Employee_.java Tue Jun 16 18:56:57 2009
@@ -12,8 +12,7 @@
     public static volatile Attribute<Employee,Contact> contactInfo;
     public static volatile Attribute<Employee,Department> department;
     public static volatile Attribute<Employee,Integer> empId;
-    public static volatile Attribute<Employee,FrequentFlierPlan> 
-    frequentFlierPlan;
+    public static volatile Attribute<Employee,FrequentFlierPlan> frequentFlierPlan;
     public static volatile Attribute<Employee,Manager> manager;
     public static volatile Attribute<Employee,String> name;
     public static volatile Attribute<Employee,Integer> rating;

Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJoinCondition.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJoinCondition.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJoinCondition.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestJoinCondition.java Tue Jun 16 18:56:57 2009
@@ -9,10 +9,10 @@
 import javax.persistence.criteria.Join;
 import javax.persistence.criteria.JoinType;
 import javax.persistence.criteria.ListJoin;
+import javax.persistence.criteria.MapJoin;
 import javax.persistence.criteria.Root;
 import javax.persistence.criteria.SetJoin;
-
-import org.apache.openjpa.persistence.test.AllowFailure;
+import javax.persistence.metamodel.Bindable;
 
 /**
  * Tests Criteria Queries that use Join.
@@ -21,13 +21,19 @@
  *
  */
 public class TestJoinCondition extends CriteriaTest {
-    
+    protected Class[] getDomainClasses() {
+        return new Class[]{A.class,B.class,C.class,D.class};
+    }
+
     public void testSingleAttributeJoinModel() {
         CriteriaQuery cq = cb.create();
         Root<A> a = cq.from(A.class);
         Join<A,B> b = a.join(A_.b);
+        assertTrue(b.getModel() instanceof Bindable);
         assertSame(B.class, b.getJavaType());
         assertSame(B.class, b.getMember().getMemberJavaType());
+        assertEquals(Bindable.BindableType.MANAGED_TYPE,
+           b.getModel().getBindableType());
     }
     
     public void testCollectionJoinModel() {
@@ -71,7 +77,6 @@
         assertEquivalence(cq, jpql);
     }
     
-    @AllowFailure(message="Missing where clause")
     public void testCrossJoin() {
         String jpql = "select a from A a, C c where a.name=c.name";
         CriteriaQuery cq = cb.create();
@@ -143,4 +148,23 @@
         assertEquivalence(c, jpql);
     }
  
+    public void testKeyExpression() {
+        String jpql = "select c from C c JOIN c.map d where KEY(d)=33";
+        CriteriaQuery cq = cb.create();
+        Root<C> c = cq.from(C.class);
+        MapJoin<C,Integer,D> d = c.join(C_.map);
+        cq.where(cb.equal(d.key(),33));
+        
+        assertEquivalence(cq, jpql);
+    }
+    
+    public void testValueExpression() {
+        String jpql = "select c from C c JOIN c.map d where VALUE(d).name='xy'";
+        CriteriaQuery cq = cb.create();
+        Root<C> c = cq.from(C.class);
+        MapJoin<C,Integer,D> d = c.join(C_.map);
+        cq.where(cb.equal(d.value().get(D_.name),"xy"));
+        
+        assertEquivalence(cq, jpql);
+    }
 }

Modified: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestMetaModelTypesafeCriteria.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestMetaModelTypesafeCriteria.java?rev=785341&r1=785340&r2=785341&view=diff
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestMetaModelTypesafeCriteria.java (original)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestMetaModelTypesafeCriteria.java Tue Jun 16 18:56:57 2009
@@ -22,17 +22,18 @@
 import java.util.List;
 
 import javax.persistence.Parameter;
-import javax.persistence.Query;
 import javax.persistence.criteria.CriteriaQuery;
 import javax.persistence.criteria.Join;
 import javax.persistence.criteria.JoinType;
 import javax.persistence.criteria.ListJoin;
 import javax.persistence.criteria.MapJoin;
 import javax.persistence.criteria.Root;
+import javax.persistence.criteria.SetJoin;
 import javax.persistence.criteria.Subquery;
 import javax.persistence.metamodel.Embeddable;
 import javax.persistence.metamodel.Entity;
 import javax.persistence.metamodel.Metamodel;
+import javax.persistence.metamodel.Set;
 
 import org.apache.openjpa.persistence.test.AllowFailure;
 
@@ -66,6 +67,7 @@
         Metamodel mm = em.getMetamodel();
         account_ = mm.entity(Account.class);
         address_ = mm.embeddable(Address.class);
+        assertNotNull(address_);
         contact_ = mm.embeddable(Contact.class);
         course_ = mm.entity(Course.class);
         creditCard_ = mm.entity(CreditCard.class);
@@ -88,50 +90,56 @@
         videoStore_ = mm.entity(VideoStore.class);
     }
 
-    public void testCriteria() {
+    public void testStringEqualExpression() {
         String jpql = "select c from Customer c " 
                     + "where c.name='Autowest Toyota'";
+        
         CriteriaQuery q = cb.create();
         Root<Customer> customer = q.from(Customer.class);
-        q.select(customer).where(
-                cb.equal(customer.get(customer_.getAttribute("name",
-                        String.class)), "Autowest Toyota"));
+        q.select(customer)
+         .where(cb.equal(
+                customer.get(customer_.getAttribute("name", String.class)), 
+                "Autowest Toyota"));
 
         assertEquivalence(q, jpql);
     }
 
-    @AllowFailure
-    public void testJoins() {
-        String jpql = "SELECT c.name FROM Customer c JOIN c.orders o "
-                + "JOIN o.lineItems i WHERE i.product.productType = 'printer'";
+    public void testSetAndListJoins() {
+        String jpql = "SELECT c.name FROM Customer c " 
+                    + "JOIN c.orders o JOIN o.lineItems i " 
+                    + "WHERE i.product.productType = 'printer'";
+        
         CriteriaQuery q = cb.create();
-        Root<Customer> cust = q.from(Customer.class);
-        Join<Customer, Order> order = cust.join(customer_.getSet("orders",
+        Root<Customer> c = q.from(Customer.class);
+        SetJoin<Customer, Order> o = c.join(customer_.getSet("orders",
                 Order.class));
-        Join<Order, LineItem> item = order.join(order_.getList("lineItems",
+        ListJoin<Order, LineItem> i = o.join(order_.getList("lineItems",
                 LineItem.class));
-        q.select(cust.get(Customer_.name)).where(
-                cb.equal(item.get(
-                        lineItem_.getAttribute("product", Product.class)).get(
-                        product_.getAttribute("productType", String.class)),
-                        "printer"));
+        q.select(c.get(Customer_.name)).where(
+                cb.equal(i.get(lineItem_.getAttribute("product", Product.class))
+                    .get(product_.getAttribute("productType", String.class)),
+                    "printer"));
 
         assertEquivalence(q, jpql);
-
-        jpql = "SELECT c FROM Customer c LEFT JOIN c.orders o WHERE "
-                + "c.status = 1";
-        q = cb.create();
-        Root<Customer> cust1 = q.from(Customer.class);
-        Join<Customer, Order> order1 = cust1.join(customer_.getSet("orders",
+    }
+    
+    public void testLeftSetJoin() {
+        String jpql = "SELECT c FROM Customer c "
+                    + "LEFT JOIN c.orders o "
+                    + "WHERE c.status = 1";
+        
+        CriteriaQuery q = cb.create();
+        Root<Customer> c = q.from(Customer.class);
+        SetJoin<Customer, Order> o = c.join(customer_.getSet("orders",
                 Order.class), JoinType.LEFT);
-        q.where(
-                cb.equal(cust1.get(customer_.getAttribute("status",
-                        Integer.class)), 1)).select(cust1);
+        q.where(cb.equal(
+                c.get(customer_.getAttribute("status", Integer.class)), 
+                1));
 
         assertEquivalence(q, jpql);
     }
 
-    @AllowFailure
+    @AllowFailure(message="FetchJoin not implemented")
     public void testFetchJoins() {
         String jpql = "SELECT d FROM Department LEFT JOIN FETCH d.employees "
                 + "WHERE d.deptNo = 1";
@@ -145,11 +153,11 @@
         assertEquivalence(q, jpql);
     }
 
-    @AllowFailure
     public void testPathNavigation() {
         String jpql = "SELECT p.vendor FROM Employee e "
-                + "JOIN e.contactInfo.phones p  "
-                + "WHERE e.contactInfo.address.zipCode = '95054'";
+                    + "JOIN e.contactInfo.phones p  "
+                    + "WHERE e.contactInfo.address.zipCode = '95054'";
+        
         CriteriaQuery q = cb.create();
         Root<Employee> emp = q.from(Employee.class);
         Join<Contact, Phone> phone = emp.join(
@@ -162,22 +170,23 @@
         q.select(phone.get(phone_.getAttribute("vendor", String.class)));
 
         assertEquivalence(q, jpql);
+    }
+    
+    public void testKeyPathNavigation() {
+        String jpql = "SELECT i.name, p FROM Item i JOIN i.photos p " 
+                    + "WHERE KEY(p) LIKE '%egret%'";
 
-        jpql = "SELECT i.name, p FROM Item i JOIN i.photos p WHERE KEY(p) "
-                + "LIKE '%egret%'";
-
-        q = cb.create();
+        CriteriaQuery q = cb.create();
         Root<Item> item = q.from(Item.class);
-        MapJoin<Item, String, Photo> photo = item.join(item_.getMap("photos",
-                String.class, Photo.class));
+        MapJoin<Item, String, Photo> photo = item.join(
+                item_.getMap("photos", String.class, Photo.class));
         q.select(item.get(item_.getAttribute("name", String.class)), photo)
                 .where(cb.like(photo.key(), "%egret%"));
 
         assertEquivalence(q, jpql);
     }
 
-    @AllowFailure
-    public void testRestrictQueryResult() {
+    public void testIndexExpression() {
         String jpql = "SELECT t FROM CreditCard c JOIN c.transactionHistory t "
                 + "WHERE c.customer.accountNum = 321987 AND INDEX(t) BETWEEN 0 "
                 + "AND 9";
@@ -193,24 +202,25 @@
                         321987), cb.between(t.index(), 0, 9));
 
         assertEquivalence(cq, jpql);
-
-        /*
-         * 
-         * jpql = "SELECT o FROM Order o WHERE o.lineItems IS EMPTY"; q =
-         * cb.create(); Root<Order> order = q.from(Order.class);
-         * Join<Order,LineItem> lineItems =
-         * order.join(order_.getList("lineItems", LineItem.class));
-         * q.where(cb.isEmpty(lineItems)); q.select(order);
-         * 
-         * assertEquivalence(q, jpql);
-         */
+    }
+    
+    @AllowFailure(message="as() not implemented")
+    public void testIsEmptyExpressionOnJoin() {
+        String jpql = "SELECT o FROM Order o WHERE o.lineItems IS EMPTY"; 
+        CriteriaQuery q = cb.create(); 
+        Root<Order> o = q.from(Order.class);
+        ListJoin<Order,LineItem> lineItems =
+        o.join(order_.getList("lineItems", LineItem.class));
+        q.where(cb.isEmpty(lineItems.as(List.class))); 
+        q.select(o);
+        assertEquivalence(q, jpql);
     }
 
-    @AllowFailure
-    public void testExpressions() {
+    public void testFunctionalExpressionInProjection() {
         String jpql = "SELECT o.quantity, o.totalCost*1.08 AS taxedCost, "
                 + "a.zipCode FROM Customer c JOIN c.orders o JOIN c.address a "
                 + "WHERE a.state = 'CA' AND a.county = 'Santa Clara'";
+        
         CriteriaQuery q = cb.create();
         Root<Customer> cust = q.from(Customer.class);
         Join<Customer, Order> order = cust.join(customer_.getSet("orders",
@@ -226,17 +236,22 @@
                 address.get(address_.getAttribute("zipCode", String.class)));
 
         assertEquivalence(q, jpql);
-
-        jpql = "SELECT TYPE(e) FROM Employee e WHERE TYPE(e) <> Exempt";
-        q = cb.create();
+    }
+    
+    public void testTypeExpression() {
+        String jpql = "SELECT TYPE(e) FROM Employee e WHERE TYPE(e) <> Exempt";
+        CriteriaQuery q = cb.create();
         Root<Employee> emp = q.from(Employee.class);
         q.select(emp.type()).where(cb.notEqual(emp.type(), Exempt.class));
 
         assertEquivalence(q, jpql);
-
-        jpql = "SELECT w.name FROM Course c JOIN c.studentWaitList w "
+    }
+    
+    public void testJoinAndIndexExpression() {
+       String jpql = "SELECT w.name FROM Course c JOIN c.studentWaitList w "
                 + "WHERE c.name = 'Calculus' AND INDEX(w) = 0";
-        q = cb.create();
+        
+       CriteriaQuery q = cb.create();
         Root<Course> course = q.from(Course.class);
         ListJoin<Course, Student> w = course.join(course_.getList(
                 "studentWaitList", Student.class));
@@ -247,11 +262,13 @@
                 w.get(student_.getAttribute("name", String.class)));
 
         assertEquivalence(q, jpql);
-
-        jpql = "SELECT SUM(i.price) " 
+    }
+    
+    public void testAggregateExpressionInProjection() {
+        String jpql = "SELECT SUM(i.price) " 
              + "FROM Order o JOIN o.lineItems i JOIN o.customer c "
              + "WHERE c.lastName = 'Smith' AND c.firstName = 'John'";
-        q = cb.create();
+        CriteriaQuery q = cb.create();
         Root<Order> o = q.from(Order.class);
         Join<Order, LineItem> i = o.join(order_.getList("lineItems",
                 LineItem.class));
@@ -264,24 +281,34 @@
         q.select(cb.sum(i.get(lineItem_.getAttribute("price", Double.class))));
 
         assertEquivalence(q, jpql);
-        /*
-         * jpql = "SELECT SIZE(d.employees) FROM Department d " +
-         * "WHERE d.name = 'Sales'"; q = cb.create(); Root<Department> d =
-         * q.from(Department.class);
-         * q.where(cb.equal(d.get(department_.getAttribute("name",
-         * String.class)), "Sales"));
-         * q.select(cb.size(d.get(department_.getSet("employees",
-         * Employee.class))));
-         * 
-         * assertEquivalence(q, jpql);
-         */
-        jpql = "SELECT e.name, CASE WHEN e.rating = 1 THEN e.salary * 1.1 "
+    }
+    
+    public void testSizeExpressionInProjection() {
+        String jpql = "SELECT SIZE(d.employees) FROM Department d " 
+         + "WHERE d.name = 'Sales'"; 
+        
+        CriteriaQuery q = cb.create(); 
+        Root<Department> d = q.from(Department.class);
+        q.where(cb.equal(
+                d.get(department_.getAttribute("name", String.class)), 
+                "Sales"));
+        Set<Department, Employee> employees = 
+            department_.getDeclaredSet("employees", Employee.class);
+        q.select(cb.size(d.get(employees)));
+        
+        assertEquivalence(q, jpql);
+        
+    }
+    
+    public void testCaseExpression() {
+        String jpql = "SELECT e.name, "
+             + "CASE WHEN e.rating = 1 THEN e.salary * 1.1 "
              + "WHEN e.rating = 2 THEN e.salary * 1.2 ELSE e.salary * 1.01 END "
              + "FROM Employee e WHERE e.department.name = 'Engineering'";
-        q = cb.create();
+        
+        CriteriaQuery q = cb.create();
         Root<Employee> e = q.from(Employee.class);
-        q
-                .where(cb.equal(e.get(
+        q.where(cb.equal(e.get(
                         employee_.getAttribute("department", Department.class))
                         .get(department_.getAttribute("name", String.class)),
                         "Engineering"));
@@ -301,32 +328,30 @@
         assertEquivalence(q, jpql);
     }
 
-    /*
-     * @AllowFailure public void testLiterals() { String jpql =
-     * "SELECT p FROM Person p where 'Joe' MEMBER OF " + "p.nickNames";
-     * CriteriaQuery q = cb.create(); Root<Person> p = q.from(Person.class);
-     * q.select(p).where(cb.isMember(cb.literal("Joe"), p.get(person_.
-     * getSet("nickNames", String.class))));
-     * 
-     * assertEquivalence(q, jpql); }
-     */
+    public void testMemberOfExpression() {
+      String jpql = "SELECT p FROM Person p where 'Joe' MEMBER OF p.nickNames";
+     
+      CriteriaQuery q = cb.create(); 
+      Root<Person> p = q.from(Person.class);
+      q.select(p).where(cb.isMember(cb.literal("Joe"), 
+             p.get(person_.getDeclaredSet("nickNames", String.class))));
+     
+       assertEquivalence(q, jpql); 
+     }
 
-    @AllowFailure
     public void testParameters() {
         String jpql = "SELECT c FROM Customer c Where c.status = :stat";
         CriteriaQuery q = cb.create();
         Root<Customer> c = q.from(Customer.class);
-        Parameter<Integer> param = cb.parameter(Integer.class);
-        q.select(c).where(
-                cb.equal(
-                        c.get(customer_.getAttribute("status", Integer.class)),
-                        param));
+        Parameter<Integer> param = cb.parameter(Integer.class, "stat");
+        q.select(c).where(cb.equal(
+            c.get(customer_.getAttribute("status", Integer.class)), param));
 
         assertEquivalence(q, jpql, new String[] { "stat" }, new Object[] { 1 });
     }
 
-    @AllowFailure
-    public void testSelectList() {
+    @AllowFailure(message="Generates invalid SQL")
+    public void testKeyExpressionInSelectList() {
         String jpql = "SELECT v.location.street, KEY(i).title, VALUE(i) FROM "
                 + "VideoStore v JOIN v.videoInventory i "
                 + "WHERE v.location.zipCode = " + "'94301' AND VALUE(i) > 0";
@@ -343,25 +368,27 @@
                 .get(movie_.getAttribute("title", String.class)), inv.value());
 
         assertEquivalence(q, jpql);
-
-        jpql = "SELECT NEW CustomerDetails(c.id, c.status, o.quantity) FROM "
-                + "Customer c JOIN c.orders o WHERE o.quantity > 100";
-        q = cb.create();
+    }
+    
+    public void testConstructorInSelectList() {
+        String jpql = "SELECT NEW CustomerDetails(c.id, c.status, o.quantity) "
+                    + "FROM Customer c JOIN c.orders o WHERE o.quantity > 100";
+        
+        CriteriaQuery q = cb.create();
         Root<Customer> c = q.from(Customer.class);
-        Join<Customer, Order> o = c.join(customer_
-                .getSet("orders", Order.class));
+        SetJoin<Customer, Order> o = c.join(
+                customer_.getSet("orders", Order.class));
         q.where(cb.gt(o.get(order_.getAttribute("quantity", Integer.class)),
                 100));
-        q.select(cb.select(CustomerDetails.class, c.get(customer_.getAttribute(
-                "id", Integer.class)), c.get(customer_.getAttribute("status",
-                Integer.class)), o.get(order_.getAttribute("quantity",
-                Integer.class))));
+        q.select(cb.select(CustomerDetails.class, 
+                c.get(customer_.getAttribute("id", Long.class)), 
+                c.get(customer_.getAttribute("status", Integer.class)), 
+                o.get(order_.getAttribute("quantity",  Integer.class))));
 
         assertEquivalence(q, jpql);
     }
 
-    @AllowFailure
-    public void testSubqueries() {
+    public void testUncorrelatedSubqueryWithAggregateProjection() {
         String jpql = "SELECT goodCustomer FROM Customer goodCustomer WHERE "
                 + "goodCustomer.balanceOwed < (SELECT AVG(c.balanceOwed) FROM "
                 + "Customer c)";
@@ -375,42 +402,49 @@
         q.select(goodCustomer);
 
         assertEquivalence(q, jpql);
-
-        jpql = "SELECT DISTINCT emp FROM Employee emp WHERE EXISTS ("
+    }
+    
+    public void testSubqueryWithExistsClause() {
+        String jpql = "SELECT DISTINCT emp FROM Employee emp WHERE EXISTS ("
                 + "SELECT spouseEmp FROM Employee spouseEmp WHERE spouseEmp = "
                 + "emp.spouse)";
-        q = cb.create();
+        CriteriaQuery q = cb.create();
         Root<Employee> emp = q.from(Employee.class);
-        Subquery<Employee> sq1 = q.subquery(Employee.class);
-        Root<Employee> spouseEmp = sq1.from(Employee.class);
-        sq1.select(spouseEmp);
-        sq1.where(cb.equal(spouseEmp, emp.get(employee_.getAttribute("spouse",
+        Subquery<Employee> sq = q.subquery(Employee.class);
+        Root<Employee> spouseEmp = sq.from(Employee.class);
+        sq.select(spouseEmp);
+        sq.where(cb.equal(spouseEmp, emp.get(employee_.getAttribute("spouse",
                 Employee.class))));
         q.where(cb.exists(sq));
         q.select(emp).distinct(true);
 
         assertEquivalence(q, jpql);
+    }
 
-        jpql = "SELECT emp FROM Employee emp WHERE emp.salary > ALL ("
+    public void testSubqueryWithAllClause() {
+        String jpql = "SELECT emp FROM Employee emp WHERE emp.salary > ALL ("
                 + "SELECT m.salary FROM Manager m WHERE m.department ="
                 + " emp.department)";
-        q = cb.create();
-        Root<Employee> emp1 = q.from(Employee.class);
-        q.select(emp1);
-        Subquery<BigDecimal> sq2 = q.subquery(BigDecimal.class);
-        Root<Manager> m = sq2.from(Manager.class);
-        sq2.select(m.get(manager_.getAttribute("salary", BigDecimal.class)));
-        sq2.where(cb.equal(m.get(manager_.getAttribute("department",
-                Department.class)), emp1.get(employee_.getAttribute(
+        
+        CriteriaQuery q = cb.create();
+        Root<Employee> emp = q.from(Employee.class);
+        q.select(emp);
+        Subquery<BigDecimal> sq = q.subquery(BigDecimal.class);
+        Root<Manager> m = sq.from(Manager.class);
+        sq.select(m.get(manager_.getAttribute("salary", BigDecimal.class)));
+        sq.where(cb.equal(m.get(manager_.getAttribute("department",
+                Department.class)), emp.get(employee_.getAttribute(
                 "department", Department.class))));
         q.where(cb.gt(emp.get(employee_.getAttribute("salary", Long.class)), cb
                 .all(sq)));
 
         assertEquivalence(q, jpql);
-
-        jpql = "SELECT c FROM Customer c WHERE "
+    }
+    
+    public void testCorrelatedSubqueryWithCount() {
+        String jpql = "SELECT c FROM Customer c WHERE "
                 + "(SELECT COUNT(o) FROM c.orders o) > 10";
-        q = cb.create();
+        CriteriaQuery q = cb.create();
         Root<Customer> c1 = q.from(Customer.class);
         q.select(c1);
         Subquery<Long> sq3 = q.subquery(Long.class);
@@ -420,43 +454,50 @@
         q.where(cb.gt(sq3.select(cb.count(o)), 10));
 
         assertEquivalence(q, jpql);
-
-        jpql = "SELECT o FROM Order o WHERE 10000 < ALL ("
+    }
+    
+    public void testCorrelatedSubqueryWithJoin() {
+        String jpql = "SELECT o FROM Order o WHERE 10000 < ALL ("
                 + "SELECT a.balance FROM o.customer c JOIN c.accounts a)";
-        q = cb.create();
-        Root<Order> o1 = q.from(Order.class);
-        q.select(o1);
-        Subquery<Integer> sq4 = q.subquery(Integer.class);
-        Root<Order> o2 = sq4.correlate(o1);
-        Join<Order, Customer> c3 = o2.join(order_.getAttribute("customer",
+        
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o);
+        Subquery<Integer> sq = q.subquery(Integer.class);
+        Root<Order> o2 = sq.correlate(o);
+        Join<Order, Customer> c = o2.join(order_.getAttribute("customer",
                 Customer.class));
-        Join<Customer, Account> a = c3.join(customer_.getList("accounts",
+        Join<Customer, Account> a = c.join(customer_.getList("accounts",
                 Account.class));
-        sq4.select(a.get(account_.getAttribute("balance", Integer.class)));
-        q.where(cb.lt(cb.literal(10000), cb.all(sq4)));
+        sq.select(a.get(account_.getAttribute("balance", Integer.class)));
+        q.where(cb.lt(cb.literal(10000), cb.all(sq)));
 
         assertEquivalence(q, jpql);
-
-        jpql = "SELECT o FROM Order o JOIN o.customer c WHERE 10000 < "
-                + "ALL (SELECT a.balance FROM c.accounts a)";
-        q = cb.create();
-        Root<Order> o3 = q.from(Order.class);
-        q.select(o3);
-        Join<Order, Customer> c4 = o3.join(Order_.customer);
-        Subquery<Integer> sq5 = q.subquery(Integer.class);
-        Join<Order, Customer> c5 = sq5.correlate(c4);
-        Join<Customer, Account> a2 = c5.join(customer_.getList("accounts",
+    }
+    
+    @AllowFailure(message="Root of the subquery._delegate not set")
+    public void testCorrelatedSubqueryWithAllClause() {
+        String jpql = "SELECT o FROM Order o JOIN o.customer c "
+                    + "WHERE 10000 < ALL (SELECT a.balance FROM c.accounts a)";
+        
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o);
+        Join<Order, Customer> c = o.join(Order_.customer);
+        Subquery<Integer> sq = q.subquery(Integer.class);
+        Join<Order, Customer> csq = sq.correlate(c);
+        Join<Customer, Account> a = csq.join(customer_.getList("accounts",
                 Account.class));
-        sq5.select(a.get(account_.getAttribute("balance", Integer.class)));
-        q.where(cb.lt(cb.literal(10000), cb.all(sq5)));
+        sq.select(a.get(account_.getAttribute("balance", Integer.class)));
+        q.where(cb.lt(cb.literal(10000), cb.all(sq)));
 
         assertEquivalence(q, jpql);
     }
 
-    @AllowFailure
     public void testGroupByAndHaving() {
         String jpql = "SELECT c.status, AVG(c.filledOrderCount), COUNT(c) FROM "
                 + "Customer c GROUP BY c.status HAVING c.status IN (1, 2)";
+        
         CriteriaQuery q = cb.create();
         Root<Customer> c = q.from(Customer.class);
         q.groupBy(c.get(customer_.getAttribute("status", Integer.class)));
@@ -469,54 +510,65 @@
         assertEquivalence(q, jpql);
     }
 
-    @AllowFailure
-    public void testOrdering() {
-        String jpql = "SELECT o FROM Customer c JOIN c.orders o "
-                + "JOIN c.address a WHERE a.state = 'CA' "
-                + "ORDER BY o.quantity DESC, o.totalCost";
+    public void testOrderingByExpressionNotIncludedInSelection() {
+        String jpql = "SELECT o FROM Customer c " 
+                    + "JOIN c.orders o JOIN c.address a "
+                    + "WHERE a.state = 'CA' "
+                    + "ORDER BY o.quantity DESC, o.totalCost";
+        
         CriteriaQuery q = cb.create();
         Root<Customer> c = q.from(Customer.class);
-        Join<Customer, Order> o = c.join(customer_
-                .getSet("orders", Order.class));
+        SetJoin<Customer, Order> o = c.join(customer_.getSet("orders", 
+                Order.class));
         Join<Customer, Address> a = c.join(customer_.getAttribute("address",
                 Address.class));
-        q.where(cb.equal(a.get(address_.getAttribute("state", String.class)),
+        q.where(cb.equal(
+                a.get(address_.getAttribute("state", String.class)),
                 "CA"));
-        q.orderBy(cb
-                .desc(o.get(order_.getAttribute("quantity", Integer.class))),
-                cb.asc(o.get(order_.getAttribute("totalCost", Double.class))));
+        q.orderBy(
+          cb.desc(o.get(order_.getAttribute("quantity", Integer.class))),
+          cb.asc(o.get(order_.getAttribute("totalCost", Double.class))));
         q.select(o);
 
         assertEquivalence(q, jpql);
-
-        jpql = "SELECT o.quantity, a.zipCode FROM Customer c JOIN c.orders "
-                + "JOIN c.address a WHERE a.state = 'CA' ORDER BY o.quantity, "
-                + "a.zipCode";
-        q = cb.create();
-        Root<Customer> c1 = q.from(Customer.class);
-        Join<Customer, Order> o1 = c1.join(customer_.getSet("orders",
+    }
+    
+    public void testOrderingByExpressionIncludedInSelection() {
+        String jpql = "SELECT o.quantity, a.zipCode FROM Customer c "
+                    + "JOIN c.orders o JOIN c.address a " 
+                    + "WHERE a.state = 'CA' "
+                    + "ORDER BY o.quantity, a.zipCode";
+        
+        CriteriaQuery q = cb.create();
+        Root<Customer> c = q.from(Customer.class);
+        Join<Customer, Order> o = c.join(customer_.getSet("orders",
                 Order.class));
-        Join<Customer, Address> a1 = c1.join(customer_.getAttribute("address",
+        Join<Customer, Address> a = c.join(customer_.getAttribute("address",
                 Address.class));
-        q.where(cb.equal(a1.get(address_.getAttribute("state", String.class)),
+        q.where(cb.equal(
+                a.get(address_.getAttribute("state", String.class)),
                 "CA"));
         q.orderBy(cb
-                .asc(o1.get(order_.getAttribute("quantity", Integer.class))),
-                cb.asc(a1.get(address_.getAttribute("zipCode", String.class))));
-        q.select(o1.get(order_.getAttribute("quantity", Integer.class)), a1
-                .get(address_.getAttribute("zipCode", String.class)));
+                .asc(o.get(order_.getAttribute("quantity", Integer.class))),
+                cb.asc(a.get(address_.getAttribute("zipCode", String.class))));
+        q.select(o.get(order_.getAttribute("quantity", Integer.class)), 
+                a.get(address_.getAttribute("zipCode", String.class)));
 
         assertEquivalence(q, jpql);
-
-        jpql = "SELECT o.quantity, o.cost * 1.08 AS taxedCost, a.zipCode "
+    }
+    
+    public void testOrderingWithNumericalExpressionInSelection() {
+        String jpql = "SELECT o.quantity, o.totalCost * 1.08 AS taxedCost, "
+                + "a.zipCode "
                 + "FROM Customer c JOIN c.orders o JOIN c.address a "
                 + "WHERE a.state = 'CA' AND a.county = 'Santa Clara' "
                 + "ORDER BY o.quantity, taxedCost, a.zipCode";
-        q = cb.create();
-        Root<Customer> c2 = q.from(Customer.class);
-        Join<Customer, Order> o2 = c2.join(customer_.getSet("orders",
+        
+        CriteriaQuery q = cb.create();
+        Root<Customer> c = q.from(Customer.class);
+        Join<Customer, Order> o = c.join(customer_.getSet("orders",
                 Order.class));
-        Join<Customer, Address> a2 = c2.join(customer_.getAttribute("address",
+        Join<Customer, Address> a = c.join(customer_.getAttribute("address",
                 Address.class));
         q.where(cb.equal(a.get(address_.getAttribute("state", String.class)),
                 "CA"), cb.equal(a.get(address_.getAttribute("county",
@@ -530,7 +582,6 @@
                 .prod(o.get(order_.getAttribute("totalCost", Double.class)),
                         1.08), a.get(address_.getAttribute("zipCode",
                 String.class)));
-
         assertEquivalence(q, jpql);
     }
 }

Added: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java
URL: http://svn.apache.org/viewvc/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java?rev=785341&view=auto
==============================================================================
--- openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java (added)
+++ openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java Tue Jun 16 18:56:57 2009
@@ -0,0 +1,604 @@
+package org.apache.openjpa.persistence.criteria;
+
+import java.sql.Timestamp;
+
+import javax.persistence.criteria.CriteriaQuery;
+import javax.persistence.criteria.Expression;
+import javax.persistence.criteria.Join;
+import javax.persistence.criteria.JoinType;
+import javax.persistence.criteria.ListJoin;
+import javax.persistence.criteria.Path;
+import javax.persistence.criteria.Root;
+import javax.persistence.criteria.SetJoin;
+import javax.persistence.criteria.Subquery;
+import javax.persistence.criteria.QueryBuilder.Coalesce;
+
+import org.apache.openjpa.persistence.test.AllowFailure;
+
+public class TestSubqueries extends CriteriaTest {
+    public void testExist() {
+        String query = "SELECT DISTINCT o.name FROM CompUser o WHERE EXISTS"
+                + " (SELECT c FROM Address c WHERE c = o.address )";
+
+        CriteriaQuery q = cb.create();
+        Root<CompUser> o = q.from(CompUser.class);
+        Subquery<Address> sq = q.subquery(Address.class);
+        sq.correlate(o);
+        Root<Address> c = sq.from(Address.class);
+        sq.select(c);
+        sq.where(cb.equal(c, o.get(CompUser_.address)));
+        q.where(cb.exists(sq));
+        q.select(o.get(CompUser_.name)).distinct(true);
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testNotExist() {
+        String query = "SELECT DISTINCT o.name FROM CompUser o WHERE NOT EXISTS"
+                + " (SELECT s FROM CompUser s WHERE s.address.country = "
+                + "o.address.country)";
+
+        CriteriaQuery q = cb.create();
+        Root<CompUser> o = q.from(CompUser.class);
+        Subquery<CompUser> sq = q.subquery(CompUser.class);
+        sq.correlate(o);
+        Root<CompUser> s = sq.from(CompUser.class);
+        sq.select(s);
+        sq.where(cb.equal(s.get(CompUser_.address).get(Address_.country), o
+                .get(CompUser_.address).get(Address_.country)));
+        q.where(cb.exists(sq).negate());
+        q.select(o.get(CompUser_.name)).distinct(true);
+
+        assertEquivalence(q, query);
+    }
+
+    public void testAny() {
+        String query = "SELECT o.name FROM CompUser o " 
+                     + "WHERE o.address.zipCode = "
+                     + " ANY (SELECT s.computerName " 
+                     + " FROM CompUser s WHERE s.address.country IS NOT NULL)";
+
+        CriteriaQuery q = cb.create();
+        Root<CompUser> o = q.from(CompUser.class);
+        q.select(o.get(CompUser_.name));
+        Subquery<String> sq = q.subquery(String.class);
+        Root<CompUser> s = sq.from(CompUser.class);
+        sq.select(s.get(CompUser_.computerName));
+        sq.where(cb.notEqual(s.get(CompUser_.address).get(Address_.country),
+                null));
+        q.where(cb.equal(o.get(CompUser_.address).get(Address_.zipCode), cb
+                .any(sq)));
+
+        assertEquivalence(q, query);
+    }
+
+    public void testSubquery01() {
+        String query = "select o1.id from Order o1 where o1.id in "
+                + " (select distinct o.id from LineItem i, Order o"
+                + " where i.quantity > 10 and o.count > 1000 and i.id = o.id)";
+        
+        CriteriaQuery q = cb.create();
+        Root<Order> o1 = q.from(Order.class);
+        q.select(o1.get(Order_.id));
+
+        Subquery<Integer> sq = q.subquery(Integer.class);
+        Root<LineItem> i = sq.from(LineItem.class);
+        Join<LineItem, Order> o = i.join(LineItem_.order);
+        sq.where(cb.and(cb.and(cb.gt(i.get(LineItem_.quantity), 10), cb.gt(o
+                .get(Order_.count), 1000)), cb.equal(i.get(LineItem_.id), o
+                .get(Order_.id))));
+        sq.select(o.get(Order_.id)).distinct(true);
+        q.where(cb.in(o1.get(Order_.id)).value(
+                sq.select(o.get(Order_.id)).distinct(true)));
+        
+        assertEquivalence(q, query);
+    }
+
+    public void testSubquery02() {
+        String query = "select o.id from Order o where o.customer.balanceOwed ="
+                + " (select max(o2.customer.balanceOwed) from Order o2"
+                + " where o.customer.id = o2.customer.id)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.id));
+        Subquery<Integer> sq = q.subquery(Integer.class);
+        Root<Order> o2 = sq.from(Order.class);
+        sq.where(cb.equal(o.get(Order_.customer).get(Customer_.id), o2.get(
+                Order_.customer).get(Customer_.id)));
+        q.where(cb.equal(o.get(Order_.customer).get(Customer_.balanceOwed), sq
+                .select(cb.max(o2.get(Order_.customer).get(
+                        Customer_.balanceOwed)))));
+        
+        assertEquivalence(q, query);
+    }
+
+    public void testSubquery03() {
+        String query = "select o from Order o where o.customer.balanceOwed ="
+                + " (select max(o2.customer.balanceOwed) from Order o2"
+                + " where o.customer.id = o2.customer.id)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o);
+        Subquery<Integer> sq = q.subquery(Integer.class);
+        Root<Order> o2 = sq.from(Order.class);
+        sq.where(cb.equal(o.get(Order_.customer).get(Customer_.id), o2.get(
+                Order_.customer).get(Customer_.id)));
+        q.where(cb.equal(o.get(Order_.customer).get(Customer_.balanceOwed), sq
+                .select(cb.max(o2.get(Order_.customer).get(
+                        Customer_.balanceOwed)))));
+        
+        assertEquivalence(q, query);
+    }
+
+    public void testSubquery04() {
+        String query = "select o.id from Order o where o.quantity >"
+                + " (select count(i) from o.lineItems i)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.id));
+        Subquery<Long> sq = q.subquery(Long.class);
+        Root<Order> osq = sq.correlate(o);
+        Join<Order, LineItem> i = osq.join(Order_.lineItems);
+        q.where(cb.gt(o.get(Order_.quantity), sq.select(cb.count(i))));
+        assertEquivalence(q, query);
+    }
+
+    public void testSubquery05() {
+        String query = "select o.id from Order o where o.quantity >"
+                + " (select count(o.quantity) from Order o)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.id));
+        Subquery<Long> sq = q.subquery(Long.class);
+        Root<Order> o2 = sq.from(Order.class);
+        q.where(cb.gt(o.get(Order_.quantity), sq.select(cb.count(o2
+                .get(Order_.quantity)))));
+        
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery06() {
+        String query = "select o.id from Order o where o.quantity >"
+                + " (select count(o.id) from Order o)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.id));
+        Subquery<Long> sq = q.subquery(Long.class);
+        Root<Order> o2 = sq.from(Order.class);
+        q.where(cb.gt(o.get(Order_.quantity), sq.select(cb.count(o2
+                .get(Order_.id)))));
+        
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery07() {
+        String query = "select o.id from Order o where o.quantity >"
+                + " (select avg(o.quantity) from Order o)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.id));
+        Subquery<Double> sq = q.subquery(Double.class);
+        Root<Order> o2 = sq.from(Order.class);
+        q.where(cb.gt(o.get(Order_.quantity), sq.select(cb.avg(o2
+                .get(Order_.quantity)))));
+        
+        assertEquivalence(q, query);
+    }
+
+    @AllowFailure(message="JPQL generates invalid SQL")
+    public void testSubquery08() {
+        String query = "select c.name from Customer c "
+                + "where exists (select o from c.orders o where o.id = 1) "
+                + "or exists (select o from c.orders o where o.id = 2)";
+        
+        CriteriaQuery q = cb.create();
+        Root<Customer> c = q.from(Customer.class);
+        q.select(c.get(Customer_.name));
+        Subquery<Order> sq1 = q.subquery(Order.class);
+        Root<Customer> c1 = sq1.correlate(c);
+        SetJoin<Customer, Order> o1 = c1.join(Customer_.orders);
+        sq1.where(cb.equal(o1.get(Order_.id), 1)).select(o1);
+
+        Subquery<Order> sq2 = q.subquery(Order.class);
+        Root<Customer> c2 = sq2.correlate(c);
+        SetJoin<Customer, Order> o2 = c2.join(Customer_.orders);
+        sq2.where(cb.equal(o2.get(Order_.id), 2)).select(o2);
+
+        q.where(cb.or(cb.exists(sq1), cb.exists(sq2)));
+        
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery09() {
+        String query = "select c.name from Customer c, in(c.orders) o "
+                + "where o.quantity between "
+                + "(select max(o.quantity) from Order o) and "
+                + "(select avg(o.quantity) from Order o) ";
+        CriteriaQuery q = cb.create();
+        Root<Customer> c = q.from(Customer.class);
+        q.select(c.get(Customer_.name));
+
+        Subquery<Integer> sq1 = q.subquery(Integer.class);
+        Root<Order> o1 = sq1.from(Order.class);
+        sq1.select(cb.max(o1.get(Order_.quantity)));
+
+        Subquery<Double> sq2 = q.subquery(Double.class);
+        Root<Order> o2 = sq2.from(Order.class);
+        sq2.select(cb.avg(o2.get(Order_.quantity)));
+
+        SetJoin<Customer, Order> o = c.join(Customer_.orders);
+        // not sure how to do call between of integer(quantity)
+        // between integer (max quantity) and double (avg quantity)
+        // q.where(cb.between(o.get(Order_.quantity), sq1, sq2));
+        
+        assertEquivalence(q, query);
+    }
+
+    @AllowFailure(message="")
+    public void testSubquery10() {
+        String query = "select o.id from Order o where o.quantity >"
+                + " (select sum(o2.quantity) from Customer c, " 
+                + "in(c.orders) o2) ";
+        
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.id));
+
+        Subquery<Integer> sq = q.subquery(Integer.class);
+        Root<Customer> c = sq.from(Customer.class);
+        SetJoin<Customer, Order> o2 = c.join(Customer_.orders);
+        sq.select(cb.sum(o2.get(Order_.quantity)));
+
+        q.where(cb.gt(o2.get(Order_.quantity), sq));
+        
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery11() {
+        String query = "select o.id from Order o where o.quantity between"
+                + " (select avg(o2.quantity) from Customer c, in(c.orders) o2)"
+                + " and (select min(o2.quantity) from Customer c, in(c.orders)"
+                + " o2)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.id));
+
+        Subquery<Double> sq1 = q.subquery(Double.class);
+        Root<Customer> c = sq1.from(Customer.class);
+        SetJoin<Customer, Order> o2 = c.join(Customer_.orders);
+        sq1.select(cb.avg(o2.get(Order_.quantity)));
+
+        Subquery<Integer> sq2 = q.subquery(Integer.class);
+        Root<Customer> c2 = sq2.from(Customer.class);
+        SetJoin<Customer, Order> o3 = c2.join(Customer_.orders);
+        sq2.select(cb.min(o3.get(Order_.quantity)));
+
+        // do not know how to call between for double and integer
+        // q.where(cb.between(o2.get(Order_.quantity), sq1, sq2));
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery12() {
+        String query =
+            "select o.id from Customer c, in(c.orders)o "
+                + "where o.quantity > (select sum(o2.quantity)"
+                + " from c.orders o2)";
+        CriteriaQuery q = cb.create();
+        Root<Customer> c = q.from(Customer.class);
+        SetJoin<Customer, Order> o = c.join(Customer_.orders);
+        q.select(o.get(Order_.id));
+
+        Subquery<Integer> sq = q.subquery(Integer.class);
+        Root<Customer> sqc = sq.correlate(c);
+        SetJoin<Customer, Order> o2 = sqc.join(Customer_.orders);
+        sq.select(cb.sum(o2.get(Order_.quantity)));
+        q.where(cb.gt(o.get(Order_.quantity), sq));
+        
+        assertEquivalence(q, query);
+    }
+
+    @AllowFailure
+    public void testSubquery13() {
+        String query = "select o1.id, c.name from Order o1, Customer c"
+                + " where o1.quantity = "
+                + " any(select o2.quantity from in(c.orders) o2)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o1 = q.from(Order.class);
+        Join<Order, Customer> c = o1.join(Order_.customer);
+        q.select(o1.get(Order_.id), c.get(Customer_.name));
+
+        Subquery<Integer> sq = q.subquery(Integer.class);
+        Join<Order, Customer> sqc = sq.correlate(c);
+        SetJoin<Customer, Order> o2 = sqc.join(Customer_.orders);
+        sq.select(o2.get(Order_.quantity));
+
+        q.where(cb.equal(o1.get(Order_.quantity), cb.any(sq)));
+        
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery14() {
+        String query = "SELECT p, m FROM Publisher p "
+            + "LEFT OUTER JOIN p.magazineCollection m "
+            + "WHERE m.id = (SELECT MAX(m2.id) FROM Magazine m2 "
+            + "WHERE m2.idPublisher.id = p.id AND m2.id = "
+            + "(SELECT MAX(m3.id) FROM Magazine m3 "
+            + "WHERE m3.idPublisher.id = p.id)) ";
+
+        CriteriaQuery q = cb.create();
+        Root<Publisher> p = q.from(Publisher.class);
+        Join<Publisher, Magazine> m = p.join(Publisher_.magazineCollection,
+            JoinType.LEFT);
+        q.select(p, m);
+
+        Subquery<Integer> sq = q.subquery(Integer.class);
+        Root<Magazine> m2 = sq.from(Magazine.class);
+        q.where(cb.equal(
+                m.get(Magazine_.id), 
+                sq.select(cb.max(m2.get(Magazine_.id)))));
+
+        Subquery<Integer> sq2 = q.subquery(Integer.class);
+        Root<Magazine> m3 = sq2.from(Magazine.class);
+        
+        sq2.where(cb.equal(
+                m3.get(Magazine_.idPublisher).get(Publisher_.id), 
+                p.get(Publisher_.id)));
+        
+        sq.where(cb.and(cb.equal(
+                   m2.get(Magazine_.idPublisher).get(Publisher_.id), 
+                   p.get(Publisher_.id)), 
+                cb.equal(
+                   m2.get(Magazine_.id), 
+                   sq2.select(cb.max(m3.get(Magazine_.id))))));
+        
+        assertEquivalence(q, query);
+    }
+
+    // outstanding problem subqueries:
+    // "select o from Order o where o.amount > (select count(o) from Order o)",
+    // "select o from Order o where o.amount > (select count(o2) from Order o2)
+    //",
+    // "select c from Customer c left join c.orders p where not exists"
+    // + " (select o2 from c.orders o2 where o2 = o",
+
+    // not sure how to write CriteriaQuery for
+    // Subquery.select(SimpleCase/GeneralCase)
+    
+    public void testSubquery15() {
+        String query = "select o.id from Order o where o.delivered =(select "
+                + "   CASE WHEN o2.quantity > 10 THEN true"
+                + "     WHEN o2.quantity = 10 THEN false "
+                + "     ELSE false END from Order o2"
+                + " where o.customer.id = o2.customer.id)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.id));
+
+        Subquery<Boolean> sq = q.subquery(Boolean.class);
+        Root<Order> o2 = sq.from(Order.class);
+        sq.where(cb.equal(o.get(Order_.customer).get(Customer_.id), o2.get(
+                Order_.customer).get(Customer_.id)));
+
+        q.where(cb.equal(o.get(Order_.delivered),
+        // sq.select(
+                cb.selectCase().when(cb.gt(o2.get(Order_.quantity), 10), true)
+                        .when(cb.equal(o2.get(Order_.quantity), 10), false)
+                        .otherwise(false)
+        // )
+                ));
+        
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery16() {
+        String query = "select o1.id from Order o1 where o1.quantity > "
+                + " (select o.quantity*2 from LineItem i, Order o"
+                + " where i.quantity > 10 and o.quantity > 1000 and i.id = " +
+                        "o.id)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o1 = q.from(Order.class);
+        q.select(o1.get(Order_.id));
+
+        Subquery<Integer> sq = q.subquery(Integer.class);
+        Root<LineItem> i = sq.from(LineItem.class);
+        Join<LineItem, Order> o = i.join(LineItem_.order);
+        sq.where(cb.and(cb.and(cb.gt(i.get(LineItem_.quantity), 10), cb.gt(o
+                .get(Order_.quantity), 1000)), cb.equal(i.get(LineItem_.id), o
+                .get(Order_.id))));
+
+        q.where(cb.gt(o1.get(Order_.quantity), sq.select(cb.prod(o
+                .get(Order_.quantity), 2))));
+
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery17() {
+        String query = "select o.id from Order o where o.customer.name ="
+                + " (select substring(o2.customer.name, 3) from Order o2"
+                + " where o.customer.id = o2.customer.id)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.customer).get(Customer_.name));
+
+        Subquery<String> sq = q.subquery(String.class);
+        Root<Order> o2 = sq.from(Order.class);
+        sq.where(cb.equal(o.get(Order_.customer).get(Customer_.id), o2.get(
+                Order_.customer).get(Customer_.id)));
+
+        q.where(cb.equal(o.get(Order_.customer).get(Customer_.name), sq
+                .select(cb.substring(o2.get(Order_.customer)
+                        .get(Customer_.name), 3))));
+
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery18() {
+        String query = "select o.id from Order o where o.orderTs >"
+                + " (select CURRENT_TIMESTAMP from o.lineItems i)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.id));
+
+        Subquery<Timestamp> sq = q.subquery(Timestamp.class);
+        Root<Order> o2 = sq.correlate(o);
+        ListJoin<Order, LineItem> i = o2.join(Order_.lineItems);
+
+        // q.where(cb.gt(
+        // o.get(Order_.orderTs),
+        // sq.select(cb.currentTimestamp())));
+        
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery19() {
+        String query = "select o.id from Order o where o.quantity >"
+                + " (select SQRT(o.quantity) from Order o where o.delivered" +
+                        " = true)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.id));
+
+        Subquery<Double> sq = q.subquery(Double.class);
+        Root<Order> o2 = sq.from(Order.class);
+        sq.where(cb.equal(o2.get(Order_.delivered), true));
+
+        q.where(cb.gt(o.get(Order_.quantity), sq.select(cb.sqrt(o2
+                .get(Order_.quantity)))));
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery20() {
+        String query = "select o.id from Order o where o.customer.name in"
+                + " (select CONCAT(o.customer.name, 'XX') from Order o"
+                + " where o.quantity > 10)";
+        CriteriaQuery q = cb.create();
+        Root<Order> o = q.from(Order.class);
+        q.select(o.get(Order_.id));
+
+        Subquery<String> sq = q.subquery(String.class);
+        Root<Order> o2 = sq.from(Order.class);
+        sq.where(cb.gt(o2.get(Order_.quantity), 10));
+
+        q.where(cb.in(o.get(Order_.customer).get(Customer_.name)).value(
+                sq.select(cb.concat(
+                        o2.get(Order_.customer).get(Customer_.name), "XX"))));
+        assertEquivalence(q, query);
+    }
+
+    @AllowFailure
+    public void testSubquery21() {
+        String query = "select c from Customer c where c.creditRating ="
+                + " (select "
+                + "   CASE WHEN o2.quantity > 10 THEN "
+       + "org.apache.openjpa.persistence.criteria.Customer$CreditRating.POOR " 
+                + "WHEN o2.quantity = 10 THEN "
+       + "org.apache.openjpa.persistence.criteria.Customer$CreditRating.GOOD " 
+                + "     ELSE "
+   + "org.apache.openjpa.persistence.criteria.Customer$CreditRating.EXCELLENT " 
+                + "     END from Order o2"
+                + " where c.id = o2.customer.id)";
+        
+        CriteriaQuery q = cb.create();
+        Root<Customer> c = q.from(Customer.class);
+        q.select(c);
+
+        Subquery<String> sq = q.subquery(String.class);
+        Root<Order> o2 = sq.from(Order.class);
+        sq.where(cb.equal(
+            c.get(Customer_.id), 
+            o2.get(Order_.customer).get(Customer_.id)));
+
+        q.where(cb.equal(c.get(Customer_.creditRating), 
+          cb.selectCase()
+          .when(cb.gt(o2.get(Order_.quantity), 10), 
+                  Customer.CreditRating.POOR)
+          .when(cb.equal(o2.get(Order_.quantity), 10), 
+                  Customer.CreditRating.GOOD)
+          .otherwise(Customer.CreditRating.EXCELLENT)
+            ));
+        assertEquivalence(q, query);
+    }
+
+    // Coalesce for Enum type
+    @AllowFailure(message="Wrong JPQL due to enum oridinal/identifier " +
+    		"type mismatch")
+    public void testSubquery22() {
+        String query = "select c from Customer c "
+                + "where c.creditRating = (select COALESCE (c1.creditRating, "
+                + "org.apache.openjpa.persistence.criteria.Customer$" +
+                        "CreditRating.POOR) "
+                + "from Customer c1 where c1.name = 'Famzy') order by c.name " +
+                        "DESC";
+        
+        CriteriaQuery q = cb.create();
+        Root<Customer> c = q.from(Customer.class);
+        q.select(c);
+        q.orderBy(cb.desc(c.get(Customer_.name)));        
+
+        Subquery<Customer.CreditRating> sq =
+            q.subquery(Customer.CreditRating.class);
+        Root<Customer> c1 = sq.from(Customer.class);
+        sq.where(cb.equal(c1.get(Customer_.name), "Famzy"));
+        
+        Expression<Customer.CreditRating> coalesce = cb.coalesce(
+                c1.get(Customer_.creditRating), 
+                Customer.CreditRating.POOR);
+        sq.select(coalesce);
+        q.where(cb.equal(c.get(Customer_.creditRating),sq));
+        assertEquivalence(q, query);
+    }
+
+    
+    public void testSubquery23() {
+        String query =
+            "select c from Customer c "
+                + "where c.creditRating = (select NULLIF (c1.creditRating, "
+                + "org.apache.openjpa.persistence.criteria."
+                + "Customer$CreditRating.POOR) "
+                + "from Customer c1 where c1.name = 'Famzy') "
+                + "order by c.name DESC";
+        CriteriaQuery q = cb.create();
+        Root<Customer> c = q.from(Customer.class);
+        q.select(c);
+        q.orderBy(cb.desc(c.get(Customer_.name)));        
+
+        Subquery<Customer.CreditRating> sq =
+            q.subquery(Customer.CreditRating.class);
+        Root<Customer> c1 = sq.from(Customer.class);
+        sq.where(cb.equal(c1.get(Customer_.name), "Famzy"));
+        
+        q.where(cb.equal(c.get(Customer_.creditRating),
+            sq.select(cb.nullif(c1.get(Customer_.creditRating),
+                Customer.CreditRating.POOR))));    
+        assertEquivalence(q, query);
+    }
+
+    /**
+     * Verify a sub query can contain MAX and additional date comparisons
+     * without losing the correct alias information. This sort of query
+     * originally caused problems for DBDictionaries which used DATABASE syntax.
+     */
+    // Not sure how to do Cartesian join when Employee can not 
+    // navigate to Dependent
+    
+    public void testSubSelectMaxDateRange() {
+        String query = "SELECT e,d from Employee e, Dependent d "
+            + "WHERE e.empId = :empid "
+            + "AND d.id.empid = (SELECT MAX (e2.empId) FROM Employee e2) "
+            + "AND d.id.effDate > :minDate "
+            + "AND d.id.effDate < :maxDate ";
+    }
+
+
+}

Propchange: openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java
------------------------------------------------------------------------------
    svn:eol-style = native



Mime
View raw message