jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/tests/jpa/jpql/JUnitJPQLComplexAggregateTestSuite.java

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

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

import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Vector;
import jakarta.persistence.Query;
import jakarta.persistence.EntityManager;
import junit.framework.Test;
import junit.framework.TestSuite;

import org.eclipse.persistence.expressions.Expression;
import org.eclipse.persistence.expressions.ExpressionBuilder;
import org.eclipse.persistence.queries.ReportQuery;
import org.eclipse.persistence.testing.models.jpa.advanced.Address;
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.AdvancedTableCreator;
import org.eclipse.persistence.testing.models.jpa.advanced.EmploymentPeriod;
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.advanced.compositepk.Cubicle;
import org.eclipse.persistence.testing.models.jpa.advanced.compositepk.Scientist;
import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;
import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.testing.models.jpa.advanced.compositepk.CompositePKTableCreator;

import org.junit.Assert;

/**
 * <p>
 * <b>Purpose</b>: Test complex aggregate EJBQL functionality.
 * <p>
 * <b>Description</b>: This class creates a test suite, initializes the database
 * and adds tests to the suite.
 * <p>
 * <b>Responsibilities</b>:
 * <ul>
 * <li> Run tests for complex aggregate EJBQL functionality
 * </ul>
 * @see org.eclipse.persistence.testing.models.jpa.advanced.EmployeePopulator
 * @see JUnitDomainObjectComparer
 */

//This test suite demonstrates the bug 4616218, waiting for bug fix
public class JUnitJPQLComplexAggregateTestSuite extends JUnitTestCase
{
    static JUnitDomainObjectComparer comparer;        //the global comparer object used in all tests

    public JUnitJPQLComplexAggregateTestSuite()
    {
        super();
    }

    public JUnitJPQLComplexAggregateTestSuite(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("JUnitJPQLComplexAggregateTestSuite");
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("testSetup"));

        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexSelectAggregateTest"));

        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexAVGTest"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexAVGOrderTest"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexCountDistinctWithGroupByAndHavingTest"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexCountDistinctWithGroupByTest"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexCountDistinctWithGroupByTest2"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexHavingWithAggregate"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexCountTest"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexCountWithGroupByTest"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexDistinctCountTest"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexMaxTest"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexMinTest"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexSumTest"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexCountDistinctOnBaseQueryClass"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexCountOnJoinedVariableSimplePK"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexCountOnJoinedVariableCompositePK"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexCountOnJoinedVariableOverManyToManySelfRefRelationship"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexCountOnJoinedVariableOverManyToManySelfRefRelationshipPortable"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("complexCountOnJoinedCompositePK"));
        suite.addTest(new JUnitJPQLComplexAggregateTestSuite("testMultipleCoalesce"));

        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();

        new AdvancedTableCreator().replaceTables(session);
        new CompositePKTableCreator().replaceTables(session);

        RelationshipsExamples relationshipExamples = new RelationshipsExamples();
        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 tables
        employeePopulator.buildExamples();

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

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

    public void complexAVGTest()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);
        ExpressionBuilder expbldr = new ExpressionBuilder();

        ReportQuery rq = new ReportQuery(Employee.class, expbldr);

        Expression exp = expbldr.get("lastName").equal("Smith");

        rq.setReferenceClass(Employee.class);
        rq.setSelectionCriteria(exp);
        rq.returnSingleAttribute();
        rq.dontRetrievePrimaryKeys();
        rq.useDistinct();
        rq.addAverage("salary", Double.class);

        String ejbqlString = "SELECT AVG(DISTINCT emp.salary) FROM Employee emp WHERE emp.lastName = \"Smith\"";

        Vector expectedResultVector = (Vector) getServerSession().executeQuery(rq);
        Double expectedResult = (Double)expectedResultVector.get(0);

        clearCache();

        Double result = (Double) em.createQuery(ejbqlString).getSingleResult();

        Assert.assertEquals("Complex AVG test failed", expectedResult, result);
        rollbackTransaction(em);
        closeEntityManager(em);
    }

    public void complexAVGOrderTest()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);
        ExpressionBuilder expbldr = new ExpressionBuilder();

        ReportQuery rq = new ReportQuery(Employee.class, expbldr);

        Expression exp = expbldr.get("lastName").equal("Smith");

        rq.setReferenceClass(Employee.class);
        rq.setSelectionCriteria(exp);
        rq.dontRetrievePrimaryKeys();
        if ((getServerSession().getPlatform().isSymfoware() || getServerSession().getPlatform().isDerby())) {
            warning("Distinct on aggregate function not supported by " + getServerSession().getPlatform());
        } else {
            // the following line uncovers a bug where 'AVG(t1.SALARY)' is
            // listed in the generated select list twice. As it is also in the
            // ORDER BY clause, Symfoware complains that it does not know which
            // in the SELECT list it is referring to.
            rq.useDistinct();
        }

        Expression avgSal = expbldr.get("salary").average();
        rq.addAttribute("salary", avgSal);
        rq.addOrdering(avgSal);

        Expression gender = expbldr.get("gender");
        rq.addAttribute("gender", gender);
        rq.addGrouping(gender);



        String ejbqlString = "SELECT emp.gender, AVG(DISTINCT emp.salary) sal FROM Employee emp WHERE emp.lastName = \"Smith\" group by emp.gender order by sal";

        if ((getServerSession().getPlatform().isSymfoware() || getServerSession().getPlatform().isDerby())) {
            ejbqlString = "SELECT emp.gender, AVG(emp.salary) sal FROM Employee emp WHERE emp.lastName = \"Smith\" group by emp.gender order by sal";
        }
        Vector expectedResultVector = (Vector) getServerSession().executeQuery(rq);

        clearCache();

        List result =  em.createQuery(ejbqlString).getResultList();

        Assert.assertTrue("complexAVGOrderTest test failed", comparer.compareObjects(result, expectedResultVector));
        rollbackTransaction(em);
        closeEntityManager(em);
    }

    /*
     * test for gf675, using count, group by and having fails.  This test is specific for a a use case
     * with Count and group by
     */
    public void complexCountDistinctWithGroupByAndHavingTest()
    {
        String havingFilterString = "Toronto";
        EntityManager em = createEntityManager();
        beginTransaction(em);
        //Need to set the class in the expressionbuilder, as the Count(Distinct) will cause the
        // query to change and be built around the Employee class instead of the Address class.
        ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);

        ReportQuery rq = new ReportQuery(Address.class, expbldr);
        Expression exp = expbldr.anyOf("employees");

        Expression exp2 = expbldr.get("city");
        rq.addAttribute("city", exp2);
        rq.addCount("COUNT",exp.distinct(),Long.class );
        rq.addGrouping(exp2);
        rq.setHavingExpression(exp2.equal(havingFilterString));
        Vector expectedResult = (Vector) getServerSession().executeQuery(rq);
        String ejbqlString3 = "SELECT a.city, COUNT( DISTINCT e ) FROM Address a JOIN a.employees e GROUP BY a.city HAVING a.city =?1";
        Query q = em.createQuery(ejbqlString3);
        q.setParameter(1,havingFilterString);
        List result = q.getResultList();

        Assert.assertTrue("Complex COUNT test failed", comparer.compareObjects(result, expectedResult));
        rollbackTransaction(em);
        closeEntityManager(em);
    }


    /*
     * test for gf675, using count, group by and having fails.  This test is specific for a a use case
     * where DISTINCT is used with Count and group by
     */
    public void complexCountDistinctWithGroupByTest()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);
        //need to set the class in the expressionbuilder, as the Count(Distinct) will cause the
        // query to change and be built around the Employee class instead of the Address class.
        ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);

        ReportQuery rq = new ReportQuery(Address.class, expbldr);
        Expression exp = expbldr.anyOf("employees");

        Expression exp2 = expbldr.get("city");
        rq.addAttribute("city", exp2);
        rq.addCount("COUNT",exp.distinct(),Long.class );
        rq.addGrouping(exp2);
        Vector expectedResult = (Vector) getServerSession().executeQuery(rq);
        String ejbqlString3 = "SELECT a.city, COUNT( DISTINCT e ) FROM Address a JOIN a.employees e GROUP BY a.city";
        Query q = em.createQuery(ejbqlString3);
        List result = q.getResultList();

        Assert.assertTrue("Complex COUNT(Distinct) with Group By test failed", comparer.compareObjects(result, expectedResult));
        rollbackTransaction(em);
        closeEntityManager(em);
    }

    /*
     * test for gf675, using count, group by and having fails.  This test is specific for a a use case
     * where DISTINCT is used with Count and group by
     */
    public void complexCountDistinctWithGroupByTest2()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);
        //need to set the class in the expressionbuilder, as the Count(Distinct) will cause the
        // query to change and be built around the Employee class instead of the Address class.
        ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);

        ReportQuery rq = new ReportQuery(Address.class, expbldr);
        Expression exp = expbldr.anyOf("employees");

        Expression exp2 = expbldr.get("city");
        rq.addAttribute("city", exp2);
        rq.addCount("COUNT1",exp, Long.class);
        rq.addCount("COUNT2",exp.get("lastName").distinct(),Long.class );
        rq.addGrouping(exp2);
        Vector expectedResult = (Vector) getServerSession().executeQuery(rq);
        String ejbqlString3 = "SELECT a.city, COUNT( e ), COUNT( DISTINCT e.lastName ) FROM Address a JOIN a.employees e GROUP BY a.city";
        Query q = em.createQuery(ejbqlString3);
        List result = q.getResultList();

        Assert.assertTrue("Complex COUNT(Distinct) with Group By test failed", comparer.compareObjects(result, expectedResult));
        rollbackTransaction(em);
        closeEntityManager(em);
    }

    /**
     * Test for partial fix of GF 932.
     */
    public void complexHavingWithAggregate()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);
        // Test using the project id in COUNT, GROUP BY and HAVING
        ExpressionBuilder employeeBuilder = new ExpressionBuilder(Employee.class);
        ReportQuery rq = new ReportQuery(Employee.class, employeeBuilder);
        Expression projects = employeeBuilder.anyOf("projects");
        Expression pid = projects.get("id");
        Expression count = pid.count();
        rq.addAttribute("id", pid);
        rq.addAttribute("COUNT", count, Long.class);
        rq.addGrouping(pid);
        rq.setHavingExpression(count.greaterThan(1));
        rq.setShouldReturnWithoutReportQueryResult(true);
        //Vector expectedResult = (Vector) em.getActiveSession().executeQuery(rq);
        Vector expectedResult = (Vector) getServerSession().executeQuery(rq);
        String jpql =
            "SELECT p.id, COUNT(p.id) FROM Employee e JOIN e.projects p " +
            "GROUP BY p.id HAVING COUNT(p.id)>1";
        List result = em.createQuery(jpql).getResultList();

        Assert.assertTrue("Complex HAVING with aggregate function failed",
                          comparer.compareObjects(result, expectedResult));

        // Test using the project itself in COUNT, GROUP BY and HAVING
        employeeBuilder = new ExpressionBuilder(Employee.class);
        rq = new ReportQuery(Employee.class, employeeBuilder);
        projects = employeeBuilder.anyOf("projects");
        count = projects.count();
        rq.addAttribute("projects", projects);
        rq.addAttribute("COUNT", count, Long.class);
        rq.addGrouping(projects);
        rq.setHavingExpression(count.greaterThan(1));
        rq.setShouldReturnWithoutReportQueryResult(true);
        expectedResult = (Vector) getServerSession().executeQuery(rq);

        jpql =
            "SELECT p, COUNT(p) FROM Employee e JOIN e.projects p " +
            "GROUP BY p HAVING COUNT(p)>1";
        result = em.createQuery(jpql).getResultList();

        Assert.assertTrue("Complex HAVING with aggregate function failed",
                          comparer.compareObjects(result, expectedResult));
        rollbackTransaction(em);
        closeEntityManager(em);
    }

    public void complexCountTest()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);
        try {
            ExpressionBuilder expbldr = new ExpressionBuilder();

            ReportQuery rq = new ReportQuery(Employee.class, expbldr);

            Expression exp = expbldr.get("lastName").equal("Smith");

            rq.setReferenceClass(Employee.class);
            rq.setSelectionCriteria(exp);
            rq.returnSingleAttribute();
            rq.dontRetrievePrimaryKeys();
            rq.addCount("COUNT", expbldr, Long.class);
            Vector expectedResultVector = (Vector) getServerSession().executeQuery(rq);
            Long expectedResult = (Long) expectedResultVector.get(0);

            String ejbqlString = "SELECT COUNT(emp) FROM Employee emp WHERE emp.lastName = \"Smith\"";
            Long result = (Long) em.createQuery(ejbqlString).getSingleResult();

            Assert.assertEquals("Complex COUNT test failed", expectedResult, result);
        } finally {
            rollbackTransaction(em);
            closeEntityManager(em);
        }
    }

    /*
     * test for gf675, using count, group by and having fails.  This test is specific for a a use case
     * with Count and group by
     */
    public void complexCountWithGroupByTest()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);
        //Need to set the class in the expressionbuilder, as the Count(Distinct) will cause the
        // query to change and be built around the Employee class instead of the Address class.
        ExpressionBuilder expbldr = new ExpressionBuilder(Address.class);

        ReportQuery rq = new ReportQuery(Address.class, expbldr);
        Expression exp = expbldr.anyOf("employees");

        Expression exp2 = expbldr.get("city");
        rq.addAttribute("city", exp2);
        rq.addCount("COUNT",exp.distinct(),Long.class );
        rq.addGrouping(exp2);
        Vector expectedResult = (Vector) getServerSession().executeQuery(rq);
        String ejbqlString3 = "SELECT a.city, COUNT( DISTINCT e ) FROM Address a JOIN a.employees e GROUP BY a.city";
        Query q = em.createQuery(ejbqlString3);
        List result = q.getResultList();

        Assert.assertTrue("Complex COUNT with Group By test failed", comparer.compareObjects(result, expectedResult));
        rollbackTransaction(em);
        closeEntityManager(em);
    }

    public void complexDistinctCountTest()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);
        ExpressionBuilder expbldr = new ExpressionBuilder();

        ReportQuery rq = new ReportQuery(Employee.class, expbldr);

        Expression exp = expbldr.get("lastName").equal("Smith");

        rq.setReferenceClass(Employee.class);
        rq.setSelectionCriteria(exp);
        rq.useDistinct();
        rq.returnSingleAttribute();
        rq.dontRetrievePrimaryKeys();
        rq.addCount("COUNT", expbldr.get("lastName").distinct(), Long.class);
        Vector expectedResultVector = (Vector) getServerSession().executeQuery(rq);
        Long expectedResult = (Long) expectedResultVector.get(0);

        String ejbqlString = "SELECT COUNT(DISTINCT emp.lastName) FROM Employee emp WHERE emp.lastName = \"Smith\"";
        Long result = (Long) em.createQuery(ejbqlString).getSingleResult();

        Assert.assertEquals("Complex DISTINCT COUNT test failed", expectedResult, result);
        rollbackTransaction(em);
        closeEntityManager(em);
    }

    public void complexMaxTest()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);
        ExpressionBuilder expbldr = new ExpressionBuilder();

        ReportQuery rq = new ReportQuery(Employee.class, expbldr);
        rq.setReferenceClass(Employee.class);
        rq.returnSingleAttribute();
        rq.dontRetrievePrimaryKeys();
        rq.addAttribute("salary", expbldr.get("salary").distinct().maximum(), Integer.class);
        Vector expectedResultVector = (Vector) getServerSession().executeQuery(rq);
        Number expectedResult = (Number) expectedResultVector.get(0);

        String ejbqlString = "SELECT MAX(DISTINCT emp.salary) FROM Employee emp";
        Number result = (Number) em.createQuery(ejbqlString).getSingleResult();

        Assert.assertEquals("Type returned was not expected", Integer.class, result.getClass());
        Assert.assertEquals("Complex MAX test failed", expectedResult, result);
        rollbackTransaction(em);
        closeEntityManager(em);
    }

    public void complexMinTest()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);
        ExpressionBuilder expbldr = new ExpressionBuilder();

        ReportQuery rq = new ReportQuery(Employee.class, expbldr);
        rq.setReferenceClass(Employee.class);
        rq.returnSingleAttribute();
        rq.dontRetrievePrimaryKeys();
        rq.addAttribute("salary", expbldr.get("salary").distinct().minimum(), Integer.class);
        Vector expectedResultVector = (Vector) getServerSession().executeQuery(rq);
        Number expectedResult = (Number) expectedResultVector.get(0);

        String ejbqlString = "SELECT MIN(DISTINCT emp.salary) FROM Employee emp";
        Number result = (Number) em.createQuery(ejbqlString).getSingleResult();

        Assert.assertEquals("Type returned was not expected", Integer.class, result.getClass());
        Assert.assertEquals("Complex MIN test failed", expectedResult, result);
        rollbackTransaction(em);
        closeEntityManager(em);
    }

    public void complexSumTest()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);
        ExpressionBuilder expbldr = new ExpressionBuilder();

        ReportQuery rq = new ReportQuery(Employee.class, expbldr);
        rq.setReferenceClass(Employee.class);
        rq.returnSingleAttribute();
        rq.dontRetrievePrimaryKeys();
        rq.addAttribute("salary", expbldr.get("salary").distinct().sum(), Long.class);
        Vector expectedResultVector = (Vector) getServerSession().executeQuery(rq);
        Long expectedResult = (Long) expectedResultVector.get(0);

        String ejbqlString = "SELECT SUM(DISTINCT emp.salary) FROM Employee emp";
        Long result = (Long) em.createQuery(ejbqlString).getSingleResult();

        Assert.assertEquals("Complex SUMtest failed", expectedResult, result);
        rollbackTransaction(em);
        closeEntityManager(em);
    }

    /**
     * Test case glassfish issue 2725:
     */
    public void complexCountDistinctOnBaseQueryClass()
    {
        EntityManager em = createEntityManager();
        beginTransaction(em);

        Long expectedResult = (long) getServerSession().readAllObjects(Employee.class).size();

        String jpql = "SELECT COUNT(DISTINCT e) FROM Employee e";
        Query q = em.createQuery(jpql);
        Long result = (Long) q.getSingleResult();

        Assert.assertEquals("Complex COUNT DISTINCT on base query class ", expectedResult, result);

        rollbackTransaction(em);
        closeEntityManager(em);
    }

    /**
     * Test case glassfish issue 2497:
     */
    public void complexCountOnJoinedVariableSimplePK()
    {
        EntityManager em = createEntityManager();

        // Need to create the expected result manually, because using the
        // TopLink query API would run into the same issue 2497.
        List<Long> expectedResult = Arrays.asList(new Long[] {1L, 0L,
                0L, 1L});
        Collections.sort(expectedResult);

        String jpql = "SELECT COUNT(o) FROM Customer c LEFT JOIN c.orders o GROUP BY c.name";
        Query q = em.createQuery(jpql);
        List result = q.getResultList();
        Collections.sort(result);

        Assert.assertEquals("Complex COUNT on joined variable simple PK", expectedResult, result);

        jpql = "SELECT COUNT(DISTINCT o) FROM Customer c LEFT JOIN c.orders o GROUP BY c.name";
        q = em.createQuery(jpql);
        result = q.getResultList();
        Collections.sort(result);

        Assert.assertEquals("Complex COUNT DISTINCT on joined variable simple PK", expectedResult, result);
    }

    /**
     * Test case glassfish issue 2497:
     */
    public void complexCountOnJoinedVariableCompositePK()
    {
        EntityManager em = createEntityManager();

        // Need to create the expected result manually, because using the
        // TopLink query API would run into the same issue 2497.
        List<Long> expectedResult = Arrays.asList(new Long[] {2L, 5L, 3L});
        Collections.sort(expectedResult);

        String jpql = "SELECT COUNT(p) FROM Employee e LEFT JOIN e.phoneNumbers p WHERE e.lastName LIKE 'S%' GROUP BY e.lastName";
        Query q = em.createQuery(jpql);
        List result = q.getResultList();
        Collections.sort(result);

        Assert.assertEquals("Complex COUNT on outer joined variable composite PK", expectedResult, result);

        // COUNT DISTINCT with inner join
        jpql = "SELECT COUNT(DISTINCT p) FROM Employee e JOIN e.phoneNumbers p WHERE e.lastName LIKE 'S%' GROUP BY e.lastName";
        q = em.createQuery(jpql);
        result = q.getResultList();
        Collections.sort(result);

        Assert.assertEquals("Complex DISTINCT COUNT on inner joined variable composite PK", expectedResult, result);
}

    /**
     * Test case bug 6155093:
     */
    public void complexCountOnJoinedCompositePK()
    {
        EntityManager em = createEntityManager();
        try{
            beginTransaction(em);
                Scientist s = new Scientist();
                s.setFirstName("John");
                s.setLastName("Doe");
                Cubicle c = new Cubicle();
                c.setCode("G");

                c.setScientist(s);
                s.setCubicle(c);
                em.persist(c);
                em.persist(s);
            em.flush();

            // Need to create the expected result manually, because using the
            // TopLink query API would run into the same issue 6155093.
            List<Long> expectedResult = Arrays.asList(new Long[] {1L});
            Collections.sort(expectedResult);

            // COUNT DISTINCT with inner join
            String jpql = "SELECT COUNT(DISTINCT p) FROM Scientist e JOIN e.cubicle p WHERE e.lastName LIKE 'D%'";
            Query q = em.createQuery(jpql);
            List result = q.getResultList();
            Collections.sort(result);

            Assert.assertEquals("Complex COUNT on joined variable composite PK", expectedResult, result);
        }finally{
            rollbackTransaction(em);
        }
}

    /**
     * Test case glassfish issue 2440:
     * On derby a JPQL query including a LEFT JOIN on a ManyToMany
     * relationship field of the same class (self-referencing relationship)
     * runs into a NPE in SQLSelectStatement.appendFromClauseForOuterJoin.
     */
    public void complexCountOnJoinedVariableOverManyToManySelfRefRelationshipPortable()
    {
        EntityManager em = createEntityManager();

        List<Object[]> expectedResult = Arrays.asList(new Object[][] { {0L, "Jane Smith"}, {1L, "John Smith"}, {0L, "Karen McDonald"}, { 0L, "Robert Sampson"} });

        String jpql = "SELECT COUNT(cc), c.name FROM Customer c LEFT JOIN c.CCustomers cc GROUP BY c.name order by c.name";
        Query q = em.createQuery(jpql);
        List<Object[]> result = q.getResultList();

        final String description = "Complex COUNT on joined variable over ManyToMany self refrenceing relationship";

        Assert.assertEquals(description + " size mismatch", expectedResult.size(), result.size());

        for (int i = 0; i < result.size(); i++) {
            Object[] expected = expectedResult.get(i);
            Object[] actual = result.get(i);
            Assert.assertEquals(expected[0], actual[0]);
            Assert.assertEquals(expected[1], actual[1]);
        }
    }

    /**
     * Test case glassfish issue 2440:
     * On derby a JPQL query including a LEFT JOIN on a ManyToMany
     * relationship field of the same class (self-referencing relationship)
     * runs into a NPE in SQLSelectStatement.appendFromClauseForOuterJoin.
     */
    public void complexCountOnJoinedVariableOverManyToManySelfRefRelationship()
    {
        if (getServerSession().getPlatform().isMaxDB()) {
            return; // bug 327108 MaxDB can't order by non-selec tlist column c.name
        }

        EntityManager em = createEntityManager();

        List<Long> expectedResult = Arrays.asList(new Long[] { 0L, 1L, 0L, 0L });

        String jpql = "SELECT COUNT(cc) FROM Customer c LEFT JOIN c.CCustomers cc GROUP BY c.name order by c.name";
        Query q = em.createQuery(jpql);
        List result = q.getResultList();

        Assert.assertEquals("Complex COUNT on joined variable over ManyToMany self refrenceing relationship failed",
                            expectedResult, result);
    }


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

        Expression exp = (new ExpressionBuilder()).get("firstName").equal("Bob");
        Employee employee = (Employee)getServerSession().readObject(Employee.class, exp);
        EmploymentPeriod expectedResult = employee.getPeriod();

        String jpql = "SELECT e.period from Employee e where e.firstName = 'Bob'";
        Query q = em.createQuery(jpql);
        EmploymentPeriod result = (EmploymentPeriod)q.getSingleResult();

        Assert.assertEquals("complexSelectAggregateTest failed - start dates don't match",
                            expectedResult.getStartDate(), result.getStartDate());
        Assert.assertEquals("complexSelectAggregateTest failed - end dates don't match",
                expectedResult.getEndDate(), result.getEndDate());
    }

    public void testMultipleCoalesce() {
        EntityManager em = createEntityManager();
        Query query = em.createQuery("SELECT SUM(COALESCE(e.roomNumber, 20)), SUM(COALESCE(e.salary, 10000)) FROM Employee e");
        List result = query.getResultList();
        Assert.assertNotNull("testMultipleCoalesce Test Failed - Unable to fetch employee data", result);
        Assert.assertFalse("testMultipleCoalesce Test Failed - Unable to fetch employee data", result.isEmpty());
        Object[] aggregateResult = (Object[])result.get(0);
        Assert.assertFalse("testMultipleCoalesce Test Failed ", aggregateResult[0].equals(aggregateResult[1]));
        closeEntityManager(em);
    }
}