/*
 * 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.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import jakarta.persistence.Parameter;
import jakarta.persistence.Query;
import jakarta.persistence.Tuple;
import jakarta.persistence.TypedQuery;
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.MapJoin;
import jakarta.persistence.criteria.ParameterExpression;
import jakarta.persistence.criteria.Path;
import jakarta.persistence.criteria.Root;
import jakarta.persistence.criteria.Selection;
import jakarta.persistence.criteria.SetJoin;
import jakarta.persistence.criteria.Subquery;
import jakarta.persistence.metamodel.EntityType;
import jakarta.persistence.metamodel.Metamodel;

import org.apache.openjpa.jdbc.sql.AbstractSQLServerDictionary;
import org.apache.openjpa.jdbc.sql.OracleDictionary;
import org.apache.openjpa.persistence.common.utils.DatabaseHelper;
import org.apache.openjpa.persistence.test.AllowFailure;

/**
 * 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 TestTypesafeCriteria extends CriteriaTest {
    private static final String TRUE_JPQL = "SELECT p FROM Person p WHERE 1=1";
    private static final String FALSE_JPQL = "SELECT p FROM Person p WHERE 1<>1";

    public void testTrueLiteral() {
        CriteriaQuery<Person> q = cb.createQuery(Person.class);
        q.from(Person.class);
        assertEquivalence(q.where(cb.literal(Boolean.TRUE)), TRUE_JPQL);
    }

    public void testFalseLiteral() {
        CriteriaQuery<Person> q = cb.createQuery(Person.class);
        q.from(Person.class);
        assertEquivalence(q.where(cb.literal(Boolean.FALSE)), FALSE_JPQL);
    }

    public void testBooleanLiteralInEquals() {
        CriteriaQuery<Order> q = cb.createQuery(Order.class);
        Root<Order> root = q.from(Order.class);
        Path<Object> path = root.get("delivered");
        Expression<Boolean> literal = cb.literal(Boolean.FALSE);
        assertEquals( // we don't want o.delivered = 1 <> 1 but o.delivered = false
                "SELECT o FROM Order o WHERE o.delivered = false",
                ((OpenJPACriteriaQuery<?>) q.select(root).where(cb.equal(path, literal))).toCQL());
    }

    public void testDefaultAndIsTrue() {
        CriteriaQuery<Person> q = cb.createQuery(Person.class);
        q.from(Person.class);
        assertEquivalence(q.where(cb.and()), TRUE_JPQL);
    }

    public void testDefaultOrIsFalse() {
        CriteriaQuery<Person> q = cb.createQuery(Person.class);
        q.from(Person.class);
        assertEquivalence(q.where(cb.or()), FALSE_JPQL);
    }

    public void testZeroDisjunctIsFalse() {
        CriteriaQuery<Person> q = cb.createQuery(Person.class);
        q.from(Person.class);
        assertEquivalence(q.where(cb.disjunction()), FALSE_JPQL);
    }

    public void testZeroConjunctIsTrue() {
        CriteriaQuery<Person> q = cb.createQuery(Person.class);
        q.from(Person.class);
        assertEquivalence(q.where(cb.conjunction()), TRUE_JPQL);
    }

    public void testExpressions() {
        String jpql = "SELECT o.quantity, o.totalCost*1.08, "
                + "a.zipCode FROM Customer c JOIN c.orders o JOIN c.address a "
                + "WHERE a.state = 'CA' AND a.county = 'Santa Clara'";
        CriteriaQuery<?> q = cb.createQuery();
        Root<Customer> cust = q.from(Customer.class);
        SetJoin<Customer, Order> order = cust.joinSet("orders");
        Join<Customer, Address> address = cust.join("address");
        q.where(cb.equal(address.get("state"), "CA"), cb.equal(address
            .get("county"), "Santa Clara"));
        Expression<Double> taxedCost = cb.prod(order.get(Order_.totalCost), 1.08);
        q.multiselect(order.get("quantity"), taxedCost, address.get("zipCode"));

        assertEquivalence(q, jpql);
    }
    public void testExplictRoot() {
        String jpql = "select a from Account a";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);
        c.select(account);

        assertEquivalence(c, jpql);
    }

    public void testImplicitRoot() {
        String jpql = "select a from Account a";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        c.from(Account.class);

        assertEquivalence(c, jpql);
    }

    public void testEqualWithAttributeAndLiteral() {
        String jpql = "select a from Account a where a.balance=100";

        CriteriaQuery c = cb.createQuery();
        Root<Account> account = c.from(Account.class);
        c.select(account).where(cb.equal(account.get(Account_.balance), 100));

        assertEquivalence(c, jpql);
    }

    public void testEqualWithAttributeAndAttribute() {
        String jpql = "select a from Account a where a.balance=a.loan";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);
        c.select(account).where(
                cb.equal(account.get(Account_.balance), account
                        .get(Account_.loan)));

        assertEquivalence(c, jpql);
    }

    public void testProjection() {
        String jpql = "select a.balance,a.loan from Account a";
        CriteriaQuery<Tuple> c = cb.createTupleQuery();
        Root<Account> account = c.from(Account.class);
        c.multiselect(account.get(Account_.balance), account.get(Account_.loan));
        assertEquivalence(c, jpql);
    }

    public void testAbsExpression() {
        String jpql = "select a from Account a where abs(a.balance)=100";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);

        c.select(account).where(cb.equal(cb.abs(account.get(Account_.balance)), 100));
        assertEquivalence(c, jpql);
    }

    public void testCeilingExpression() {
        String jpql = "select a from Account a where ceiling(a.balance)=100";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);

        c.select(account).where(cb.equal(cb.ceiling(account.get(Account_.balance)), 100));
        assertEquivalence(c, jpql);
    }

    public void testExponentialExpression() {
        String jpql = "select a from Account a where exp(a.balance)=100";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);

        c.select(account).where(cb.equal(cb.exp(account.get(Account_.balance)), 100));
        assertEquivalence(c, jpql);
    }

    public void testFloorExpression() {
        String jpql = "select a from Account a where floor(a.balance)=100";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);

        c.select(account).where(cb.equal(cb.floor(account.get(Account_.balance)), 100));
        assertEquivalence(c, jpql);
    }

    public void testLnExpression() {
        String jpql = "select a from Account a where ln(a.balance)=100";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);

        c.select(account).where(cb.equal(cb.ln(account.get(Account_.balance)), 100));
        assertEquivalence(c, jpql);
    }

    public void testSignExpression() {
        String jpql = "select a from Account a where sign(a.balance)=1";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);

        c.select(account).where(cb.equal(cb.sign(account.get(Account_.balance)), 1));
        assertEquivalence(c, jpql);
    }

    public void testPowerExpression() {
        DatabaseHelper.createPowerFunctionIfNecessary(getEntityManager(), getDictionary());
        String jpql = "select a from Account a where power(a.balance, 7)=1";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);

        c.select(account).where(cb.equal(cb.power(account.get(Account_.balance), 7), 1));
        assertEquivalence(c, jpql);
        DatabaseHelper.dropPowerFunction(getEntityManager(), getDictionary());
    }

    public void testRoundExpression() {
        DatabaseHelper.createRoundFunctionIfNecessary(getEntityManager(), getDictionary());
        String jpql = "select a from Account a where round(a.balance, 1)=1";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);

        c.select(account).where(cb.equal(cb.round(account.get(Account_.balance), 1), 1));
        assertEquivalence(c, jpql);
        DatabaseHelper.dropRoundFunction(getEntityManager(), getDictionary());
    }

    public void testAvgExpression() {
        String jpql = "select avg(a.balance) from Account a";

        CriteriaQuery<Double> c = cb.createQuery(Double.class);
        Root<Account> account = c.from(Account.class);

        c.select(cb.avg(account.get(Account_.balance)));
        assertEquivalence(c, jpql);
    }

    public void testInPredicate() {
        String jpql = "select a from Account a where a.name in ('X','Y','Z')";
        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);
        c.where(cb.in(account.get(Account_.name)).value("X").value("Y").value("Z"));
        assertEquivalence(c, jpql);
    }

    public void testInPredicateWithPath() {
        String jpql = "select a from Account a where a.owner.name in ('X','Y','Z')";
        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);
        c.where(cb.in(account.get(Account_.owner).get(Person_.name)).value("X")
                .value("Y").value("Z"));
        assertEquivalence(c, jpql);
    }

    public void testBinaryPredicate() {
        String jpql = "select a from Account a where a.balance>100 and a.balance<200";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);
        c.select(account).where(
                cb.and(cb.greaterThan(account.get(Account_.balance), 100), cb
                        .lessThan(account.get(Account_.balance), 200)));

        assertEquivalence(c, jpql);
    }

    public void testEqualWithAttributeAndUnaryExpression() {
        String jpql = "select a from Account a where a.balance=abs(a.balance)";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);
        c.select(account).where(
                cb.equal(account.get(Account_.balance), cb.abs(account
                        .get(Account_.balance))));

        assertEquivalence(c, jpql);
    }

    public void testBetweenExpression() {
        String jpql =
            "select a from Account a where a.balance between 100 and 200";

        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> account = c.from(Account.class);
        c.select(account).where(
                cb.between(account.get(Account_.balance), 100, 200));

        assertEquivalence(c, jpql);
    }

    public void testSimplePath() {
        String jpql = "select a from Account a where a.owner.name='Pinaki'";
        CriteriaQuery<Account> c = cb.createQuery(Account.class);
        Root<Account> a = c.from(Account.class);
        c.where(cb.equal(a.get(Account_.owner).get(Person_.name), "Pinaki"));

        assertEquivalence(c, jpql);
    }

    public void testSimpleLeftJoin() {
        String jpql = "SELECT c FROM Customer c LEFT JOIN c.orders o ";
        CriteriaQuery<Customer> c = cb.createQuery(Customer.class);
        c.from(Customer.class).join(Customer_.orders, JoinType.LEFT);
        assertEquivalence(c, jpql);
    }

    public void testMultipartNavigation() {
        String jpql = "select a from A a where a.b.age=22";

        CriteriaQuery<A> cq = cb.createQuery(A.class);
        Root<A> a = cq.from(A.class);
        cq.where(cb.equal(a.get(A_.b).get(B_.age), 22));

        assertEquivalence(cq, jpql);
    }

    public void testMultiLevelJoins() {
        String jpql = "SELECT c FROM Customer c JOIN c.orders o "
                    + "JOIN o.lineItems i WHERE i.product.productType = 'printer'";

        CriteriaQuery<Customer> cq = cb.createQuery(Customer.class);
        Root<Customer> c = cq.from(Customer.class);
        Join<Customer, Order> o = c.join(Customer_.orders);
        Join<Order, LineItem> i = o.join(Order_.lineItems);
        cq.select(c)
            .where(cb.equal(i.get(LineItem_.product)
            .get(Product_.productType), "printer"));

        assertEquivalence(cq, jpql);
    }

    public void testJoinsNotPresentInWhereClause() {
        String jpql = "SELECT c FROM Customer c LEFT JOIN c.orders o WHERE c.status = 1";

        CriteriaQuery<Customer> c = cb.createQuery(Customer.class);
        Root<Customer> cust = c.from(Customer.class);
        Join<Customer, Order> order = cust
        .join(Customer_.orders, JoinType.LEFT);
        c.where(cb.equal(cust.get(Customer_.status), 1)).select(cust);

        assertEquivalence(c, jpql);
    }

    public void testFetchJoins() {
        String jpql = "SELECT d FROM Department d LEFT JOIN FETCH d.employees WHERE d.deptNo = 1";

        CriteriaQuery<Department> q = cb.createQuery(Department.class);
        Root<Department> d = q.from(Department.class);
        d.fetch(Department_.employees, JoinType.LEFT);
        q.where(cb.equal(d.get(Department_.deptNo), 1)).select(d);

        assertEquivalence(q, jpql);
    }

    public void testJoinedPathInProjection() {
        String jpql = "SELECT p.vendor FROM Employee e JOIN e.contactInfo c JOIN c.phones p "
                    + "WHERE c.address.zipCode = '95054'";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<Employee> e = cq.from(Employee.class);
        Join<Contact, Phone> p = e.join(Employee_.contactInfo).join(Contact_.phones);
        cq.where(cb.equal(e.get(Employee_.contactInfo).get(Contact_.address)
                .get(Address_.zipCode), "95054"));
        cq.select(p.get(Phone_.vendor));

        assertEquivalence(cq, jpql);
    }

    public void testKeyExpression() {
        String jpql = "select i.name, VALUE(p) from Item i join i.photos p where KEY(p) like 'egret'";

        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<Item> item = q.from(Item.class);
        MapJoin<Item, String, Photo> photo = item.join(Item_.photos);
        q.multiselect(item.get(Item_.name), photo).where(cb.like(photo.key(), "%egret%"));

        assertEquivalence(q, jpql);
    }

    public void testIndexExpression() {
        String jpql = "SELECT t FROM CreditCard c JOIN c.transactionHistory t "
            + "WHERE c.customer.accountNum = 321987 AND INDEX(t) BETWEEN 0 AND 9";

        CriteriaQuery<TransactionHistory> q = cb.createQuery(TransactionHistory.class);
        Root<CreditCard> c = q.from(CreditCard.class);
        ListJoin<CreditCard, TransactionHistory> t = c
        .join(CreditCard_.transactionHistory);
        q.select(t).where(cb.and(
            cb.equal(c.get(CreditCard_.customer).get(Customer_.accountNum),
                321987),
            cb.between(t.index(), 0, 9)));

        assertEquivalence(q, jpql);
    }

    public void testIsEmptyExpression() {
        String jpql = "SELECT o FROM Order o WHERE o.lineItems IS EMPTY";

        CriteriaQuery<Order> q = cb.createQuery(Order.class);
        Root<Order> order = q.from(Order.class);
        q.where(cb.isEmpty(order.get(Order_.lineItems))).select(order);

        assertEquivalence(q, jpql);
    }

    public void testExpressionInProjection() {
        String jpql = "SELECT o.quantity, o.totalCost*1.08, "
            + "a.zipCode FROM Customer c JOIN c.orders o JOIN c.address a "
            + "WHERE a.state = 'CA' AND a.county = 'Santa Clara'";

        CriteriaQuery<Tuple> cq = cb.createTupleQuery();
        Root<Customer> c = cq.from(Customer.class);
        Join<Customer, Order> o = c.join(Customer_.orders);
        Join<Customer, Address> a = c.join(Customer_.address);
        cq.where(cb.and(
            cb.equal(a.get(Address_.state), "CA"),
            cb.equal(a.get(Address_.county), "Santa Clara")));
        cq.multiselect(o.get(Order_.quantity), cb.prod(o
                .get(Order_.totalCost), 1.08), a.get(Address_.zipCode));

        assertEquivalence(cq, jpql);
    }

    public void testTypeExpression() {
        String jpql = "SELECT TYPE(e) FROM Employee e WHERE TYPE(e) <> Exempt";

        CriteriaQuery<Object> q = cb.createQuery();
        Root<Employee> emp = q.from(Employee.class);
        q.multiselect(emp.type()).where(cb.notEqual(emp.type(), Exempt.class));

        assertEquivalence(q, jpql);
    }

    public void testIndexExpressionAndLietral() {
        String jpql = "SELECT w.name FROM Course c JOIN c.studentWaitList w "
                    + "WHERE c.name = 'Calculus' AND INDEX(w) = 0";

        CriteriaQuery<String> q = cb.createQuery(String.class);
        Root<Course> course = q.from(Course.class);
        ListJoin<Course, Student> w = course.join(Course_.studentWaitList);
        q.where(cb.and(
            cb.equal(course.get(Course_.name), "Calculus"),
            cb.equal(w.index(), 0))).select(w.get(Student_.name));

        assertEquivalence(q, jpql);
    }

    public void testAggregateInProjection() {
        String jpql = "SELECT SUM(i.price) FROM Order o JOIN o.lineItems i " +
            "JOIN o.customer c WHERE c.lastName = 'Smith' AND " +
            "c.firstName = 'John'";

        CriteriaQuery<Double> q = cb.createQuery(Double.class);
        Root<Order> o = q.from(Order.class);
        Join<Order, LineItem> i = o.join(Order_.lineItems);
        Join<Order, Customer> c = o.join(Order_.customer);
        q.where(cb.and(
            cb.equal(c.get(Customer_.lastName), "Smith"),
            cb.equal(c.get(Customer_.firstName), "John")));
        q.select(cb.sum(i.get(LineItem_.price)));

        assertEquivalence(q, jpql);
    }

    public void testSizeExpression() {
        String jpql = "SELECT SIZE(d.employees) FROM Department d "
            + "WHERE d.name = 'Sales'";

        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<Department> d = q.from(Department.class);
        q.where(cb.equal(d.get(Department_.name), "Sales"));
        q.select(cb.size(d.get(Department_.employees)));

        assertEquivalence(q, jpql);
    }

    public void testCaseExpression() {
        String jpql = "SELECT e.name, CASE "
            + "WHEN e.rating = 1 THEN e.salary * 1.1 "
                + "WHEN e.rating = 2 THEN e.salary * 1.2 "
                + "ELSE e.salary * 1.01 END "
            + "FROM Employee e WHERE e.department.name = 'Engineering'";
        CriteriaQuery<?> q = cb.createQuery();
        Root<Employee> e = q.from(Employee.class);
        q.where(cb.equal(e.get(Employee_.department).get(Department_.name), "Engineering"));
        q.multiselect(e.get(Employee_.name),
                cb.selectCase()
                  .when(cb.equal(e.get(Employee_.rating), 1), cb.prod(e.get(Employee_.salary), 1.1))
                  .when(cb.equal(e.get(Employee_.rating), 2), cb.prod(e.get(Employee_.salary), 1.2))
                  .otherwise(cb.prod(e.get(Employee_.salary), 1.01)));

        assertEquivalence(q, jpql);
    }

    public void testExpression1() {
        String jpql = "SELECT o.quantity, o.totalCost*1.08, "
            + "a.zipCode FROM Customer c JOIN c.orders o JOIN c.address a "
            + "WHERE a.state = 'CA' AND a.county = 'Santa Clara'";

        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<Customer> cust = q.from(Customer.class);
        Join<Customer, Order> order = cust.join(Customer_.orders);
        Join<Customer, Address> address = cust.join(Customer_.address);
        q.where(cb.and(
            cb.equal(address.get(Address_.state), "CA"),
            cb.equal(address.get(Address_.county), "Santa Clara")));
        q.multiselect(order.get(Order_.quantity), cb.prod(order
                .get(Order_.totalCost), 1.08), address.get(Address_.zipCode));

        assertEquivalence(q, jpql);
    }

    public void testExpression3() {
        String jpql = "SELECT w.name FROM Course c JOIN c.studentWaitList w "
            + "WHERE c.name = 'Calculus' AND INDEX(w) = 0";

        CriteriaQuery<String> q = cb.createQuery(String.class);
        Root<Course> course = q.from(Course.class);
        ListJoin<Course, Student> w = course.join(Course_.studentWaitList);
        q.where(cb.and(
            cb.equal(course.get(Course_.name), "Calculus"),
            cb.equal(w.index(), 0))).select(w.get(Student_.name));

        assertEquivalence(q, jpql);
    }

    public void testGeneralCaseExpression() {
        String jpql = "SELECT e.name, CASE "
            + "WHEN e.rating = 1 THEN e.salary * 1.1 "
            + "WHEN e.rating = 2 THEN e.salary * 1.2 ELSE e.salary * "
            + "1.01 END "
            + "FROM Employee e WHERE e.department.name = 'Engineering'";
        CriteriaQuery<?> q = cb.createQuery();
        Root<Employee> e = q.from(Employee.class);
        q.where(cb.equal(e.get(Employee_.department).get(Department_.name), "Engineering"));
        q.multiselect(e.get(Employee_.name),
                cb.selectCase()
                  .when(cb.equal(e.get(Employee_.rating), 1), cb.prod(e.get(Employee_.salary), 1.1))
                  .when(cb.equal(e.get(Employee_.rating), 2), cb.prod(e.get(Employee_.salary), 1.2))
                  .otherwise(cb.prod(e.get(Employee_.salary), 1.01)));

        assertEquivalence(q, jpql);
    }

    public void testSimpleCaseExpression1() {
        String jpql = "SELECT e.name, CASE e.rating "
            + "WHEN 1 THEN e.salary * 1.1 "
            + "WHEN 2 THEN e.salary * 1.2 ELSE e.salary * 1.01 END "
            + "FROM Employee e WHERE e.department.name = 'Engineering'";
        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<Employee> e = q.from(Employee.class);
        q.where(cb.equal(e.get(Employee_.department).get(Department_.name), "Engineering"));
        Expression<Long> salary = e.get(Employee_.salary);
        Expression<Integer> rating  = e.get(Employee_.rating);
        q.multiselect(e.get(Employee_.name),
                      cb.selectCase(rating).
                              when(1, cb.prod(salary, 1.1))
                             .when(2, cb.prod(salary, 1.2))
                             .otherwise(cb.prod(salary, 1.01)));

        assertEquivalence(q, jpql);
    }

    public void testSimpleCaseExpression2() {
        String jpql = "SELECT e.name, CASE e.rating WHEN 1 THEN 10 "
            + "WHEN 2 THEN 20 ELSE 30 END "
            + "FROM Employee e WHERE e.department.name = 'Engineering'";
        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<Employee> e = q.from(Employee.class);
        Expression<Integer> rating  = e.get(Employee_.rating);
        q.where(cb.equal(e.get(Employee_.department).get(Department_.name), "Engineering"));
        q.multiselect(e.get(Employee_.name),
                cb.selectCase(rating)
                  .when(1, 10)
                  .when(2, 20)
                  .otherwise(30));
        assertEquivalence(q, jpql);
    }

    public void testLiterals() {
        String jpql = "SELECT p FROM Person p where 'Joe' MEMBER OF p.nickNames";
        CriteriaQuery<Person> q = cb.createQuery(Person.class);
        Root<Person> p = q.from(Person.class);
        q.select(p).where(cb.isMember(cb.literal("Joe"), p.get(Person_.nickNames)));

        assertEquivalence(q, jpql);
    }

    public void testParameters1() {
        String jpql = "SELECT c FROM Customer c Where c.status = :stat";

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        Parameter<Integer> param = cb.parameter(Integer.class, "stat");
        q.select(c).where(cb.equal(c.get(Customer_.status), param));

        assertEquivalence(new QueryDecorator() {
            @Override
            public void decorate(Query q) {
                q.setParameter("stat", 1);
            }
        }, q, jpql);
    }

    public void testParameters2() {
        String jpql = "SELECT c FROM Customer c Where c.status = :stat AND c.name = :name";

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        Parameter<Integer> param1 = cb.parameter(Integer.class, "stat");
        Parameter<String> param2 = cb.parameter(String.class, "name");
        q.select(c).where(cb.and(cb.equal(c.get(Customer_.status), param1),
                cb.equal(c.get(Customer_.name), param2)));

        assertEquivalence(new QueryDecorator() {
            @Override
            public void decorate(Query q) {
                q.setParameter("stat", 1);
                q.setParameter("name", "test");
            }
        }, q, jpql);
    }

    public void testParameters_wo_paramName() {
        final int rand = new Random().nextInt();
        final String name = "testName_" + rand;
        final String lastName = "lastName_" + rand;
        em.getTransaction().begin();
        Customer cNew = new Customer();
        cNew.setName(name);
        cNew.setLastName(lastName);
        cNew.setStatus(4711);
        em.persist(cNew);
        em.getTransaction().commit();

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        Parameter<String> paramName = cb.parameter(String.class);
        Parameter<String> paramLastName = cb.parameter(String.class);
        q.select(c).where(cb.and(cb.equal(c.get(Customer_.lastName), paramLastName),
                cb.equal(c.get(Customer_.name), paramName)));

        final TypedQuery<Customer> query = em.createQuery(q);
        query.setParameter(paramName, name);
        query.setParameter(paramLastName, lastName);

        System.err.println("CQ: " + query.toString());

        final List<Customer> customers = query.getResultList();
        assertNotNull(customers);
        assertEquals(1, customers.size());
    }

    public void testParameters3() {
        String jpql = "SELECT c FROM Customer c Where c.status = :stat";

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        Parameter<Integer> param = cb.parameter(Integer.class, "stat");
        q.select(c).where(cb.equal(c.get(Customer_.status), param));

        assertEquivalence(new QueryDecorator() {
            @Override
            public void decorate(Query q) {
                q.setParameter("stat", 1);
            }
        }, q, jpql);
    }

    public void testParameters4() {
        String jpql = "SELECT c FROM Customer c Where c.status = :stat AND c.name = :name";

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        Parameter<Integer> param1 = cb.parameter(Integer.class, "stat");
        Parameter<String> param2 = cb.parameter(String.class, "name");
        q.select(c).where(cb.and(cb.equal(c.get(Customer_.status), param1),
                cb.equal(c.get(Customer_.name), param2)));
        assertEquivalence(new QueryDecorator() {
            @Override
            public void decorate(Query q) {
                q.setParameter("stat", 1);
                q.setParameter("name", "test");
            }
        }, q, jpql);
    }

    public void testParameters5() {
        String jpql = "SELECT c FROM Customer c Where c.status IN :coll";

        CriteriaQuery<Customer> q = cb.createQuery(Customer.class);
        Root<Customer> c = q.from(Customer.class);
        ParameterExpression<List> param1 = cb.parameter(List.class, "coll");
        q.where(c.get(Customer_.status).in(param1));
        q.select(c);
        final List vals = new ArrayList();
        vals.add(1);
        vals.add(2);
        assertEquivalence(new QueryDecorator() {
            @Override
            public void decorate(Query q) {
                q.setParameter("coll", vals);
            }
        }, q, jpql);
    }

    public void testSelectList1() {
        String jpql = "SELECT v.location.street, KEY(i).title, VALUE(i) FROM "
            + "VideoStore v JOIN v.videoInventory i WHERE v.location.zipCode = "
            + "'94301' AND VALUE(i) > 0";

        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<VideoStore> v = q.from(VideoStore.class);
        MapJoin<VideoStore, Movie, Integer> i = v.join(VideoStore_.videoInventory);
        q.where(cb.and(
        cb.equal(v.get(VideoStore_.location).get(Address_.zipCode), "94301"),
        cb.gt(i.value(), 0)));
        q.multiselect(v.get(VideoStore_.location).get(Address_.street),
                i.key().get(Movie_.title),
                i.value());

        assertEquivalence(q, jpql);
    }

    public void testNewConstruct() {
        String jpql = "SELECT NEW CustomerDetails(c.id, c.status) FROM Customer c";

        CriteriaQuery<CustomerDetails> q = cb.createQuery(CustomerDetails.class);

        Root<Customer> c = q.from(Customer.class);
        q.select(cb.construct(CustomerDetails.class, c.get(Customer_.id), c.get(Customer_.status)));
        assertEquivalence(q, jpql);
    }

    public void testConstructorInProjection() {
        String jpql = "SELECT NEW CustomerDetails(c.id, c.status, o.quantity) "
                    + "FROM Customer c JOIN c.orders o WHERE o.quantity > 100";

        CriteriaQuery<CustomerDetails> q = cb.createQuery(CustomerDetails.class);
        Root<Customer> c = q.from(Customer.class);
        SetJoin<Customer, Order> o = c.join(Customer_.orders);
        q.where(cb.gt(o.get(Order_.quantity), 100));
        q.select(cb.construct(CustomerDetails.class,
                            c.get(Customer_.id),
                            c.get(Customer_.status),
                            o.get(Order_.quantity)));

        assertEquivalence(q, jpql);
    }

    public void testMultipleConstructorInProjection() {
        String jpql = "SELECT NEW CustomerDetails(c.id, c.status), "
                    + "NEW CustomerFullName(c.firstName, c.lastName) "
                    + "FROM Customer c";

        CriteriaQuery<?> q = cb.createQuery();
        Root<Customer> c = q.from(Customer.class);
        q.multiselect(cb.construct(CustomerDetails.class,
                             c.get(Customer_.id),
                             c.get(Customer_.status)),
                cb.construct(CustomerFullName.class,
                             c.get(Customer_.firstName),
                             c.get(Customer_.lastName))
        );
        em.createQuery(q).getResultList();

        // assertEquivalence(q, jpql);
    }


    public void testSubqueries1() {
        String jpql = "SELECT goodCustomer FROM Customer goodCustomer WHERE "
            + "goodCustomer.balanceOwed < (SELECT AVG(c.balanceOwed) "
            + " FROM "
            + "Customer c)";
