/*
 * 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.math.BigDecimal;
import java.sql.Timestamp;
import java.util.Date;

import jakarta.persistence.Query;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Expression;
import jakarta.persistence.criteria.Join;
import jakarta.persistence.criteria.JoinType;
import jakarta.persistence.criteria.ListJoin;
import jakarta.persistence.criteria.ParameterExpression;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;
import jakarta.persistence.criteria.SetJoin;
import jakarta.persistence.criteria.Subquery;

import org.apache.openjpa.jdbc.sql.DerbyDictionary;

/**
 * Tests type-strict version of Criteria API.
 *
 * Most of the tests build Criteria Query and then execute the query as well as
 * a reference JPQL query supplied as a string. The test is validated by
 * asserting that the resultant SQL queries for these two alternative form of
 * executing a query are the same.
 *
 *
 */
public class TestJPQLSubquery extends CriteriaTest {

    public void testSubqueries1() {
        String jpql = "SELECT goodCustomer FROM Customer goodCustomer WHERE "
            + "goodCustomer.balanceOwed < (SELECT AVG(c.balanceOwed) "
            + " FROM Customer c)";
        String expectedSQL = "SELECT t0.id, t0.accountNum, t2.id, t2.city, t2.country, t2.county, t2.state, "
            + "t2.street, t3.userid, t3.DTYPE, t3.age, t3.compName, t3.creditRating, t3.name, t2.zipCode, "
            + "t0.balanceOwed, t0.creditRating, t0.filledOrderCount, t0.firstName, t0.lastName, t0.name, t0.status "
            + "FROM CR_CUST t0 LEFT OUTER JOIN CR_ADDR t2 ON t0.ADDRESS_ID = t2.id "
            + "LEFT OUTER JOIN CompUser t3 ON t2.id = t3.ADD_ID "
            + "WHERE (t0.balanceOwed < (SELECT AVG(t1.balanceOwed) FROM CR_CUST t1))";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> goodCustomer = q.from(Customer.class);
        q.select(goodCustomer);
        Subquery<Double> sq = q.subquery(Double.class);
        Root<Customer> c = sq.from(Customer.class);
        q.where(cb.lt(goodCustomer.get(Customer_.balanceOwed), sq
                .select(cb.avg(c.get(Customer_.balanceOwed)))));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries2() {
        String jpql = "SELECT DISTINCT emp FROM Employee emp WHERE EXISTS ("
            + "SELECT spouseEmp FROM Employee spouseEmp WHERE spouseEmp ="
            + " emp.spouse)";
        String expectedSQL = "SELECT t1.empId, t1.EMP_TYPE, t2.id, t2.city, t2.country, t2.county, t2.state, "
            + "t2.street, t3.userid, t3.DTYPE, t3.age, t3.compName, t3.creditRating, t3.name, t2.zipCode, t4.deptNo, "
            + "t4.name, t5.id, t5.annualMiles, t5.name, t6.id, t7.deptNo, t7.name, t6.name, t6.salary, t1.name, "
            + "t1.rating, t1.salary, t8.empId, t8.EMP_TYPE, t8.ADDRESS_ID, t8.DEPARTMENT_DEPTNO, "
            + "t8.FREQUENTFLIERPLAN_ID, "
            + "t8.MANAGER_ID, t8.name, t8.rating, t8.salary, t8.hireDate, t1.hireDate "
            + "FROM CR_EMP t1 LEFT OUTER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id "
            + "LEFT OUTER JOIN CR_DEPT t4 ON t1.DEPARTMENT_DEPTNO = t4.deptNo "
            + "LEFT OUTER JOIN FrequentFlierPlan t5 ON t1.FREQUENTFLIERPLAN_ID = t5.id "
            + "LEFT OUTER JOIN CR_MGR t6 ON t1.MANAGER_ID = t6.id "
            + "LEFT OUTER JOIN CR_EMP t8 ON t1.SPOUSE_EMPID = t8.empId "
            + "LEFT OUTER JOIN CompUser t3 ON t2.id = t3.ADD_ID "
            + "LEFT OUTER JOIN CR_DEPT t7 ON t6.DEPARTMENT_DEPTNO = t7.deptNo "
            + "WHERE (EXISTS (SELECT t0.empId FROM CR_EMP t0 WHERE (t0.empId = t1.SPOUSE_EMPID)))";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Employee> q = cb.createQuery(Employee.class);
        Root<Employee> emp = q.from(Employee.class);
        q.select(emp).distinct(true);
        Subquery<Employee> sq = q.subquery(Employee.class);
        Root<Employee> spouseEmp = sq.from(Employee.class);
        sq.where(cb.equal(spouseEmp, emp.get(Employee_.spouse)));
        q.where(cb.exists(sq.select(spouseEmp)));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries3() {
        String jpql = "SELECT emp FROM Employee emp WHERE emp.salary > ALL ("
            + "SELECT m.salary FROM Manager m WHERE m.department = "
            + "emp.department)";
        String expectedSQL = "SELECT t0.empId, t0.EMP_TYPE, t2.id, t2.city, t2.country, t2.county, t2.state, "
            + "t2.street, t3.userid, t3.DTYPE, t3.age, t3.compName, t3.creditRating, t3.name, t2.zipCode, t4.deptNo, "
            + "t4.name, t5.id, t5.annualMiles, t5.name, t6.id, t7.deptNo, t7.name, t6.name, t6.salary, t0.name, "
            + "t0.rating, t0.salary, t8.empId, t8.EMP_TYPE, t8.ADDRESS_ID, t8.DEPARTMENT_DEPTNO, "
            + "t8.FREQUENTFLIERPLAN_ID, t8.MANAGER_ID, t8.name, t8.rating, t8.salary, t8.hireDate, t0.hireDate "
            + "FROM CR_EMP t0 LEFT OUTER JOIN CR_ADDR t2 ON t0.ADDRESS_ID = t2.id "
            + "LEFT OUTER JOIN CR_DEPT t4 ON t0.DEPARTMENT_DEPTNO = t4.deptNo "
            + "LEFT OUTER JOIN FrequentFlierPlan t5 ON t0.FREQUENTFLIERPLAN_ID = t5.id "
            + "LEFT OUTER JOIN CR_MGR t6 ON t0.MANAGER_ID = t6.id "
            + "LEFT OUTER JOIN CR_EMP t8 ON t0.SPOUSE_EMPID = t8.empId "
            + "LEFT OUTER JOIN CompUser t3 ON t2.id = t3.ADD_ID "
            + "LEFT OUTER JOIN CR_DEPT t7 ON t6.DEPARTMENT_DEPTNO = t7.deptNo "
            + "WHERE (t0.salary > ALL (SELECT t1.salary "
            + "FROM CR_MGR t1 WHERE (t1.DEPARTMENT_DEPTNO = t0.DEPARTMENT_DEPTNO)))";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Employee> q = cb.createQuery(Employee.class);
        Root<Employee> emp = q.from(Employee.class);
        q.select(emp);
        Subquery<BigDecimal> sq = q.subquery(BigDecimal.class);
        Root<Manager> m = sq.from(Manager.class);
        sq.where(cb.equal(m.get(Manager_.department), emp.get(Employee_.department)));
        sq.select(m.get(Manager_.salary));
        q.where(cb.gt(emp.get(Employee_.salary), cb.all(sq)));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries4() {
        String jpql = "SELECT c FROM Customer c WHERE "
            + "(SELECT COUNT(o) FROM c.orders o) > 10";
        String expectedSQL = "SELECT t0.id, t0.accountNum, t3.id, t3.city, t3.country, t3.county, t3.state, "
            + "t3.street, t4.userid, t4.DTYPE, t4.age, t4.compName, t4.creditRating, t4.name, t3.zipCode, "
            + "t0.balanceOwed, t0.creditRating, "
            + "t0.filledOrderCount, t0.firstName, t0.lastName, t0.name, t0.status "
            + "FROM CR_CUST t0 LEFT OUTER JOIN CR_ADDR t3 ON t0.ADDRESS_ID = t3.id "
            + "LEFT OUTER JOIN CompUser t4 ON t3.id = t4.ADD_ID "
            + "WHERE ((SELECT COUNT(t2.id) FROM CR_ODR t1, CR_ODR t2 WHERE (t1.id = t2.id) AND "
            + "(t0.id = t1.CUSTOMER_ID)) > ?)";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        q.select(c);
        Subquery<Long> sq = q.subquery(Long.class);
        Root<Customer> csq = sq.correlate(c);
        Join<Customer, Order> o = csq.join(Customer_.orders);
        q.where(cb.gt(sq.select(cb.count(o)), 10));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries4a() {
        String jpql = "SELECT c FROM Customer c WHERE "
            + "(SELECT COUNT(o) FROM Customer c1 JOIN c1.orders o WHERE c = c1) > 10";
        String expectedSQL = "SELECT t2.id, t2.accountNum, t3.id, t3.city, t3.country, t3.county, t3.state, "
            + "t3.street, t4.userid, t4.DTYPE, t4.age, t4.compName, t4.creditRating, t4.name, t3.zipCode, "
            + "t2.balanceOwed, t2.creditRating, t2.filledOrderCount, t2.firstName, t2.lastName, t2.name, t2.status "
            + "FROM CR_CUST t2 LEFT OUTER JOIN CR_ADDR t3 ON t2.ADDRESS_ID = t3.id "
            + "LEFT OUTER JOIN CompUser t4 ON t3.id = t4.ADD_ID "
            + "WHERE ((SELECT COUNT(t1.id) "
            + "FROM CR_CUST t0 INNER JOIN CR_ODR t1 ON t0.id = t1.CUSTOMER_ID WHERE (t2.id = t0.id)) > ?)";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        q.select(c);
        Subquery<Long> sq = q.subquery(Long.class);
        Root<Customer> c1 = sq.from(Customer.class);
        Join<Customer, Order> o = c1.join(Customer_.orders);
        sq.where(cb.equal(c, c1));
        q.where(cb.gt(sq.select(cb.count(o)), 10));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries4b() {
        String jpql = "SELECT c FROM Customer c WHERE "
            + "(SELECT COUNT(o) FROM Customer c1 JOIN c1.orders o WHERE c.id = c1.id) > 10";

        String expectedSQL = "SELECT t2.id, t2.accountNum, t3.id, t3.city, t3.country, t3.county, t3.state, "
            + "t3.street, t4.userid, t4.DTYPE, t4.age, t4.compName, t4.creditRating, t4.name, t3.zipCode, "
            + "t2.balanceOwed, t2.creditRating, t2.filledOrderCount, t2.firstName, t2.lastName, t2.name, t2.status "
            + "FROM CR_CUST t2 LEFT OUTER JOIN CR_ADDR t3 ON t2.ADDRESS_ID = t3.id "
            + "LEFT OUTER JOIN CompUser t4 ON t3.id = t4.ADD_ID "
            + "WHERE ((SELECT COUNT(t1.id) "
            + "FROM CR_CUST t0 INNER JOIN CR_ODR t1 ON t0.id = t1.CUSTOMER_ID WHERE (t2.id = t0.id)) > ?)";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        q.select(c);
        Subquery<Long> sq = q.subquery(Long.class);
        Root<Customer> c1 = sq.from(Customer.class);
        Join<Customer, Order> o = c1.join(Customer_.orders);
        sq.where(cb.equal(c.get(Customer_.id), c1.get(Customer_.id)));
        q.where(cb.gt(sq.select(cb.count(o)), 10));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries4c() {
        String jpql = "SELECT c FROM Customer c WHERE "
            + "(SELECT COUNT(o) FROM Customer c1 JOIN c1.orders o) > 10";
        String expectedSQL = "SELECT t2.id, t2.accountNum, t3.id, t3.city, t3.country, t3.county, t3.state, "
            + "t3.street, t4.userid, t4.DTYPE, t4.age, t4.compName, t4.creditRating, t4.name, t3.zipCode, "
            + "t2.balanceOwed, t2.creditRating, t2.filledOrderCount, t2.firstName, t2.lastName, t2.name, t2.status "
            + "FROM CR_CUST t2 LEFT OUTER JOIN CR_ADDR t3 ON t2.ADDRESS_ID = t3.id "
            + "LEFT OUTER JOIN CompUser t4 ON t3.id = t4.ADD_ID "
            + "WHERE ((SELECT COUNT(t1.id) FROM CR_CUST t0 INNER JOIN CR_ODR t1 ON t0.id = t1.CUSTOMER_ID) > ?)";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        q.select(c);
        Subquery<Long> sq = q.subquery(Long.class);
        Root<Customer> c1 = sq.from(Customer.class);
        Join<Customer, Order> o = c1.join(Customer_.orders);
        q.where(cb.gt(sq.select(cb.count(o)), 10));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries4d() {
        String jpql = "SELECT c FROM Customer c WHERE (SELECT COUNT(o) "
            + "FROM Customer c1 JOIN c1.orders o WHERE c.address.county = c1.address.county) > 10";

        String expectedSQL = "SELECT t2.id, t2.accountNum, t5.id, t5.city, t5.country, t5.county, "
            + "t5.state, t5.street, t6.userid, t6.DTYPE, t6.age, t6.compName, t6.creditRating, t6.name, "
            + "t5.zipCode, t2.balanceOwed, t2.creditRating, t2.filledOrderCount, t2.firstName, t2.lastName, "
            + "t2.name, t2.status "
            + "FROM CR_CUST t2 "
            + "INNER JOIN CR_ADDR t3 ON t2.ADDRESS_ID = t3.id "
            + "LEFT OUTER JOIN CR_ADDR t5 ON t2.ADDRESS_ID = t5.id "
            + "LEFT OUTER JOIN CompUser t6 ON t5.id = t6.ADD_ID "
            + "WHERE ((SELECT COUNT(t1.id) "
            + "FROM CR_CUST t0 "
            + "INNER JOIN CR_ODR t1 ON t0.id = t1.CUSTOMER_ID "
            + "INNER JOIN CR_ADDR t4 ON t0.ADDRESS_ID = t4.id "
            + "WHERE (t3.county = t4.county)) > ?)";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        q.select(c);
        Subquery<Long> sq = q.subquery(Long.class);
        Root<Customer> c1 = sq.from(Customer.class);
        Join<Customer, Order> o = c1.join(Customer_.orders);
        sq.where(cb.equal(c.get(Customer_.address).get(Address_.county),
            c1.get(Customer_.address).get(Address_.county)));
        q.where(cb.gt(sq.select(cb.count(o)), 10));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries4e() {
        String jpql = "SELECT c FROM Customer c WHERE EXISTS "
            + "(SELECT o.id FROM Order o WHERE o.customer = c)";
        String expectedSQL = "SELECT t1.id, t1.accountNum, t2.id, t2.city, t2.country, t2.county, t2.state, " +
        "t2.street, t3.userid, t3.DTYPE, t3.age, t3.compName, t3.creditRating, t3.name, t2.zipCode, t1.balanceOwed, " +
        "t1.creditRating, t1.filledOrderCount, t1.firstName, t1.lastName, t1.name, t1.status " +
        "FROM CR_CUST t1 " +
        "LEFT OUTER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id " +
        "LEFT OUTER JOIN CompUser t3 ON t2.id = t3.ADD_ID WHERE " +
        "(EXISTS (SELECT t0.id FROM CR_ODR t0 WHERE (t0.CUSTOMER_ID = t1.id)))";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        q.select(c);
        Subquery<Integer> sq = q.subquery(Integer.class);
        Root<Order> o = sq.from(Order.class);
        sq.where(cb.equal(o.get(Order_.customer), c));
        sq.select(o.get(Order_.id));
        q.where(cb.exists(sq));

        assertEquivalence(q, jpql);

    }

    public void testSubqueries5() {
        String jpql = "SELECT o FROM Order o WHERE 10000 < ALL ("
            + "SELECT a.balance FROM o.customer c JOIN c.accounts a)";
        String expectedSQL = "SELECT t3.id, t3.cnt, t4.id, t4.accountNum, t5.id, t5.city, t5.country, t5.county, "
            + "t5.state, t5.street, t6.userid, t6.DTYPE, t6.age, t6.compName, t6.creditRating, t6.name, t5.zipCode, "
            + "t4.balanceOwed, t4.creditRating, t4.filledOrderCount, t4.firstName, t4.lastName, t4.name, t4.status, "
            + "t3.delivered, t3.name, t3.orderTs, t3.quantity, t3.totalCost "
            + "FROM CR_ODR t3 LEFT OUTER JOIN CR_CUST t4 ON t3.CUSTOMER_ID = t4.id "
            + "LEFT OUTER JOIN CR_ADDR t5 ON t4.ADDRESS_ID = t5.id "
            + "LEFT OUTER JOIN CompUser t6 ON t5.id = t6.ADD_ID "
            + "WHERE (? < ALL (SELECT t2.balance FROM CR_CUST t0 INNER JOIN CR_CUST_CR_ACCT t1 ON "
            + "t0.id = t1.CUSTOMER_ID INNER JOIN CR_ACCT t2 ON t1.ACCOUNTS_ID = t2.id "
            + "WHERE (t3.CUSTOMER_ID = t0.id)))";

        executeAndCompareSQL(jpql, expectedSQL);

        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> o1 = sq.correlate(o);
        Join<Order,Customer> c = o1.join(Order_.customer);
        ListJoin<Customer,Account> a = c.join(Customer_.accounts);
        sq.select(a.get(Account_.balance));
        q.where(cb.lt(cb.literal(10000), cb.all(sq)));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries5a() {
        String jpql = "SELECT o FROM Order o WHERE o.name = SOME ("
            + "SELECT a.name FROM o.customer c JOIN c.accounts a)";
        String expectedSQL = "SELECT t3.id, t3.cnt, t4.id, t4.accountNum, t5.id, t5.city, t5.country, t5.county, "
            + "t5.state, t5.street, t6.userid, t6.DTYPE, t6.age, t6.compName, t6.creditRating, t6.name, t5.zipCode, "
            + "t4.balanceOwed, t4.creditRating, t4.filledOrderCount, t4.firstName, t4.lastName, t4.name, t4.status, "
            + "t3.delivered, t3.name, t3.orderTs, t3.quantity, t3.totalCost "
            + "FROM CR_ODR t3 LEFT OUTER JOIN CR_CUST t4 ON t3.CUSTOMER_ID = t4.id "
            + "LEFT OUTER JOIN CR_ADDR t5 ON t4.ADDRESS_ID = t5.id "
            + "LEFT OUTER JOIN CompUser t6 ON t5.id = t6.ADD_ID "
            + "WHERE (t3.name = ANY (SELECT t2.name "
            + "FROM CR_CUST t0 INNER JOIN CR_CUST_CR_ACCT t1 ON t0.id = t1.CUSTOMER_ID "
            + "INNER JOIN CR_ACCT t2 ON t1.ACCOUNTS_ID = t2.id WHERE (t3.CUSTOMER_ID = t0.id)))";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Order> q = cb.createQuery(Order.class);
        Root<Order> o = q.from(Order.class);
        q.select(o);
        Subquery<String> sq = q.subquery(String.class);
        Root<Order> o1 = sq.correlate(o);
        Join<Order,Customer> c = o1.join(Order_.customer);
        ListJoin<Customer,Account> a = c.join(Customer_.accounts);
        sq.select(a.get(Account_.name));
        q.where(cb.equal(o.get(Order_.name), cb.some(sq)));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries6() {
        String jpql = "SELECT o FROM Order o JOIN o.customer c WHERE 10000 < "
            + "ALL (SELECT a.balance FROM c.accounts a)";
        String expectedSQL = "SELECT t0.id, t0.cnt, t4.id, t4.accountNum, t5.id, t5.city, t5.country, t5.county, "
            + "t5.state, t5.street, t6.userid, t6.DTYPE, t6.age, t6.compName, t6.creditRating, t6.name, t5.zipCode, "
            + "t4.balanceOwed, t4.creditRating, t4.filledOrderCount, t4.firstName, t4.lastName, t4.name, t4.status, "
            + "t0.delivered, t0.name, t0.orderTs, t0.quantity, t0.totalCost "
            + "FROM CR_ODR t0 INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
            + "LEFT OUTER JOIN CR_CUST t4 ON t0.CUSTOMER_ID = t4.id "
            + "LEFT OUTER JOIN CR_ADDR t5 ON t4.ADDRESS_ID = t5.id "
            + "LEFT OUTER JOIN CompUser t6 ON t5.id = t6.ADD_ID WHERE (? < ALL ("
            + "SELECT t3.balance FROM CR_CUST_CR_ACCT t2, CR_ACCT t3 WHERE (t2.ACCOUNTS_ID = t3.id) AND "
            + "(t1.id = t2.CUSTOMER_ID)))";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Order> q = cb.createQuery(Order.class);
        Root<Order> o = q.from(Order.class);
        Join<Order,Customer> c = o.join(Order_.customer);
        q.select(o);
        Subquery<Integer> sq = q.subquery(Integer.class);
        Join<Order,Customer> c1 = sq.correlate(c);
        ListJoin<Customer,Account> a = c1.join(Customer_.accounts);
        sq.select(a.get(Account_.balance));
        q.where(cb.lt(cb.literal(10000), cb.all(sq)));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries6a() {
        String jpql = "SELECT o FROM Order o JOIN o.customer c WHERE o.name = "
            + "SOME (SELECT a.name FROM c.accounts a)";
        String expectedSQL = "SELECT t0.id, t0.cnt, t4.id, t4.accountNum, t5.id, t5.city, t5.country, t5.county, "
            + "t5.state, t5.street, t6.userid, t6.DTYPE, t6.age, t6.compName, t6.creditRating, t6.name, t5.zipCode, "
            + "t4.balanceOwed, t4.creditRating, t4.filledOrderCount, t4.firstName, t4.lastName, t4.name, t4.status, "
            + "t0.delivered, t0.name, t0.orderTs, t0.quantity, t0.totalCost "
            + "FROM CR_ODR t0 INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
            + "LEFT OUTER JOIN CR_CUST t4 ON t0.CUSTOMER_ID = t4.id "
            + "LEFT OUTER JOIN CR_ADDR t5 ON t4.ADDRESS_ID = t5.id "
            + "LEFT OUTER JOIN CompUser t6 ON t5.id = t6.ADD_ID WHERE (t0.name = ANY ("
            + "SELECT t3.name FROM CR_CUST_CR_ACCT t2, CR_ACCT t3 WHERE (t2.ACCOUNTS_ID = t3.id) "
            + "AND (t1.id = t2.CUSTOMER_ID)))";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Order> q = cb.createQuery(Order.class);
        Root<Order> o = q.from(Order.class);
        Join<Order,Customer> c = o.join(Order_.customer);
        q.select(o);
        Subquery<String> sq = q.subquery(String.class);
        Join<Order,Customer> c1 = sq.correlate(c);
        ListJoin<Customer,Account> a = c1.join(Customer_.accounts);
        sq.select(a.get(Account_.name));
        q.where(cb.equal(o.get(Order_.name), cb.some(sq)));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries6b() {
        String jpql = "SELECT o FROM Order o JOIN o.customer c WHERE o.name = "
            + "SOME (SELECT a.county FROM c.address a)";
        String expectedSQL = "SELECT t0.id, t0.cnt, t3.id, t3.accountNum, t4.id, t4.city, t4.country, t4.county, "
            + "t4.state, t4.street, t5.userid, t5.DTYPE, t5.age, t5.compName, t5.creditRating, t5.name, t4.zipCode, "
            + "t3.balanceOwed, t3.creditRating, t3.filledOrderCount, t3.firstName, t3.lastName, t3.name, t3.status, "
            + "t0.delivered, t0.name, t0.orderTs, t0.quantity, t0.totalCost "
            + "FROM CR_ODR t0 INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
            + "LEFT OUTER JOIN CR_CUST t3 ON t0.CUSTOMER_ID = t3.id "
            + "LEFT OUTER JOIN CR_ADDR t4 ON t3.ADDRESS_ID = t4.id "
            + "LEFT OUTER JOIN CompUser t5 ON t4.id = t5.ADD_ID WHERE (t0.name = ANY "
            + "(SELECT t2.county FROM CR_ADDR t2 WHERE (t1.ADDRESS_ID = t2.id)))";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Order> q = cb.createQuery(Order.class);
        Root<Order> o = q.from(Order.class);
        Join<Order,Customer> c = o.join(Order_.customer);
        q.select(o);
        Subquery<String> sq = q.subquery(String.class);
        Join<Order,Customer> c1 = sq.correlate(c);
        Join<Customer,Address> a = c1.join(Customer_.address);
        sq.select(a.get(Address_.county));
        q.where(cb.equal(o.get(Order_.name), cb.some(sq)));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries6c() {
        String jpql = "SELECT o FROM Order o JOIN o.customer c WHERE 10000 < "
            + "ALL (SELECT u.age FROM c.address a JOIN a.user u)";

        String expectedSQL = "SELECT t0.id, t0.cnt, t4.id, t4.accountNum, t5.id, t5.city, t5.country, t5.county, "
            + "t5.state, t5.street, t6.userid, t6.DTYPE, t6.age, t6.compName, t6.creditRating, t6.name, t5.zipCode, "
            + "t4.balanceOwed, t4.creditRating, t4.filledOrderCount, t4.firstName, t4.lastName, t4.name, t4.status, "
            + "t0.delivered, t0.name, t0.orderTs, t0.quantity, t0.totalCost "
            + "FROM CR_ODR t0 INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
            + "LEFT OUTER JOIN CR_CUST t4 ON t0.CUSTOMER_ID = t4.id "
            + "LEFT OUTER JOIN CR_ADDR t5 ON t4.ADDRESS_ID = t5.id "
            + "LEFT OUTER JOIN CompUser t6 ON t5.id = t6.ADD_ID WHERE (? < ALL ("
            + "SELECT t3.age FROM CR_ADDR t2 INNER JOIN CompUser t3 ON t2.id = t3.ADD_ID "
            + "WHERE (t1.ADDRESS_ID = t2.id)))";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Order> q = cb.createQuery(Order.class);
        Root<Order> o = q.from(Order.class);
        Join<Order,Customer> c = o.join(Order_.customer);
        q.select(o);
        Subquery<Integer> sq = q.subquery(Integer.class);
        Join<Order,Customer> c1 = sq.correlate(c);
        Join<Customer,Address> a = c1.join(Customer_.address);
        Join<Address, CompUser> u = a.join(Address_.user);
        sq.select(u.get(CompUser_.age));
        q.where(cb.lt(cb.literal(10000), cb.all(sq)));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries6d() {
        String jpql = "SELECT o FROM Order o JOIN o.customer c JOIN c.address a WHERE 10000 < "
            + "ALL (SELECT u.age FROM c.address a1 JOIN a1.user u WHERE a.city = a1.city)";
        String expectedSQL = "SELECT t2.id, t2.cnt, t5.id, t5.accountNum, t6.id, t6.city, t6.country, t6.county, "
            + "t6.state, t6.street, t7.userid, t7.DTYPE, t7.age, t7.compName, t7.creditRating, t7.name, t6.zipCode, "
            + "t5.balanceOwed, t5.creditRating, t5.filledOrderCount, t5.firstName, t5.lastName, t5.name, t5.status, "
            + "t2.delivered, t2.name, t2.orderTs, t2.quantity, t2.totalCost "
            + "FROM CR_ODR t2 INNER JOIN CR_CUST t3 ON t2.CUSTOMER_ID = t3.id "
            + "LEFT OUTER JOIN CR_CUST t5 ON t2.CUSTOMER_ID = t5.id "
            + "INNER JOIN CR_ADDR t4 ON t3.ADDRESS_ID = t4.id "
            + "LEFT OUTER JOIN CR_ADDR t6 ON t5.ADDRESS_ID = t6.id "
            + "LEFT OUTER JOIN CompUser t7 ON t6.id = t7.ADD_ID WHERE (? < ALL ("
            + "SELECT t1.age FROM CR_ADDR t0 INNER JOIN CompUser t1 ON t0.id = t1.ADD_ID WHERE (t4.city = t0.city AND "
            + "t3.ADDRESS_ID = t0.id)) AND 1 = 1)";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Order> q = cb.createQuery(Order.class);
        Root<Order> o = q.from(Order.class);
        Join<Order,Customer> c = o.join(Order_.customer);
        Join<Customer,Address> a = c.join(Customer_.address);
        q.select(o);
        Subquery<Integer> sq = q.subquery(Integer.class);
        Join<Order,Customer> c1 = sq.correlate(c);
        Join<Customer,Address> a1 = c1.join(Customer_.address);
        Join<Address, CompUser> u = a1.join(Address_.user);
        sq.select(u.get(CompUser_.age));
        sq.where(cb.equal(a.get(Address_.city), a1.get(Address_.city)));
        q.where(cb.lt(cb.literal(10000), cb.all(sq)));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries6e() {
        String jpql = "SELECT o FROM Order o JOIN o.customer c JOIN c.address a WHERE 10000 < "
            + "ALL (SELECT u.age FROM a.user u)";

        String expectedSQL = "SELECT t0.id, t0.cnt, t5.id, t5.accountNum, t6.id, t6.city, t6.country, t6.county, "
            + "t6.state, t6.street, t7.userid, t7.DTYPE, t7.age, t7.compName, t7.creditRating, t7.name, t6.zipCode, "
            + "t5.balanceOwed, t5.creditRating, t5.filledOrderCount, t5.firstName, t5.lastName, t5.name, t5.status, "
            + "t0.delivered, t0.name, t0.orderTs, t0.quantity, t0.totalCost "
            + "FROM CR_ODR t0 INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
            + "LEFT OUTER JOIN CR_CUST t5 ON t0.CUSTOMER_ID = t5.id "
            + "INNER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id "
            + "LEFT OUTER JOIN CR_ADDR t6 ON t5.ADDRESS_ID = t6.id "
            + "LEFT OUTER JOIN CompUser t7 ON t6.id = t7.ADD_ID "
            + "WHERE (? < ALL (SELECT t4.age FROM CompUser t3, CompUser t4 "
            + "WHERE (t3.userid = t4.userid) AND (t2.id = t3.ADD_ID)) AND 1 = 1)";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Order> q = cb.createQuery(Order.class);
        Root<Order> o = q.from(Order.class);
        Join<Order,Customer> c = o.join(Order_.customer);
        Join<Customer,Address> a = c.join(Customer_.address);
        q.select(o);
        Subquery<Integer> sq = q.subquery(Integer.class);
        Join<Customer,Address> a1 = sq.correlate(a);
        Join<Address, CompUser> u = a1.join(Address_.user);
        sq.select(u.get(CompUser_.age));
        q.where(cb.lt(cb.literal(10000), cb.all(sq)));

        assertEquivalence(q, jpql);
    }

    public void testSubqueries6f() {
        String jpql = "SELECT o FROM Order o JOIN o.customer c WHERE 10000 < "
            + "ALL (SELECT u.age FROM c.address.user u)";

        String expectedSQL = "SELECT t0.id, t0.cnt, t5.id, t5.accountNum, t6.id, t6.city, t6.country, t6.county, "
            + "t6.state, t6.street, t7.userid, t7.DTYPE, t7.age, t7.compName, t7.creditRating, t7.name, t6.zipCode, "
            + "t5.balanceOwed, t5.creditRating, t5.filledOrderCount, t5.firstName, t5.lastName, t5.name, t5.status, "
            + "t0.delivered, t0.name, t0.orderTs, t0.quantity, t0.totalCost "
            + "FROM CR_ODR t0 INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
            + "LEFT OUTER JOIN CR_CUST t5 ON t0.CUSTOMER_ID = t5.id "
            + "LEFT OUTER JOIN CR_ADDR t6 ON t5.ADDRESS_ID = t6.id "
            + "LEFT OUTER JOIN CompUser t7 ON t6.id = t7.ADD_ID "
            + "WHERE (? < ALL (SELECT t4.age "
            + "FROM CR_ADDR t2 INNER JOIN CompUser t3 ON t2.id = t3.ADD_ID, CompUser t4 "
            + "WHERE (t3.userid = t4.userid) AND (t1.ADDRESS_ID = t2.id)))";

        executeAndCompareSQL(jpql, expectedSQL);
    }

    public void testSubqueries6g() {
        String jpql = "SELECT o FROM Order o JOIN o.customer c WHERE 10000 < "
            + "ALL (SELECT u.age FROM c.address a JOIN a.user u)";

        String expectedSQL = "SELECT t0.id, t0.cnt, t4.id, t4.accountNum, t5.id, t5.city, t5.country, t5.county, "
            + "t5.state, t5.street, t6.userid, t6.DTYPE, t6.age, t6.compName, t6.creditRating, t6.name, t5.zipCode, "
            + "t4.balanceOwed, t4.creditRating, t4.filledOrderCount, t4.firstName, t4.lastName, t4.name, t4.status, "
            + "t0.delivered, t0.name, t0.orderTs, t0.quantity, t0.totalCost "
            + "FROM CR_ODR t0 "
            + "INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
            + "LEFT OUTER JOIN CR_CUST t4 ON t0.CUSTOMER_ID = t4.id "
            + "LEFT OUTER JOIN CR_ADDR t5 ON t4.ADDRESS_ID = t5.id "
            + "LEFT OUTER JOIN CompUser t6 ON t5.id = t6.ADD_ID WHERE (? < ALL ("
            + "SELECT t3.age "
            + "FROM CR_ADDR t2 "
            + "INNER JOIN CompUser t3 ON t2.id = t3.ADD_ID WHERE (t1.ADDRESS_ID = t2.id)))";

        executeAndCompareSQL(jpql, expectedSQL);

        CriteriaQuery<Order> q = cb.createQuery(Order.class);
        Root<Order> o = q.from(Order.class);
        Join<Order,Customer> c = o.join(Order_.customer);
        q.select(o);
        Subquery<Integer> sq = q.subquery(Integer.class);
        Join<Order,Customer> c1 = sq.correlate(c);
        Join<Address, CompUser> u = c1.join(Customer_.address).join(Address_.user);
        sq.select(u.get(CompUser_.age));
        q.where(cb.lt(cb.literal(10000), cb.all(sq)));

        assertEquivalence(q, jpql);
    }

    // redundant t3
    // compare to 6e, t3 should be in main query for LEFT OUTER JOIN
    public void testSubqueries6h() {
        String jpql = "SELECT o FROM Order o JOIN o.customer.address a WHERE 10000 < "
            + "ALL (SELECT u.age FROM a.user u)";
        String expectedSQL = "SELECT t0.id, t0.cnt, t1.id, t1.accountNum, t6.id, t6.city, t6.country, t6.county, "
            + "t6.state, t6.street, t7.userid, t7.DTYPE, t7.age, t7.compName, t7.creditRating, t7.name, t6.zipCode, "
            + "t1.balanceOwed, t1.creditRating, t1.filledOrderCount, t1.firstName, t1.lastName, t1.name, t1.status, "
            + "t0.delivered, t0.name, t0.orderTs, t0.quantity, t0.totalCost "
            + "FROM CR_ODR t0 INNER JOIN CR_CUST t1 ON t0.CUSTOMER_ID = t1.id "
            + "INNER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id "
            + "LEFT OUTER JOIN CR_ADDR t6 ON t1.ADDRESS_ID = t6.id "
            + "LEFT OUTER JOIN CompUser t7 ON t6.id = t7.ADD_ID WHERE (? < ALL ("
            + "SELECT t5.age FROM CR_CUST t3, CompUser t4, CompUser t5 "
            + "WHERE (t4.userid = t5.userid) AND (t0.CUSTOMER_ID = t3.id) AND (t2.id = t4.ADD_ID)))";

        executeAndCompareSQL(jpql, expectedSQL);
    }

    public void testExist1() {
        String jpql = "SELECT DISTINCT c.name FROM CompUser c WHERE EXISTS"
            + " (SELECT a FROM Address a WHERE a = c.address )";

        String expectedSQL = "SELECT DISTINCT t1.name FROM CompUser t1 WHERE (EXISTS ("
            + "SELECT t0.id FROM CR_ADDR t0 WHERE (t0.id = t1.ADD_ID)))";

