openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/TestSubqueries.java

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package org.apache.openjpa.persistence.criteria;

import java.sql.Timestamp;
import java.util.List;

import javax.persistence.Parameter;
import javax.persistence.Tuple;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.*;

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<String> q = cb.createQuery(String.class);
        Root<CompUser> o = q.from(CompUser.class);
        Subquery<Address> sq = q.subquery(Address.class);
        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<String> q = cb.createQuery(String.class);
        Root<CompUser> o = q.from(CompUser.class);
        Subquery<CompUser> sq = q.subquery(CompUser.class);
        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).not());
        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<String> q = cb.createQuery(String.class);
        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<Integer> q = cb.createQuery(Integer.class);
        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);
        Root<Order> o = sq.from(Order.class);
        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));

        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<Integer> q = cb.createQuery(Integer.class);
        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<Order> q = cb.createQuery(Order.class);
        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<Integer> q = cb.createQuery(Integer.class);
        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<Integer> q = cb.createQuery(Integer.class);
        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<Integer> q = cb.createQuery(Integer.class);
        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<Integer> q = cb.createQuery(Integer.class);
        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);
    }

    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<String> q = cb.createQuery(String.class);
        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<String> q = cb.createQuery(String.class);
        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);
        q.where(cb.between(o.get(Order_.quantity), sq1, sq2.as(Integer.class)));

        assertEquivalence(q, query);
    }

    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<Integer> q = cb.createQuery(Integer.class);
        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(o.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<Integer> q = cb.createQuery(Integer.class);
        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)));

        q.where(cb.between(o.get(Order_.quantity), sq1.as(Integer.class), 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<Integer> q = cb.createQuery(Integer.class);
        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);
    }

    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<Tuple> q = cb.createTupleQuery();
        Root<Order> o1 = q.from(Order.class);
        Root<Customer> c = q.from(Customer.class);
        q.multiselect(o1.get(Order_.id), c.get(Customer_.name));

        Subquery<Integer> sq = q.subquery(Integer.class);
        Root<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<Tuple> q = cb.createTupleQuery();
        Root<Publisher> p = q.from(Publisher.class);
        Join<Publisher, Magazine> m = p.join(Publisher_.magazineCollection,
            JoinType.LEFT);
        q.multiselect(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);
    }

    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<Integer> q = cb.createQuery(Integer.class);
        Root<Order> o = q.from(Order.class);
        q.select(o.get(Order_.id));

        Subquery<Object> sq = q.subquery(Object.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)));
        sq.select(
            cb.selectCase().when(cb.gt(o2.get(Order_.quantity), 10), true)
                .when(cb.equal(o2.get(Order_.quantity), 10), false)
                .otherwise(false)
        );

        q.where(cb.equal(o.get(Order_.delivered), sq));

        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<Integer> q = cb.createQuery(Integer.class);
        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);
        Root<Order> o = sq.from(Order.class);
        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<Integer> q = cb.createQuery(Integer.class);
        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.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<Integer> q = cb.createQuery(Integer.class);
        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).as(Long.class), sq.select(cb.currentTimestamp()).as(Long.class)));

        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<Integer> q = cb.createQuery(Integer.class);
        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<Integer> q = cb.createQuery(Integer.class);
        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);
    }

    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<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        q.select(c);

        Subquery<Object> sq = q.subquery(Object.class);
        Root<Order> o2 = sq.from(Order.class);
        sq.where(cb.equal(
            c.get(Customer_.id),
            o2.get(Order_.customer).get(Customer_.id)));
        Expression<Object> generalCase = 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);

        sq.select(generalCase);
        q.where(cb.equal(c.get(Customer_.creditRating), sq));
        assertEquivalence(q, query);
    }

    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<Customer> q = cb.createQuery(Customer.class);
        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<Customer> q = cb.createQuery(Customer.class);
        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);
    }

    public void testSubquery24() {
        em.getTransaction().begin();

        freshCustomerAndOrder();

        CriteriaQuery<Long> q = cb.createQuery(Long.class);
        Root<Customer> root = q.from(Customer.class);
        q.select(root.get(Customer_.accountNum));

        ParameterExpression<String> testParam = cb.parameter(String.class, "param1");

        Subquery<Customer> sq = q.subquery(Customer.class);
        Root<Order> sqRoot = sq.from(Order.class);
        sq.where(cb.and(
                cb.equal(cb.parameter(String.class, "param2"), sqRoot.get(Order_.customer).get(Customer_.lastName)),
                cb.equal(testParam, sqRoot.get(Order_.customer).get(Customer_.name))
                ));
        sq.select(sqRoot.get(Order_.customer));

        q.where(cb.and(
                cb.equal(testParam, root.get(Customer_.name)),
                cb.in(root).value(sq)
        ));

        // em.createQuery(q).getResultList();
        TypedQuery<Long> tq = em.createQuery(q);
        tq.setParameter("param1", "Capricorn");
        tq.setParameter("param2", "Doe");

        assertEquals(1, tq.getResultList().size());

        em.getTransaction().rollback();

        cleanCustomerAndOrder();
    }

    /**
     * Test 2 different ParameterExpression instances which both have the same name.
     * They should
     */
    public void testSubquery25() {
        em.getTransaction().begin();

        freshCustomerAndOrder();

        CriteriaQuery<Long> q = cb.createQuery(Long.class);
        Root<Customer> root = q.from(Customer.class);
        q.select(root.get(Customer_.accountNum));

        ParameterExpression<String> testParam = cb.parameter(String.class, "param1");
        ParameterExpression<String> testParam2 = cb.parameter(String.class, "param1");

        Subquery<Customer> sq = q.subquery(Customer.class);
        Root<Order> sqRoot = sq.from(Order.class);
        sq.where(cb.and(
                cb.equal(cb.parameter(String.class, "param2"), sqRoot.get(Order_.customer).get(Customer_.lastName)),
                cb.equal(testParam, sqRoot.get(Order_.customer).get(Customer_.name))
                ));
        sq.select(sqRoot.get(Order_.customer));

        q.where(cb.and(
                cb.equal(testParam2, root.get(Customer_.name)),
                cb.in(root).value(sq)
        ));

        // em.createQuery(q).getResultList();
        TypedQuery<Long> tq = em.createQuery(q);
        tq.setParameter("param1", "Capricorn");
        tq.setParameter("param2", "Doe");

        assertEquals(1, tq.getResultList().size());

        em.getTransaction().rollback();

        cleanCustomerAndOrder();
    }

    private void freshCustomerAndOrder() {
        cleanCustomerAndOrder();

        Customer c1 = new Customer();
        c1.setAccountNum(156);
        c1.setFirstName("John");
        c1.setLastName("Doe");
        c1.setName("Capricorn");
        em.persist(c1);

        Order o1 = new Order();
        o1.setCustomer(c1);
        em.persist(o1);
        o1 = new Order();
        o1.setCustomer(c1);
        em.persist(o1);

        em.flush();
    }

    private void cleanCustomerAndOrder() {
        boolean txActive = em.getTransaction().isActive();
        if (!txActive) {
            em.getTransaction().begin();
        }
        em.createQuery("delete from Order o where o.customer.name = 'Capricorn'").executeUpdate();
        em.createQuery("delete from Order o").executeUpdate();
        em.createQuery("delete from Customer c where c.name = 'Capricorn'").executeUpdate();

        em.flush();
        if (!txActive) {
            em.getTransaction().commit();
        }
    }

}