/*
 * Copyright (c) 1998, 2021 Oracle and/or its affiliates. All rights reserved.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0,
 * or the Eclipse Distribution License v. 1.0 which is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
 */

// Contributors:
//     Oracle - initial API and implementation from Oracle TopLink
package org.eclipse.persistence.testing.tests.jpa.jpql;

import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;

import org.junit.Assert;
import junit.framework.Test;
import junit.framework.TestSuite;

import org.eclipse.persistence.config.QueryHints;
import org.eclipse.persistence.expressions.Expression;
import org.eclipse.persistence.expressions.ExpressionBuilder;
import org.eclipse.persistence.jpa.JpaEntityManager;
import org.eclipse.persistence.internal.expressions.ConstantExpression;
import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.queries.ReadAllQuery;
import org.eclipse.persistence.queries.ReportQuery;
import org.eclipse.persistence.queries.ReportQueryResult;
import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;
import org.eclipse.persistence.testing.models.jpa.advanced.Address;
import org.eclipse.persistence.testing.models.jpa.advanced.AdvancedTableCreator;
import org.eclipse.persistence.testing.models.jpa.advanced.Employee;
import org.eclipse.persistence.testing.models.jpa.advanced.EmployeePopulator;
import org.eclipse.persistence.testing.models.jpa.advanced.EmploymentPeriod;
import org.eclipse.persistence.testing.models.jpa.advanced.PhoneNumber;
import org.eclipse.persistence.testing.models.jpa.relationships.Customer;
import org.eclipse.persistence.testing.models.jpa.relationships.CustomerDetails;
import org.eclipse.persistence.testing.models.jpa.relationships.Order;
import org.eclipse.persistence.testing.models.jpa.relationships.RelationshipsExamples;
import org.eclipse.persistence.testing.models.jpa.relationships.RelationshipsTableManager;
import org.eclipse.persistence.testing.models.jpa.relationships.SalesPerson;

public class JUnitJPQLExamplesTestSuite extends JUnitTestCase {
    static JUnitDomainObjectComparer comparer;
    static EmployeePopulator employeePopulator;

    public JUnitJPQLExamplesTestSuite() {
        super();
    }

    public JUnitJPQLExamplesTestSuite(String name) {
        super(name);
    }

    /**
     * This method is run at the end of EVERY test case method.
     */
    @Override
    public void tearDown() {
        clearCache();
    }

    /**
     * This suite contains all tests contained in this class.
     */
    public static Test suite() {
        TestSuite suite = new TestSuite();
        suite.setName("JUnitJPQLExamplesTestSuite");
        suite.addTest(new JUnitJPQLExamplesTestSuite("testSetup"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("findAllOrders"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("findEmployeesInOntario"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("findAllProvinceWithEmployees"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("findAllEmployeesWithPhoneNumbers"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("findAllEmployeesWithOutPhoneNumbers"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("findAllEmployeesWithCellPhones"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("findOrdersWithDifferentBilledCustomer"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("findEmployeeWithWorkPhone2258812"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("parameterTest"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("getOrderLargerThan"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("getSalesPersonForOrders"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("getOrderForCustomer"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testOuterJoin"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testExistsExpression"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testAllExpressions"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testCountInSubQuery"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testGroupByHavingExpression"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testGroupByHavingCount"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testConstructorQuery"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testSumExpression"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testAvgExpression"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testOrderByExpression"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testCountInSubQuery"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testOrderByExpressionWithSelect"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testDeleteExpression"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("testComplexDeleteExpression"));

        suite.addTest(new JUnitJPQLExamplesTestSuite("testCountExpression"));    //bug 5166658
        suite.addTest(new JUnitJPQLExamplesTestSuite("testUpdateExpression"));   //bug 5159164, 5159198

        //Bug5097278
        suite.addTest(new JUnitJPQLExamplesTestSuite("updateAllTest"));
        //Bug5040609
        suite.addTest(new JUnitJPQLExamplesTestSuite("namedQueryCloneTest"));
        //Bug4924639
        suite.addTest(new JUnitJPQLExamplesTestSuite("aggregateParameterTest"));
        // Bug 5090182
        suite.addTest(new JUnitJPQLExamplesTestSuite("testEJBQLQueryString"));
        suite.addTest(new JUnitJPQLExamplesTestSuite("updateEmbeddedFieldTest"));

        return suite;
    }

    /**
     * The setup is done as a test, both to record its failure, and to allow execution in the server.
     */
    public void testSetup() {
        clearCache();
        //get session to start setup
        DatabaseSession session = JUnitTestCase.getServerSession();

        //create a new EmployeePopulator
        EmployeePopulator employeePopulator = new EmployeePopulator();

        RelationshipsExamples relationshipExamples = new RelationshipsExamples();

        new AdvancedTableCreator().replaceTables(session);

        new RelationshipsTableManager().replaceTables(session);

        //initialize the global comparer object
        comparer = new JUnitDomainObjectComparer();

        //set the session for the comparer to use
        comparer.setSession((AbstractSession)session.getActiveSession());

        //Populate the advanced model
        employeePopulator.buildExamples();
        //populate the relationships model and persist as well
        relationshipExamples.buildExamples(session);

        //Persist the advanced model examples in the database
        employeePopulator.persistExample(session);
    }

    public void findAllOrders() {
        EntityManager em = createEntityManager();
        List expectedResult = getServerSession().readAllObjects(Order.class);

        String ejbqlString = "SELECT o FROM OrderBean o";
        List result = em.createQuery(ejbqlString).getResultList();
        // 4 orders returned
        Assert.assertEquals("Find all orders test failed: data validation error", result.size(), 4);
        Assert.assertTrue("Find all orders test failed", comparer.compareObjects(expectedResult, result));
    }

    public void findEmployeesInOntario() {
        EntityManager em = createEntityManager();
        ExpressionBuilder builder = new ExpressionBuilder();
        Expression whereClause = builder.get("address").get("province").equal("ONT");

        List expectedResult = getServerSession().readAllObjects(Employee.class, whereClause);

        String ejbqlString = "SELECT e FROM Employee e WHERE e.address.province='ONT'";
        List result = em.createQuery(ejbqlString).getResultList();
        //9 employees returned
        Assert.assertEquals("Find Employees in Ontario test failed: data validation error", result.size(), 9);
        Assert.assertTrue("Find Employees in Ontario test failed", comparer.compareObjects(expectedResult, result));

    }

    public void findAllProvinceWithEmployees() {
        boolean testPass = false;
        EntityManager em = createEntityManager();
        ExpressionBuilder builder = new ExpressionBuilder();
        Expression whereClause = builder.get("address").get("province");

        ReportQuery rq = new ReportQuery();
        rq.returnWithoutReportQueryResult();
        rq.setReferenceClass(Employee.class);
        rq.addItem("province", whereClause);
        rq.useDistinct();

        List expectedResult = (List)getServerSession().executeQuery(rq);

        String ejbqlString = "SELECT DISTINCT e.address.province FROM Employee e";
        List result = em.createQuery(ejbqlString).getResultList();

        if (expectedResult.equals(result))
            testPass = true;
        //5 provinces returned
        Assert.assertEquals("Find Province with employees test failed: data validation error", result.size(), 5);
        Assert.assertTrue("Find Province with employees test failed", testPass);
    }

    public void findAllEmployeesWithPhoneNumbers() {
        EntityManager em = createEntityManager();
        ExpressionBuilder builder = new ExpressionBuilder();
        Expression whereClause = builder.isEmpty("phoneNumbers").not();

        ReadAllQuery raq = new ReadAllQuery(Employee.class);
        raq.setSelectionCriteria(whereClause);
        raq.useDistinct();

        List expectedResult = (List)getServerSession().executeQuery(raq);

        String ejbqlString = "SELECT DISTINCT e FROM Employee e, IN (e.phoneNumbers) l";
        Query query = em.createQuery(ejbqlString);
        if (usesSOP() && getServerSession().getPlatform().isOracle()) {
            // distinct is incompatible with blob in selection clause on Oracle
            query.setHint(QueryHints.SERIALIZED_OBJECT, "false");
        }
        List firstResult = query.getResultList();

        String alternateEjbqlString = "SELECT e FROM Employee e WHERE e.phoneNumbers IS NOT EMPTY";
        List secondResult = em.createQuery(alternateEjbqlString).getResultList();
        //14 employees returned
        Assert.assertEquals("Ejbql statements returned different results: data validation error", firstResult.size(), 14);
        Assert.assertTrue("Equivalent Ejbql statements returned different results", comparer.compareObjects(secondResult, firstResult));
        Assert.assertTrue("Find all employees with phone numbers test failed", comparer.compareObjects(expectedResult, firstResult));
    }

    public void findAllEmployeesWithOutPhoneNumbers() {
        EntityManager em = createEntityManager();
        ExpressionBuilder builder = new ExpressionBuilder();
        Expression whereClause = builder.isEmpty("phoneNumbers");

        ReadAllQuery raq = new ReadAllQuery(Employee.class);
        raq.setSelectionCriteria(whereClause);
        raq.useDistinct();

        List expectedResult = (List)getServerSession().executeQuery(raq);

        String ejbqlString = "SELECT DISTINCT e FROM Employee e WHERE e.phoneNumbers IS EMPTY";
        List result = em.createQuery(ejbqlString).getResultList();
        //1 employee w/o phone number returned
        Assert.assertEquals("Find all employees WITHOUT phone numbers test failed: data validation error", result.size(), 1);
        Assert.assertTrue("Find all employees WITHOUT phone numbers test failed", comparer.compareObjects(expectedResult, result));
    }

    public void findAllEmployeesWithCellPhones() {
        EntityManager em = createEntityManager();
        ExpressionBuilder builder = new ExpressionBuilder();
        Expression whereClause = builder.anyOf("phoneNumbers").get("type").equal("Cellular");

        ReadAllQuery raq = new ReadAllQuery(Employee.class);
        raq.setSelectionCriteria(whereClause);
        if (usesSOP() && getServerSession().getPlatform().isOracle()) {
            // distinct is incompatible with blob in selection clause on Oracle
        } else {
            raq.useDistinct();
        }

        List expectedResult = (List)getServerSession().executeQuery(raq);

        String ejbqlString;
        if (usesSOP() && getServerSession().getPlatform().isOracle()) {
            // distinct is incompatible with blob in selection clause on Oracle
            ejbqlString = "SELECT e FROM Employee e JOIN e.phoneNumbers p " + "WHERE p.type = 'Cellular'";
        } else {
            ejbqlString = "SELECT DISTINCT e FROM Employee e JOIN e.phoneNumbers p " + "WHERE p.type = 'Cellular'";
        }
        List firstResult = em.createQuery(ejbqlString).getResultList();
        String alternateEjbqlString;
        if (usesSOP() && getServerSession().getPlatform().isOracle()) {
            // distinct is incompatible with blob in selection clause on Oracle
            alternateEjbqlString = "SELECT e FROM Employee e INNER JOIN e.phoneNumbers p " + "WHERE p.type = 'Cellular'";
        } else {
            alternateEjbqlString = "SELECT DISTINCT e FROM Employee e INNER JOIN e.phoneNumbers p " + "WHERE p.type = 'Cellular'";
        }
        List secondResult = em.createQuery(alternateEjbqlString).getResultList();
        //4 employees returned
        Assert.assertEquals("Find all employees with cellular phone numbers test failed: data validation error", firstResult.size(), 4);
        Assert.assertTrue("Find all employees with cellular phone numbers test failed: two equivalent ejb queries return different results", comparer.compareObjects(secondResult, firstResult));
        Assert.assertTrue("Find all employees with cellular phone numbers test failed", comparer.compareObjects(expectedResult, secondResult));
    }

    public void findOrdersWithDifferentBilledCustomer() {
        EntityManager em = createEntityManager();
        ExpressionBuilder builder = new ExpressionBuilder();
        Expression whereClause = builder.get("customer").equal(builder.get("billedCustomer")).not();

        ReadAllQuery raq = new ReadAllQuery(Order.class);
        raq.setSelectionCriteria(whereClause);

        List expectedResult = (List)getServerSession().executeQuery(raq);

        String ejbqlString = "SELECT o FROM OrderBean o WHERE o.customer <> o.billedCustomer";
        List firstResult = em.createQuery(ejbqlString).getResultList();

        String alternateEjbqlString = "SELECT o FROM OrderBean o WHERE NOT o.customer.customerId = o.billedCustomer.customerId";
        List secondResult = em.createQuery(alternateEjbqlString).getResultList();
        //2 orders returned
        Assert.assertTrue("Find orders with different billed customers test failed: two equivalent ejb queries return different results", comparer.compareObjects(secondResult, firstResult));
        Assert.assertTrue("Find orders with different billed customers test failed", comparer.compareObjects(expectedResult, firstResult));
    }

    public void findEmployeeWithWorkPhone2258812() {
        EntityManager em = createEntityManager();
        ExpressionBuilder builder = new ExpressionBuilder();
        Expression whereClause1 = builder.anyOf("phoneNumbers").get("type").equal("Work");
        Expression whereClause2 = builder.anyOf("phoneNumbers").get("number").equal("2258812");

        ReadAllQuery raq = new ReadAllQuery(Employee.class);
        raq.setSelectionCriteria(whereClause1.and(whereClause2));
        if (usesSOP() && getServerSession().getPlatform().isOracle()) {
            // distinct is incompatible with blob in selection clause on Oracle
        } else {
            raq.useDistinct();
        }

        List expectedResult = (List)getServerSession().executeQuery(raq);

        String ejbqlString;
        if (usesSOP() && getServerSession().getPlatform().isOracle()) {
            // distinct is incompatible with blob in selection clause on Oracle
            ejbqlString = "SELECT e FROM Employee e JOIN e.phoneNumbers p " + "WHERE p.type = 'Work' AND p.number = '2258812' ";
        } else {
            ejbqlString = "SELECT DISTINCT e FROM Employee e JOIN e.phoneNumbers p " + "WHERE p.type = 'Work' AND p.number = '2258812' ";
        }
        List result = em.createQuery(ejbqlString).getResultList();
        //8 employees
        Assert.assertEquals("Find employee with 2258812 number test failed: data validation error", result.size(), 8);
        Assert.assertTrue("Find employee with 2258812 number test failed", comparer.compareObjects(expectedResult, result));
    }

    public void parameterTest() {
        EntityManager em = createEntityManager();
        List employeeList = getServerSession().readAllObjects(Employee.class);
        Employee expectedEmployee = (Employee)employeeList.get(0);
        int i = 1;
        while (expectedEmployee.getPhoneNumbers().size() == 0) {
            expectedEmployee = (Employee)employeeList.get(i);
            i++;
        }
        String phoneNumber = (expectedEmployee.getPhoneNumbers().iterator().next()).getNumber();
        String ejbqlString;
        String alternateEjbqlString;
        if (usesSOP() && getServerSession().getPlatform().isOracle()) {
            // distinct is incompatible with blob in selection clause on Oracle
            ejbqlString = "SELECT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = ?1";
            alternateEjbqlString = "SELECT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = :number";
        } else {
            ejbqlString = "SELECT DISTINCT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = ?1";
            alternateEjbqlString = "SELECT DISTINCT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = :number";
        }

        List firstResult = em.createQuery(ejbqlString).setParameter(1, phoneNumber).getResultList();
        List secondResult = em.createQuery(alternateEjbqlString).setParameter("number", phoneNumber).getResultList();
        //random test cant duplicate
        Assert.assertTrue("Parameter test failed: two equivalent ejb queries return different results", comparer.compareObjects(secondResult, firstResult));
        Assert.assertTrue("Parameter test failed", comparer.compareObjects(expectedEmployee, firstResult));
    }


    public void getOrderLargerThan() {
        EntityManager em = createEntityManager();

        ExpressionBuilder builder1 = new ExpressionBuilder(Order.class);
        ExpressionBuilder builder2 = new ExpressionBuilder(Order.class);
        Expression o1Quantity = builder1.get("quantity");
        Expression o2Quantity = builder2.get("quantity");
        Expression quantityComparison = o1Quantity.greaterThan(o2Quantity);
        Expression o2CustomerName = builder2.get("customer").get("name");
        Expression nameComparison = o2CustomerName.equal("Jane Smith");
        Expression whereClause = quantityComparison.and(nameComparison);

        ReadAllQuery raq = new ReadAllQuery();
        raq.setSelectionCriteria(whereClause);
        raq.setReferenceClass(Order.class);
        raq.useDistinct();
        List expectedResult = (List)getServerSession().executeQuery(raq);

        String ejbqlString = "SELECT DISTINCT o1 FROM OrderBean o1, OrderBean o2 WHERE o1.quantity > o2.quantity AND" + " o2.customer.name = 'Jane Smith' ";
        List result = em.createQuery(ejbqlString).getResultList();
        //only 1 order
        Assert.assertEquals("Get order larger than test failed: data validation error", result.size(), 1);
        Assert.assertTrue("Get order larger than test failed", comparer.compareObjects(expectedResult, result));
    }

    public void getOrderForCustomer() {
        EntityManager em = createEntityManager();
        ExpressionBuilder builder = new ExpressionBuilder();
        Expression whereClause = builder.get("name").equal("Jane Smith");

        ReadAllQuery raq = new ReadAllQuery(Customer.class);
        raq.setSelectionCriteria(whereClause);

        Customer expectedCustomer = (Customer)(((List)getServerSession().executeQuery(raq)).get(0));
        SalesPerson salesPerson = ((Order)(expectedCustomer.getOrders().iterator().next())).getSalesPerson();

        String ejbqlString = "SELECT DISTINCT c FROM Customer c JOIN c.orders o JOIN o.salesPerson s WHERE s.id = " + salesPerson.getId();
        List firstResult = em.createQuery(ejbqlString).getResultList();
        String alternateEjbqlString = "SELECT DISTINCT c FROM Customer c, IN(c.orders) o WHERE o.salesPerson.id = " + salesPerson.getId();
        List secondResuslt = em.createQuery(alternateEjbqlString).getResultList();

        //only 1 order for this customer
        Assert.assertEquals("Get order for customer test failed: data validation error", firstResult.size(), 1);
        Assert.assertTrue("Get order for customer test failed: two equivalent ejb queries return different results", comparer.compareObjects(secondResuslt, firstResult));
        Assert.assertTrue("Get order for customer test failed", comparer.compareObjects(expectedCustomer, firstResult));
    }

    public void getSalesPersonForOrders() {
        EntityManager em = createEntityManager();

        List expectedResult = getServerSession().readAllObjects(SalesPerson.class);

        String ejbqlString = "SELECT DISTINCT o.salesPerson FROM Customer AS c, IN(c.orders) o";
        List result = em.createQuery(ejbqlString).getResultList();
        //2 sales person
        Assert.assertEquals("Get SalesPerson for Orders test failed: data validation error", result.size(), 2);
        Assert.assertTrue("Get SalesPerson for Orders test failed", comparer.compareObjects(expectedResult, result));
    }

    public void testOuterJoin() {
        EntityManager em = createEntityManager();
        ExpressionBuilder builder = new ExpressionBuilder();
        Expression whereClause = builder.anyOfAllowingNone("phoneNumbers").get("type").equal("Cellular");
        ReadAllQuery raq = new ReadAllQuery(Employee.class);
        raq.setSelectionCriteria(whereClause);
        List expectedResult = (List)getServerSession().executeQuery(raq);

        String ejbqlString = "SELECT e FROM Employee e LEFT JOIN e.phoneNumbers p " + "WHERE p.type = 'Cellular'";
        List firstResult = em.createQuery(ejbqlString).getResultList();
        String alternateEjbqlString = "SELECT e FROM Employee e LEFT OUTER JOIN e.phoneNumbers p " + "WHERE p.type = 'Cellular'";
        List secondResult = em.createQuery(alternateEjbqlString).getResultList();
        //return 4 employees with cell phones
        Assert.assertEquals("Get SalesPerson for Orders test failed: data validation error", firstResult.size(), 4);
        Assert.assertTrue("Get Outer Join test failed: two equivalent ejb queries return different results", comparer.compareObjects(secondResult, firstResult));
        Assert.assertTrue("Get Outer Join test failed", comparer.compareObjects(expectedResult, firstResult));
    }

    public void testExistsExpression() {
        EntityManager em = createEntityManager();
        boolean testPass = false;
        ExpressionBuilder employeeBuilder = new ExpressionBuilder(Employee.class);
        ExpressionBuilder managerBuilder = new ExpressionBuilder(Employee.class);
        ReportQuery mainQuery = new ReportQuery();
        ReportQuery subQuery = new ReportQuery();

        subQuery.setReferenceClass(Employee.class);
        Expression managerExpression = employeeBuilder.get("manager").get("id").equal(managerBuilder.get("id"));
        subQuery.addAttribute("one", new ConstantExpression(1, subQuery.getExpressionBuilder()));
        subQuery.setSelectionCriteria(managerExpression);
        Expression employeeExpression = employeeBuilder.exists(subQuery);

        mainQuery.setReferenceClass(Employee.class);
        mainQuery.setSelectionCriteria(employeeExpression);
        mainQuery.addAttribute("id");
        mainQuery.returnWithoutReportQueryResult();
        List expectedResult = (List)getServerSession().executeQuery(mainQuery);

        String ejbqlString = "SELECT DISTINCT emp.id FROM Employee emp WHERE EXISTS ( SELECT managedEmp.id FROM Employee managedEmp WHERE managedEmp = emp.manager)";
        List result = em.createQuery(ejbqlString).getResultList();

        if (result.containsAll(expectedResult) && expectedResult.containsAll(result))
            testPass = true;
        //8 employees with managers
        Assert.assertEquals("Exists Expression test failed: data validation error", result.size(), 8);
        Assert.assertTrue("Exists Expression test failed", testPass);
    }

    public void testAllExpressions() {
        EntityManager em = createEntityManager();
        boolean testPass = false;
        ExpressionBuilder mainQueryBuilder = new ExpressionBuilder(Employee.class);
        ExpressionBuilder subQueryBuilder = new ExpressionBuilder(Employee.class);
        ReportQuery mainQuery = new ReportQuery();
        mainQuery.setReferenceClass(Employee.class);
        ReportQuery subQuery = new ReportQuery();
        subQuery.setReferenceClass(Employee.class);

        Expression subQueryExpression = subQueryBuilder.get("address").get("city").equal(mainQueryBuilder.get("address").get("city")).and(subQueryBuilder.get("salary").lessThan(1000));
        subQuery.setSelectionCriteria(subQueryExpression);
        subQuery.addAttribute("salary");
        Expression mainQueryExpression = mainQueryBuilder.get("salary").greaterThan(mainQueryBuilder.all(subQuery));
        mainQuery.addAttribute("id");
        mainQuery.setSelectionCriteria(mainQueryExpression);
        mainQuery.returnWithoutReportQueryResult();
        List expectedResult = (List)getServerSession().executeQuery(mainQuery);

        String ejbqlString = "SELECT emp.id FROM Employee emp WHERE emp.salary > ALL ( SELECT e.salary FROM Employee e WHERE e.address.city = emp.address.city AND e.salary < 1000)";
        List result = em.createQuery(ejbqlString).getResultList();

        if (result.containsAll(expectedResult) && expectedResult.containsAll(result))
            testPass = true;

        if (result.size() != 12) {
            // H2 ALL does not work correctly if the result is empty.
            if (getServerSession().getPlatform().isH2()) {
                warning("ALL fails on H2 as H2 has an SQL bug in ALL of none");
            } else {
                fail("All Expression test failed: data validation error: " + result.size() + " != " + 12);
            }
        }
        Assert.assertTrue("All Expression test failed", testPass);
    }

    public void testCountInSubQuery() {
        EntityManager em = createEntityManager();
        boolean testPass = false;
        ReportQuery mainQuery = new ReportQuery(Customer.class, new ExpressionBuilder());
        ReportQuery subQuery = new ReportQuery(Order.class, new ExpressionBuilder());
        subQuery.setSelectionCriteria(subQuery.getExpressionBuilder().get("customer").get("customerId").equal(mainQuery.getExpressionBuilder().get("customerId")));
        subQuery.addCount("orderId");
        mainQuery.setSelectionCriteria(mainQuery.getExpressionBuilder().subQuery(subQuery).greaterThan(0));
        mainQuery.addAttribute("customerId");
        mainQuery.returnWithoutReportQueryResult();
        List expectedResult = (List)getServerSession().executeQuery(mainQuery);
        String ejbqlString = "SELECT c.customerId FROM Customer c WHERE (SELECT COUNT(o) FROM c.orders o) > 0";
        List result = em.createQuery(ejbqlString).getResultList();
        if (result.containsAll(expectedResult) && expectedResult.containsAll(result))
            testPass = true;

        Assert.assertEquals("Count subquery test failed: data validation error", result.size(), 2);
        Assert.assertTrue("Count subquery test failed", testPass);
    }

    public void testGroupByHavingExpression() {
        EntityManager em = createEntityManager();
        boolean testPass = true;

        ReadAllQuery raq = new ReadAllQuery(Employee.class, new ExpressionBuilder());
        Expression whereClause1 = raq.getExpressionBuilder().get("firstName").equal("Bob");
        Expression whereClause2 = raq.getExpressionBuilder().get("lastName").equal("Smith");
        Expression whereClause3 = raq.getExpressionBuilder().get("firstName").equal("John");
        Expression whereClause4 = raq.getExpressionBuilder().get("lastName").equal("Way");

        raq.setSelectionCriteria((whereClause1.and(whereClause2)).or(whereClause3.and(whereClause4)));
        List employees = (List)getServerSession().executeQuery(raq);
        int firstManagerId = ((Employee)employees.get(0)).getId();
        int secondManagerId = ((Employee)employees.get(1)).getId();
        int expectedEmployeesManaged = ((Employee)employees.get(0)).getManagedEmployees().size() + ((Employee)employees.get(1)).getManagedEmployees().size();
        Vector managerVector = new Vector();
        managerVector.add(firstManagerId);
        managerVector.add(secondManagerId);
        ReportQuery query = new ReportQuery(Employee.class, new ExpressionBuilder());
        query.returnWithoutReportQueryResult();
        query.addGrouping(query.getExpressionBuilder().get("manager").get("id"));
        query.setHavingExpression(query.getExpressionBuilder().get("manager").get("id").in(managerVector));
        query.addAttribute("managerId", query.getExpressionBuilder().get("manager").get("id"));
        query.addAverage("salary", Double.class);
        query.addCount("id", Long.class);


        List expectedResult = (List)getServerSession().executeQuery(query);

        String ejbqlString = "SELECT e.manager.id, avg(e.salary), count(e) FROM Employee e" + " GROUP BY e.manager.id HAVING e.manager.id IN (" + firstManagerId + "," + secondManagerId + ")";

        List result = em.createQuery(ejbqlString).getResultList();
        int employeesManaged = 0;
        Iterator expectedResultIterator = expectedResult.iterator();
        Iterator resultIterator = result.iterator();
        if (expectedResult.size() == result.size()) {
            while (resultIterator.hasNext()) {
                Object objectArray[] = (Object[])expectedResultIterator.next();
                Object otherObjectArray[] = (Object[])resultIterator.next();
                testPass = testPass && objectArray[0].equals(otherObjectArray[0]);
                testPass = testPass && objectArray[1].equals(otherObjectArray[1]);
                testPass = testPass && objectArray[2].equals(otherObjectArray[2]);
                employeesManaged = ((Long)objectArray[2]).intValue() + employeesManaged;
            }
        } else {
            testPass = false;
        }

        Assert.assertEquals("GroupBy Having expression test failed: data validation error", employeesManaged, expectedEmployeesManaged);
        Assert.assertTrue("GroupBy Having expression test failed", testPass);

    }

    public void testGroupByHavingCount() {
        EntityManager em = createEntityManager();
        boolean testPass = true;
        ReportQuery query = new ReportQuery(Employee.class, new ExpressionBuilder());
        query.returnWithoutReportQueryResult();
        query.addGrouping(query.getExpressionBuilder().get("address").get("province"));
        query.addAttribute("province", query.getExpressionBuilder().get("address").get("province"));
        query.addCount("provinces", query.getExpressionBuilder().get("address").get("province"), Long.class);
        query.setHavingExpression(query.getExpressionBuilder().get("address").get("province").count().greaterThan(3));
        List expectedResult = (List)getServerSession().executeQuery(query);

        String ejbqlString = "SELECT e.address.province, COUNT(e) FROM Employee e GROUP BY e.address.province HAVING COUNT(e.address.province) > 3";
        List result = em.createQuery(ejbqlString).getResultList();

        Iterator expectedResultIterator = expectedResult.iterator();
        Iterator resultIterator = result.iterator();
        if (expectedResult.size() == result.size()) {
            while (resultIterator.hasNext()) {
                Object objectArray[] = (Object[])expectedResultIterator.next();
                Object otherObjectArray[] = (Object[])resultIterator.next();
                testPass = testPass && objectArray[0].equals(otherObjectArray[0]);
                testPass = testPass && objectArray[1].equals(otherObjectArray[1]);
            }
        } else {
            testPass = false;
        }
        Assert.assertEquals("GroupBy Having count expression test failed: data validation error", result.size(), 1);
        Assert.assertTrue("GroupBy Having Count expression test failed", testPass);
    }

    public void testConstructorQuery() {
        EntityManager em = createEntityManager();
        String ejbqlString = "SELECT NEW org.eclipse.persistence.testing.models.jpa.relationships.CustomerDetails(c.customerId, o.quantity) FROM Customer " + "c JOIN c.orders o WHERE o.quantity > 100";

        List custDetails = em.createQuery(ejbqlString).getResultList();
        Assert.assertTrue("Constructor query test failed: not an instance of CustomerDetail", custDetails.get(0) instanceof CustomerDetails);
        Assert.assertEquals("Constructor query test failed, expecting only 1 customer with order > 100", custDetails.size(), 1);
    }

    public void testSumExpression() {
        EntityManager em = createEntityManager();

        ReportQuery query = new ReportQuery(Employee.class, new ExpressionBuilder());
        Expression whereClause = query.getExpressionBuilder().get("address").get("province").equal("QUE");
        query.addSum("areaCodeSums", query.getExpressionBuilder().anyOf("phoneNumbers").get("id"), Long.class);
        query.setSelectionCriteria(whereClause);
        query.returnWithoutReportQueryResult();
        Long expectedResult = (Long)((List)getServerSession().executeQuery(query)).get(0);
        String ejbqlString = "SELECT SUM(p.id) FROM Employee e JOIN e.phoneNumbers p JOIN e.address a" + " WHERE a.province = 'QUE' ";
        Long result = (Long)em.createQuery(ejbqlString).getSingleResult();
        Assert.assertEquals("Average expression test failed", expectedResult, result);
    }

    public void testAvgExpression() {
        EntityManager em = createEntityManager();

        ReportQuery query = new ReportQuery(Order.class, new ExpressionBuilder());
        query.addAverage("average quantity", query.getExpressionBuilder().get("quantity"), Double.class);
        query.returnSingleResult();
        Double expectedResult = (Double)((ReportQueryResult)getServerSession().executeQuery(query)).get("average quantity");
        String ejbqlString = "SELECT AVG(o.quantity) FROM OrderBean o";
        Double result = (Double)em.createQuery(ejbqlString).getSingleResult();
        Assert.assertEquals("Average expression test failed", expectedResult, result);
    }

    //bug 5166658

    public void testCountExpression() {
        EntityManager em = createEntityManager();

        ReportQuery query = new ReportQuery(Employee.class, new ExpressionBuilder());
        Expression whereClause1 = query.getExpressionBuilder().get("address").get("province").equal("QUE");
        Expression whereClause2 = query.getExpressionBuilder().get("address").get("city").equal("Montreal");
        query.setSelectionCriteria(whereClause1.and(whereClause2));
        query.addCount("areaCodeCount", query.getExpressionBuilder().anyOf("phoneNumbers").get("areaCode"), Long.class);
        query.returnSingleResult();
        Long expectedResult = (Long)((ReportQueryResult)getServerSession().executeQuery(query)).get("areaCodeCount");

        String ejbqlString = "SELECT COUNT(p.areaCode) FROM Employee e JOIN e.phoneNumbers p JOIN e.address a " + " WHERE a.province='QUE' AND a.city='Montreal'";
        Long result = (Long)em.createQuery(ejbqlString).getSingleResult();

        String alternateEjbqlString = "SELECT COUNT(p) FROM Employee e JOIN e.phoneNumbers p JOIN e.address a " + " WHERE a.province='QUE' AND a.city='Montreal' AND p.areaCode IS NOT NULL";
        Long alternateResult = (Long)em.createQuery(alternateEjbqlString).getSingleResult();

        Assert.assertTrue("Count expression test failed: data validation error, ReportQuery returned 0", expectedResult.intValue() > 0);
        Assert.assertTrue("Count expression test failed: data validation error, first JPQL returned 0", result.intValue() > 0);
        Assert.assertTrue("Count expression test failed: data validation error, second JPQL returned 0", alternateResult.intValue() > 0);
        Assert.assertTrue("Count expression test failed: two equivalent ejb queries return different results", alternateResult.equals(result));
        Assert.assertEquals("Count expression test failed", expectedResult, result);
    }

    public void testOrderByExpression() {
        EntityManager em = createEntityManager();

        ReadAllQuery raq = new ReadAllQuery(PhoneNumber.class, new ExpressionBuilder());
        Expression whereClause = raq.getExpressionBuilder().get("owner").get("address").get("province").equal("ONT");
        raq.setSelectionCriteria(whereClause);
        raq.addOrdering(raq.getExpressionBuilder().get("areaCode"));
        raq.addOrdering(raq.getExpressionBuilder().get("type"));
        List expectedResult = (List)getServerSession().executeQuery(raq);

        String ejbqlString = "SELECT p FROM Employee e JOIN e.phoneNumbers p JOIN e.address a WHERE a.province = 'ONT' " + "ORDER BY p.areaCode, p.type";
        List result = em.createQuery(ejbqlString).getResultList();
        Assert.assertEquals("OrderBy expression test failed: data validation error", result.size(), 13);
        Assert.assertTrue("OrderBy expression test failed", comparer.compareObjects(expectedResult, result));
    }

    public void testOrderByExpressionWithSelect() {
        EntityManager em = createEntityManager();
        boolean testPass = true;
        ReportQuery query = new ReportQuery(PhoneNumber.class, new ExpressionBuilder());
        Expression whereClause = query.getExpressionBuilder().get("owner").get("address").get("province").equal("ONT");
        query.setSelectionCriteria(whereClause);
        query.addOrdering(query.getExpressionBuilder().get("areaCode"));
        query.addOrdering(query.getExpressionBuilder().get("type"));
        query.addAttribute("areaCode", query.getExpressionBuilder().get("areaCode"));
        query.addAttribute("type", query.getExpressionBuilder().get("type"));
        //query.useDistinct(); //removed as distinct no longer used on joins in JPQL
        query.returnWithoutReportQueryResult();
        List expectedResult = (List)getServerSession().executeQuery(query);

        String ejbqlString = "SELECT p.areaCode, p.type FROM Employee e JOIN e.phoneNumbers p JOIN e.address a WHERE a.province = 'ONT' " + "ORDER BY p.areaCode, p.type";
        List result = em.createQuery(ejbqlString).getResultList();
        Iterator expectedResultIterator = expectedResult.iterator();
        Iterator resultIterator = result.iterator();
        if (expectedResult.size() == result.size()) {
            while (resultIterator.hasNext()) {
                Object objectArray[] = (Object[])expectedResultIterator.next();
                Object otherObjectArray[] = (Object[])resultIterator.next();
                testPass = testPass && objectArray[0].equals(otherObjectArray[0]);
                testPass = testPass && objectArray[1].equals(otherObjectArray[1]);
            }
        } else {
            testPass = false;
        }

        Assert.assertEquals("OrderBy expression test failed: data validation error", result.size(), 13);
        Assert.assertTrue("OrderBy with Select expression test failed", testPass);
    }

    public void testDeleteExpression() {
        if (isOnServer()) {
            // Not work on server.
            return;
        }
        if ((JUnitTestCase.getServerSession()).getPlatform().isSymfoware()) {
            getServerSession().logMessage("Test testDeleteExpression skipped for this platform, "
                    + "Symfoware doesn't support UpdateAll/DeleteAll on multi-table objects (see rfe 298193).");
            return;
        }

        JpaEntityManager em = (org.eclipse.persistence.jpa.JpaEntityManager)createEntityManager();
        try {
            beginTransaction(em);
            String orderString = "DELETE FROM OrderBean o WHERE o.customer.name ='Karen McDonald' ";
            em.createQuery(orderString).executeUpdate();
            orderString = "DELETE FROM OrderBean o WHERE o.billedCustomer.name ='Karen McDonald' ";
            em.createQuery(orderString).executeUpdate();
            String ejbqlString = "DELETE FROM Customer c WHERE c.name='Karen McDonald' ";
            int result = em.createQuery(ejbqlString).executeUpdate();
            Assert.assertEquals("Delete Expression test failed: customer to delete not found", 1, result);
            em.flush();

            ReadAllQuery raq = new ReadAllQuery(Customer.class, new ExpressionBuilder());
            Expression whereClause = raq.getExpressionBuilder().get("name").equal("Karen McDonald");
            raq.setSelectionCriteria(whereClause);
            List customerFound = (List)em.getActiveSession().executeQuery(raq);
            Assert.assertEquals("Delete Expression test failed", 0, customerFound.size());
        } finally {
            rollbackTransaction(em);
        }
    }

    // Bug 5090182

    public void testEJBQLQueryString() {
        List<Object[]> emps = createEntityManager().createQuery("SELECT e, a FROM Employee e, Address a WHERE e.address = a").getResultList();
        assertFalse("No employees were read, debug and look at the SQL that was generated. ", emps.isEmpty());
    }

    public void testComplexDeleteExpression() {
        if (isOnServer()) {
            // Not work on server.
            return;
        }
        if ((JUnitTestCase.getServerSession()).getPlatform().isSymfoware()) {
            getServerSession().logMessage("Test testComplexDeleteExpression skipped for this platform, "
                    + "Symfoware doesn't support UpdateAll/DeleteAll on multi-table objects (see rfe 298193).");
            return;
        }

        JpaEntityManager em = (org.eclipse.persistence.jpa.JpaEntityManager)createEntityManager();
        try {
            beginTransaction(em);
            String orderString = "DELETE FROM OrderBean o WHERE o.customer.name ='Karen McDonald' ";
            em.createQuery(orderString).executeUpdate();
            orderString = "DELETE FROM OrderBean o WHERE o.billedCustomer.name ='Karen McDonald' ";
            em.createQuery(orderString).executeUpdate();
            String ejbqlString = "DELETE FROM Customer c WHERE c.name='Karen McDonald' AND c.orders IS EMPTY";
            int result = em.createQuery(ejbqlString).executeUpdate();
            Assert.assertEquals("Complex Delete Expression test failed: customer to delete not found", 1, result);
            em.flush();

            ReadAllQuery raq = new ReadAllQuery(Customer.class, new ExpressionBuilder());
            Expression whereClause1 = raq.getExpressionBuilder().get("name").equal("Karen McDonald");
            Expression whereClause2 = raq.getExpressionBuilder().isEmpty("orders");
            raq.setSelectionCriteria(whereClause1.and(whereClause2));
            List customerFound = (List)em.getActiveSession().executeQuery(raq);
            Assert.assertEquals("Complex Delete Expression test failed", 0, customerFound.size());
        } finally {
            rollbackTransaction(em);
        }
    }

    //bug 5159164, 5159198

    public void testUpdateExpression() {
        if ((JUnitTestCase.getServerSession()).getPlatform().isSymfoware()) {
            getServerSession().logMessage("Test testUpdateExpression skipped for this platform, "
                    + "Symfoware doesn't support UpdateAll/DeleteAll on multi-table objects (see rfe 298193).");
            return;
        }
        EntityManager em = createEntityManager();
        Number result = null;
        beginTransaction(em);
        try {
            String ejbqlString = "UPDATE Customer c SET c.name = 'Test Case' WHERE c.name = 'Jane Smith' " + "AND 0 < (SELECT COUNT(o) FROM Customer cust JOIN cust.orders o)";
            result = em.createQuery(ejbqlString).executeUpdate();
            commitTransaction(em);
        } finally {
            if (isTransactionActive(em)) {
                rollbackTransaction(em);
            }
        }

        Assert.assertEquals("Update expression test failed", 1, result);
    }

    //Bug5097278 Test case for updating the manager of ALL employees that have a certain address

    public void updateAllTest() {
        if ((JUnitTestCase.getServerSession()).getPlatform().isSymfoware()) {
            getServerSession().logMessage("Test updateAllTest skipped for this platform, "
                    + "Symfoware doesn't support UpdateAll/DeleteAll on multi-table objects (see rfe 298193).");
            return;
        }
        EntityManager em = createEntityManager();

        String empName = "Saunders";
        String manName = "Smitty";

        String ejbqlString = "SELECT DISTINCT e FROM Employee e WHERE e.lastName = '" + empName + "'";
        Employee employee = (Employee)em.createQuery(ejbqlString).getSingleResult();
        Address addr = em.find(Address.class, employee.getAddress().getID());

        String ejbqlString2 = "SELECT DISTINCT e FROM Employee e WHERE e.lastName = '" + manName + "'";
        Employee manager = (Employee)em.createQuery(ejbqlString2).getSingleResult();


        beginTransaction(em);

        em.createQuery("UPDATE Employee e SET e.manager = :manager " + "WHERE e.address = :addr ").setParameter("manager", manager).setParameter("addr", addr).executeUpdate();

        commitTransaction(em);

        String ejbqlString3 = "SELECT DISTINCT e.manager FROM Employee e WHERE e.lastName = '" + empName + "'";
        String result = ((Employee)em.createQuery(ejbqlString3).getSingleResult()).getLastName();

        Assert.assertTrue("UpdateAll test failed", result.equals(manName));

    }

    public void updateEmbeddedFieldTest() {
        if ((JUnitTestCase.getServerSession()).getPlatform().isSymfoware()) {
            getServerSession().logMessage("Test updateEmbeddedFieldTest skipped for this platform, "
                    + "Symfoware doesn't support UpdateAll/DeleteAll on multi-table objects (see rfe 298193).");
            return;
        }

        EntityManager em = createEntityManager();
        Calendar startCalendar = Calendar.getInstance();
        startCalendar.set(1905, 11, 31, 0, 0, 0);
        java.sql.Date startDate = new java.sql.Date(startCalendar.getTime().getTime());

        beginTransaction(em);
        em.createQuery("UPDATE Employee e SET e.period.startDate= :startDate").setParameter("startDate", startDate).executeUpdate();
        commitTransaction(em);
    }


    //Bug5040609  Test if EJBQuery makes a clone of the original DatabaseQuery from the session

    public void namedQueryCloneTest() {
        EntityManager em = createEntityManager();

        List result1 = em.createNamedQuery("findAllCustomers").getResultList();

        List result2 = em.createNamedQuery("findAllCustomers").setMaxResults(1).getResultList();

        List result3 = em.createNamedQuery("findAllCustomers").getResultList();

        Assert.assertEquals("Named query clone test failed: the first result should be 4", result1.size(), 4);
        Assert.assertEquals("Named query clone test failed: the second result should be 1", result2.size(), 1);
        Assert.assertEquals("Named query clone test failed: the third result should be 4", result3.size(), 4);
    }

    //Bug5040609 Test case for aggregates as parameters in EJBQL

    public void aggregateParameterTest() {
        EntityManager em = createEntityManager();

        ExpressionBuilder builder = new ExpressionBuilder();
        ReportQuery query = new ReportQuery(org.eclipse.persistence.testing.models.jpa.advanced.Employee.class, builder);
        query.returnWithoutReportQueryResult();
        query.addItem("employee", builder);

        org.eclipse.persistence.testing.models.jpa.advanced.EmploymentPeriod period = new EmploymentPeriod();
        Calendar startCalendar = Calendar.getInstance();
        Calendar endCalendar = Calendar.getInstance();
        startCalendar.set(1901, 11, 31, 0, 0, 0);
        endCalendar.set(1995, 0, 12, 0, 0, 0);
        period.setStartDate(new java.sql.Date(startCalendar.getTime().getTime()));
        period.setEndDate(new java.sql.Date(endCalendar.getTime().getTime()));
        Expression exp = builder.get("period").equal(builder.getParameter("period"));
        query.setSelectionCriteria(exp);
        query.addArgument("period", EmploymentPeriod.class);

        Vector args = new Vector();
        args.add(period);

        List expectedResult = (Vector)getServerSession().executeQuery(query, args);

        List result = em.createQuery("SELECT e FROM Employee e WHERE e.period = :period ").setParameter("period", period).getResultList();

        Assert.assertTrue("aggregateParameterTest failed", comparer.compareObjects(expectedResult, result));
    }

}