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

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

/**
 * Tests type-strict version of Criteria API. The test scenarios are adapted
 * from TestEJBQLCondExpression in
 * org.apache.openjpa.persistence.jpql.expressions and TestEJBQLFunction in
 * org.apache.openjpa.persistence.jpql.functions.
 *
 */

public class TestTypeSafeCondExpression extends CriteriaTest {

    public void testNothingUsingCriteria() {
        String query = "SELECT o FROM CompUser o";

        CriteriaQuery<CompUser> cq = cb.createQuery(CompUser.class);
        cq.select(cq.from(CompUser.class));
        assertEquivalence(cq, query);
    }

    public void testBetween() {
        String jpql = "SELECT o.name FROM CompUser o "
                    + "WHERE o.age BETWEEN 19 AND 40 AND o.computerName = 'PC'";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> o = cq.from(CompUser.class);
        cq.where(cb.and(cb.between(o.get(CompUser_.age), 19, 40),
                cb.equal(o.get(CompUser_.computerName), "PC")));
        cq.select(o.get(CompUser_.name));

        assertEquivalence(cq, jpql);
    }

    public void testNotBetween() {
        String jpql = "SELECT o.name FROM CompUser o WHERE o.age NOT BETWEEN 19 AND 40 AND o.computerName= 'PC'";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> o = cq.from(CompUser.class);
        cq.where(cb.and(cb.between(o.get(CompUser_.age), 19, 40).not(),
                cb.equal(o.get(CompUser_.computerName), "PC")));
        cq.select(o.get(CompUser_.name));

        assertEquivalence(cq, jpql);
    }

    public void testInExpr() {
        String jpql = "SELECT o.name FROM CompUser o WHERE o.age IN (29, 40, 10)";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> o = cq.from(CompUser.class);
        cq.where(cb.in(o.get(CompUser_.age)).value(29).value(40).value(10));
        cq.select(o.get(CompUser_.name));

        assertEquivalence(cq, jpql);
    }

    public void testNotIn() {
        String jpql = "SELECT o.name FROM CompUser o WHERE o.age NOT IN (29, 40, 10)";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> o = cq.from(CompUser.class);
        cq.where(cb.in(o.get(CompUser_.age)).value(29).value(40).value(10)
            .not());
        cq.select(o.get(CompUser_.name));

        assertEquivalence(cq, jpql);
    }

    public void testLike1() {
        String jpql = "SELECT o.computerName FROM CompUser o "
                 + "WHERE o.name LIKE 'Sha%' AND o.computerName NOT IN ('PC','Laptop')";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> o = cq.from(CompUser.class);
        cq.where(cb.and(
                    cb.like(o.get(CompUser_.name),"Sha%"),
                    cb.in(o.get(CompUser_.computerName)).value("PC").value("Laptop").not()
                ));

        cq.select(o.get(CompUser_.computerName));

        assertEquivalence(cq, jpql);
    }

    public void testLike2() {
        String jpql = "SELECT o.computerName FROM CompUser o "
            + "WHERE o.name LIKE 'Sha%o_' AND o.computerName NOT IN ('UNIX','DOS')";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> o = cq.from(CompUser.class);
        cq.where(cb.and(
                    cb.like(o.get(CompUser_.name),"Sha%o_"),
                    cb.in(o.get(CompUser_.computerName)).value("UNIX").value("DOS").not()
                ));
        cq.select(o.get(CompUser_.computerName));

        assertEquivalence(cq, jpql);
    }

    public void testLike3() {
        String jpql = "SELECT o.name FROM CompUser o WHERE o.name LIKE '_J%'";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> o = cq.from(CompUser.class);
        cq.where(cb.like(o.get(CompUser_.name),"_J%"));
        cq.select(o.get(CompUser_.name));

        assertEquivalence(cq, jpql);
    }

    public void testLikeWithEscapeCharacter() {
        String query = "SELECT o.name FROM CompUser o WHERE o.name LIKE :name ESCAPE '|'";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> c = cq.from(CompUser.class);
        ParameterExpression<String> param = cb.parameter(String.class, "name");
        cq.where(cb.like(c.get(CompUser_.name), param, '|'));
        cq.select(c.get(CompUser_.name));

        assertEquivalence(new QueryDecorator() {
            @Override
            public void decorate(Query q) {
                q.setParameter("name", "%|_%");
            }
        }, cq, query);
    }

    public void testNullExpression() {
        String query = "SELECT o.name FROM CompUser o "
                     + "WHERE o.age IS NOT NULL AND o.computerName = 'PC'";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> c = cq.from(CompUser.class);
        cq.where(cb.and(cb.notEqual(c.get(CompUser_.age), null),
                cb.equal(c.get(CompUser_.computerName), "PC")));
        cq.select(c.get(CompUser_.name));

        assertEquivalence(cq, query);
    }

    public void testNullExpr2UsingCriteria() {
        String query =
            "SELECT o.name FROM CompUser o WHERE o.address.country IS NULL";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> c = cq.from(CompUser.class);
        cq.where(cb.equal(c.get(CompUser_.address).get(Address_.country),
            null));
        cq.select(c.get(CompUser_.name));

        assertEquivalence(cq, query);
    }

    public void testIsEmptyExprUsingCriteria() {
        String query = "SELECT o.name FROM CompUser o WHERE o.nicknames IS NOT EMPTY";

        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> o = cq.from(CompUser.class);
        cq.select(o.get(CompUser_.name));
        cq.where(cb.isEmpty(o.get(CompUser_.nicknames)).not());
        assertEquivalence(cq, query);
    }

    public void testConstructorExprUsingCriteria() {
        String query = "SELECT NEW org.apache.openjpa.persistence.criteria.MaleUser(" +
            "c.name, c.computerName, c.address, c.age, c.userid)" +
            " FROM CompUser c WHERE c.name = 'Seetha'";

        CriteriaQuery<MaleUser> cq = cb.createQuery(MaleUser.class);
        Root<CompUser> c = cq.from(CompUser.class);
        cq.where(cb.equal(c.get(CompUser_.name), "Seetha"));
        cq.select(cb.construct(MaleUser.class, c.get(CompUser_.name),
            c.get(CompUser_.computerName), c.get(CompUser_.address),
            c.get(CompUser_.age), c.get(CompUser_.userid)));

        assertEquivalence(cq, query);
   }

    public void testConcatSubStringFunc1() {
        String query = "select " +
            "CONCAT('Ablahum', SUBSTRING(e.name, LOCATE('e', e.name), 4)) " +
            "From CompUser e WHERE e.name='Seetha'";
        CriteriaQuery<String> cq = cb.createQuery(String.class);
        Root<CompUser> e = cq.from(CompUser.class);
        cq.select(
            cb.concat("Ablahum",
                cb.substring(
                    e.get(CompUser_.name),
                    cb.locate(e.get(CompUser_.name), "e"),
                    cb.literal(4)
                 )
             )
        );
        cq.where(cb.equal(e.get(CompUser_.name), "Seetha"));

        assertEquivalence(cq, query);
    }

    public void testConcatSubStringFunc2() {
        String query = "select e.address From CompUser e where " +
            "e.name = 'Seetha' AND e.computerName = " +
            "CONCAT('Ablahum', SUBSTRING(e.name, LOCATE('e', e.name), 4)) ";
        CriteriaQuery<Address> cq = cb.createQuery(Address.class);
        Root<CompUser> e = cq.from(CompUser.class);
        cq.select(e.get(CompUser_.address));
        cq.where(cb.and(cb.equal(e.get(CompUser_.name), "Seetha"),
                cb.equal(e.get(CompUser_.computerName),
                cb.concat("Ablahum",
                    cb.substring(e.get(CompUser_.name),
                    cb.locate(e.get(CompUser_.name), "e"), cb.literal(4))))));

        assertEquivalence(cq, query);
    }

    public void testConcatSubStringFunc3() {
        String query = "select " +
            "CONCAT('XYZ', SUBSTRING(e.name, LOCATE('e', e.name))) " +
            "From CompUser e WHERE e.name='Ablahumeeth'";
        CriteriaQuery<?> cq = cb.createQuery();
        Root<CompUser> e = cq.from(CompUser.class);
        cq.multiselect(cb.concat("XYZ", cb.substring(e.get(CompUser_.name),
                 cb.locate(e.get(CompUser_.name), "e"))));
        cq.where(cb.equal(e.get(CompUser_.name), "Ablahumeeth"));

        assertEquivalence(cq, query);
    }

    public void testConcatSubStringFunc4() {
        String query = "select e.age from CompUser e where " +
            "e.name = 'Seetha' AND e.computerName = " +
            "CONCAT('XYZ', SUBSTRING(e.name, LOCATE('e', e.name))) ";
        CriteriaQuery<?> q = cb.createQuery();
        Root<CompUser> e = q.from(CompUser.class);
        q.multiselect(e.get(CompUser_.age));
        q.where(cb.and(cb.equal(e.get(CompUser_.name), "Seetha"),
                cb.equal(e.get(CompUser_.computerName),
                cb.concat("XYZ", cb.substring(e.get(CompUser_.name),
                cb.locate(e.get(CompUser_.name), "e"))))));

        assertEquivalence(q, query);
    }

    public void testConcatFunc() {
        String query = "select " +
            "CONCAT('', '') From CompUser e WHERE e.name='Seetha'";
        CriteriaQuery<?> q = cb.createQuery();
        Root<CompUser> e = q.from(CompUser.class);
        q.multiselect(cb.concat("", cb.literal("")));
        q.where(cb.equal(e.get(CompUser_.name), "Seetha"));

        assertEquivalence(q, query);
    }

    public void testTrimFunc1() {
        String query = "select Trim(e.computerName) From CompUser e " +
        		"WHERE e.name='Shannon '";
        CriteriaQuery<?> q = cb.createQuery();
        Root<CompUser> e = q.from(CompUser.class);
        q.multiselect(cb.trim(e.get(CompUser_.computerName)));
        q.where(cb.equal(e.get(CompUser_.name), "Shannon "));

        assertEquivalence(q, query);
    }

    public void testTrimFunc2() {
        String query = "select e.computerName From CompUser e where " +
            "Trim(e.name) = 'Shannon '";
        CriteriaQuery<?> q = cb.createQuery();
        Root<CompUser> e = q.from(CompUser.class);
        q.where(cb.equal(cb.trim(e.get(CompUser_.name)), "Shannon"));
        q.multiselect(e.get(CompUser_.computerName));

        assertEquivalence(q, query);
    }

    public void testLowerFunc1() {
        String query = "select LOWER(e.name) From CompUser e WHERE " +
        		"e.computerName='UNIX'";
        CriteriaQuery<?> q = cb.createQuery();
        Root<CompUser> e = q.from(CompUser.class);
        q.multiselect(cb.lower(e.get(CompUser_.name)));
        q.where(cb.equal(e.get(CompUser_.computerName), "UNIX"));

        assertEquivalence(q, query);
    }

    public void testLowerFunc2() {
        String query = "select e.age From CompUser e where LOWER(e.name) ='ugo'";
        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.where(cb.equal(cb.lower(e.get(CompUser_.name)), "ugo"));
        q.select(e.get(CompUser_.age));

        assertEquivalence(q, query);
    }

    public void testUpperFunc1() {
        String query = "select UPPER(e.name) From CompUser e WHERE " +
                       "e.computerName='PC'";
        CriteriaQuery<String> q = cb.createQuery(String.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.select(cb.upper(e.get(CompUser_.name)));
        q.where(cb.equal(e.get(CompUser_.computerName), "PC"));

        assertEquivalence(q, query);
    }

    public void testUpperFunc2() {
        String query = "select e.age from CompUser e where " +
            "UPPER(e.name)='UGO'";
        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.where(cb.equal(cb.upper(e.get(CompUser_.name)), "UGO"));
        q.select(e.get(CompUser_.age));

        assertEquivalence(q, query);
    }

    public void testLengthFunc() {
        String query = "SELECT o.name FROM CompUser o " +
            "WHERE LENGTH(o.address.country) = 3";

        CriteriaQuery<String> q = cb.createQuery(String.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.where(cb.equal(cb.length(e.get(CompUser_.address).
            get(Address_.country)), 3));
        q.select(e.get(CompUser_.name));

        assertEquivalence(q, query);
    }

    public void testArithmFunc1() {
        String query = "select ABS(e.age) From CompUser e WHERE e.name='Seetha'";

        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.select(cb.abs(e.get(CompUser_.age)));
        q.where(cb.equal(e.get(CompUser_.name), "Seetha"));

        assertEquivalence(q, query);
    }

    public void testArithmFunc2() {
        String query = "select SQRT(e.age) From CompUser e WHERE e.name='Seetha'";

        CriteriaQuery<Double> q = cb.createQuery(Double.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.select(cb.sqrt(e.get(CompUser_.age)));
        q.where(cb.equal(e.get(CompUser_.name), "Seetha"));

        assertEquivalence(q, query);
    }

    public void testArithmFunc3() {
        String query = "select MOD(e.age, 4) From CompUser e WHERE e.name='Seetha'";

        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.select(cb.mod(e.get(CompUser_.age), 4));
        q.where(cb.equal(e.get(CompUser_.name), "Seetha"));

        assertEquivalence(q, query);
    }

    public void testArithmFunc4() {
        String query = "SELECT e.name FROM CompUser e WHERE SIZE(e.nicknames) = 6";

        CriteriaQuery<String> q = cb.createQuery(String.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.where(cb.equal(cb.size(e.get(CompUser_.nicknames)), 6));
        q.select(e.get(CompUser_.name));

        assertEquivalence(q, query);
    }

    public void testGroupByHavingClause() {
        String query = "SELECT c.name FROM CompUser c GROUP BY c.name HAVING c.name LIKE 'S%'";

        CriteriaQuery<String> q = cb.createQuery(String.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.groupBy(e.get(CompUser_.name));
        q.having(cb.like(e.get(CompUser_.name), "S%"));
        q.select(e.get(CompUser_.name));

        assertEquivalence(q, query);
    }

    public void testOrderByClause() {
        String query = "SELECT c.name FROM CompUser c WHERE c.name LIKE 'S%' ORDER BY c.name";

        CriteriaQuery<String> q = cb.createQuery(String.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.where(cb.like(e.get(CompUser_.name), "S%"));
        q.select(e.get(CompUser_.name));
        q.orderBy(cb.asc(e.get(CompUser_.name)));

        assertEquivalence(q, query);
    }

    public void testAVGAggregFunc() {
        //To be Tested: AVG, COUNT, MAX, MIN, SUM
        String query = "SELECT AVG(e.age) FROM CompUser e";

        CriteriaQuery<Double> q = cb.createQuery(Double.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.select(cb.avg(e.get(CompUser_.age)));

        assertEquivalence(q, query);
    }

    public void testCOUNTAggregFunc() {
        String query = "SELECT COUNT(c.name) FROM CompUser c";

        CriteriaQuery<Long> q = cb.createQuery(Long.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.select(cb.count(e.get(CompUser_.name)));

        assertEquivalence(q, query);
    }

    public void testMAXAggregFunc() {
        String query = "SELECT DISTINCT MAX(c.age) FROM CompUser c";

        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.select(cb.max(e.get(CompUser_.age))).distinct(true);

        assertEquivalence(q, query);
    }

    public void testMINAggregFunc() {
        String query = "SELECT DISTINCT MIN(c.age) FROM CompUser c";

        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.select(cb.min(e.get(CompUser_.age))).distinct(true);

        assertEquivalence(q, query);
    }

    public void testSUMAggregFunc() {
        String query = "SELECT SUM(c.age) FROM CompUser c";

        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<CompUser> e = q.from(CompUser.class);
        q.select(cb.sum(e.get(CompUser_.age)));

        assertEquivalence(q, query);
    }

    public void testTypeExpression1() {
        String jpql = "SELECT e FROM CompUser e where TYPE(e) in (:a, :b) ORDER By e.name";

        CriteriaQuery<CompUser> cq = cb.createQuery(CompUser.class);
        Root<CompUser> e = cq.from(CompUser.class);
        cq.select(e);
        Parameter<Class> param1 = cb.parameter(Class.class, "a");
        Parameter<Class> param2 = cb.parameter(Class.class, "b");
        cq.where(e.type().in(param1, param2));
        cq.orderBy(cb.asc(e.get(CompUser_.name)));

        assertEquivalence(new QueryDecorator() {
            @Override
            public void decorate(Query q) {
                q.setParameter("a", MaleUser.class);
                q.setParameter("b", FemaleUser.class);
            }
        }, cq, jpql);
    }

    public void testTypeExpression2() {
        String query = "SELECT TYPE(e) FROM CompUser e where TYPE(e) <> :t";

        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<CompUser> e = q.from(CompUser.class);
        Parameter<Class> param1 = cb.parameter(Class.class, "t");
        Expression<Class<? extends CompUser>> etype = e.type();
        // how to specify the following
        q.multiselect(e.type());
        q.where(cb.equal(e.type(), param1).not());

        assertEquivalence(new QueryDecorator() {
            @Override
            public void decorate(Query q) {
                q.setParameter("t", MaleUser.class);
            }
        }, q, query);
    }

    public void testTypeExpression3() {
        String query = "SELECT e, FemaleUser, a FROM Address a, FemaleUser e where e.address IS NOT NULL";

        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<Address> a = q.from(Address.class);
        Root<FemaleUser> e = q.from(FemaleUser.class);
        q.multiselect(e, cb.literal(FemaleUser.class), a);
        q.where(e.get(CompUser_.address).isNotNull());

        assertEquivalence(q, query);
    }

    public void testTypeExpression4() {
        String query = "SELECT e FROM CompUser e where TYPE(e) = MaleUser";

        CriteriaQuery<CompUser> cq = cb.createQuery(CompUser.class);
        Root<CompUser> e = cq.from(CompUser.class);
        cq.select(e);
        cq.where(cb.equal(e.type(), cb.literal(MaleUser.class)));

        assertEquivalence(cq, query);
    }

    public void testTypeExpression5() {
        String query = "SELECT e FROM CompUser e where TYPE(e) in (MaleUser)";

        CriteriaQuery<CompUser> cq = cb.createQuery(CompUser.class);
        Root<CompUser> e = cq.from(CompUser.class);
        cq.where(cb.in(e.type()).value(MaleUser.class));

        assertEquivalence(cq, query);
    }

    public void testTypeExpression6() {
        String query = "SELECT e FROM CompUser e where TYPE(e) not in " +
            "(MaleUser, FemaleUser)";

        CriteriaQuery<CompUser> cq = cb.createQuery(CompUser.class);
        Root<CompUser> e = cq.from(CompUser.class);
        cq.where(cb.in(e.type()).value(MaleUser.class).value(FemaleUser.class)
                .not());

        assertEquivalence(cq, query);
    }


    public void testTypeExpression7() {
        String query = "SELECT TYPE(a.b) FROM A a";
        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<A> a = q.from(A.class);
        q.multiselect(a.get(A_.b).type());

        assertEquivalence(q, query);
    }

    public void testTypeExpression8() {
        String query = "SELECT MaleUser FROM A a";

        CriteriaQuery<Class> q = cb.createQuery(Class.class);
        Root<A> a = q.from(A.class);
        q.multiselect(cb.literal(MaleUser.class));

        assertEquivalence(q, query);
    }

    public void testTypeExpression9() {
        String query = "SELECT "
                + " CASE TYPE(e) WHEN FemaleUser THEN 'Female' "
                + " ELSE 'Male' END FROM CompUser e";
        CriteriaQuery<Object> q = cb.createQuery();
        Root<CompUser> e = q.from(CompUser.class);
        q.select(cb.selectCase(e.type())
                    .when(FemaleUser.class, "Female")
                    .otherwise("Male"));

        assertEquivalence(q, query);
    }

    public void testCoalesceExpressions() {
         String query = "SELECT e.name, COALESCE (e.address.country, 'Unknown') FROM CompUser e ORDER BY e.name DESC";

        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<CompUser> e = q.from(CompUser.class);
        q.multiselect(e.get(CompUser_.name),
                      cb.coalesce().value(e.get(CompUser_.address).get(Address_.country)).value("Unknown"));
        q.orderBy(cb.desc(e.get(CompUser_.name)));

        assertEquivalence(q, query);
    }

    public void testNullIfExpressions() {
        String query = "SELECT e.name, NULLIF (e.address.country, 'USA') FROM CompUser e ORDER BY e.name DESC";

        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<CompUser> e = q.from(CompUser.class);
        q.multiselect(e.get(CompUser_.name),
                cb.nullif(e.get(CompUser_.address).get(Address_.country), "USA"));
        q.orderBy(cb.desc(e.get(CompUser_.name)));

        assertEquivalence(q, query);
    }

    public void testSimpleCaseExpression1() {
        String query = "SELECT e.name, e.age+1, "
                + "CASE e.address.country WHEN 'USA' THEN 'us' "
                + " ELSE 'non-us' END, e.address.country "
                + " FROM CompUser e";

        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<CompUser> e = q.from(CompUser.class);
        Expression<Integer> cage = cb.sum(e.get(CompUser_.age), 1);
        Expression d2 = cb.selectCase(
                e.get(CompUser_.address).get(Address_.country)).when("USA",
                "us").otherwise("non-us");
        q.multiselect(e.get(CompUser_.name), cage, d2, e.get(CompUser_.address).get(
                Address_.country));

        assertEquivalence(q, query);
    }

    public void testSimpleCaseExpression2() {
        String query = "SELECT e.name, e.age+1, "
                + "CASE e.address.country WHEN 'USA'"
                + " THEN 'United-States' "
                + " ELSE e.address.country  END," + " e.address.country "
                + " FROM CompUser e";
        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<CompUser> e = q.from(CompUser.class);
        Expression cage = cb.sum(e.get(CompUser_.age), 1);
        Expression d2 = cb.selectCase(
                e.get(CompUser_.address).get(Address_.country)).when("USA",
                "United-States").otherwise(
                e.get(CompUser_.address).get(Address_.country));
        q.multiselect(e.get(CompUser_.name), cage, d2, e.get(CompUser_.address).get(
                Address_.country));

        assertEquivalence(q, query);
    }

    public void testSimpleCaseExpression3() {
        String query = "SELECT e.name, "
                + " CASE TYPE(e) WHEN FemaleUser THEN 'Female' "
                + " ELSE 'Male' END"
                + " FROM CompUser e WHERE e.name like 'S%' "
                + " ORDER BY e.name DESC";
        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<CompUser> e = q.from(CompUser.class);
        q.multiselect(e.get(CompUser_.name),
            cb.selectCase(e.type()).when(FemaleUser.class, "Female")
            .otherwise("Male"));
        q.where(cb.like(e.get(CompUser_.name), "S%"));
        q.orderBy(cb.desc(e.get(CompUser_.name)));

        assertEquivalence(q, query);
    }

    public void testSimpleCaseExpression4() {
        String query = "SELECT e.name, CASE e.address.country WHEN 'USA'"
                + " THEN true ELSE false END,"
                + " e.address.country FROM CompUser e";
        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<CompUser> e = q.from(CompUser.class);
        Expression b = cb.selectCase(
                e.get(CompUser_.address).get(Address_.country)).when("USA",
                true).otherwise(false);
        q.multiselect(e.get(CompUser_.name), b, e.get(CompUser_.address).get(
                Address_.country));

        assertEquivalence(q, query);
    }

    public void testGeneralCaseExpression1() {
        String query = "SELECT e.name, e.age, "
                + " CASE WHEN e.age > 30 THEN e.age - 1 "
                + " WHEN e.age < 15 THEN e.age + 1 ELSE e.age + 0 "
                + " END FROM CompUser e";
        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<CompUser> e = q.from(CompUser.class);
        Expression cage = cb.selectCase().when(cb.gt(e.get(CompUser_.age), 30),
                cb.diff(e.get(CompUser_.age), 1)).when(
                cb.lt(e.get(CompUser_.age), 15),
                cb.sum(e.get(CompUser_.age), 1)).otherwise(
                cb.sum(e.get(CompUser_.age), 0));
        q.multiselect(e.get(CompUser_.name), e.get(CompUser_.age), cage);

        assertEquivalence(q, query);
    }

    public void testGeneralCaseExpression2() {
        String query = "SELECT e.name, e.age+1, "
                + "CASE WHEN e.address.country = 'USA' "
                + " THEN 'United-States' "
                + " ELSE 'Non United-States'  END,"
                + " e.address.country "
                + " FROM CompUser e";
        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<CompUser> e = q.from(CompUser.class);
        Expression d2 = cb.selectCase()
                .when(
                        cb.equal(
                                e.get(CompUser_.address).get(Address_.country),
                                "USA"), "United-States").otherwise(
                        "Non United-States");
        Expression cage = cb.sum(e.get(CompUser_.age), 1);
        q.multiselect(e.get(CompUser_.name), cage, d2, e.get(CompUser_.address).get(
                Address_.country));

        assertEquivalence(q, query);
    }

    public void testGeneralCaseExpression3() {
        String query = " select e.name, "
            + "CASE WHEN e.age = 11 THEN "
            + "org.apache.openjpa.persistence.criteria.CompUser$"
            + "CreditRating.POOR"
            + " WHEN e.age = 35 THEN "
            + "org.apache.openjpa.persistence.criteria.CompUser$"
            + "CreditRating.GOOD"
            + " ELSE "
            + "org.apache.openjpa.persistence.criteria.CompUser$"
            + "CreditRating.EXCELLENT"
            + " END FROM CompUser e ORDER BY e.age";
        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<CompUser> e = q.from(CompUser.class);
        q.multiselect(e.get(CompUser_.name), cb.selectCase().when(
                cb.equal(e.get(CompUser_.age), 11), CompUser.CreditRating.POOR)
                .when(cb.equal(e.get(CompUser_.age), 35),
                        CompUser.CreditRating.GOOD).otherwise(
                        CompUser.CreditRating.EXCELLENT));

        q.orderBy(cb.asc(e.get(CompUser_.age)));

        assertEquivalence(q, query);
    }

    // not sure how to write CriteriaQuery for
    // Subquery.select(SimpleCase/GeneralCase)

    public void testGeneralCaseExpression4() {
        String query = "select e.name, e.creditRating from CompUser e "
            + "where e.creditRating = "
            + "(select "
            + "CASE WHEN e1.age = 11 THEN "
            + "org.apache.openjpa.persistence.criteria.CompUser$"
            + "CreditRating.POOR"
            + " WHEN e1.age = 35 THEN "
            + "org.apache.openjpa.persistence.criteria.CompUser$"
            + "CreditRating.GOOD"
            + " ELSE "
            + "org.apache.openjpa.persistence.criteria.CompUser$"
            + "CreditRating.EXCELLENT"
            + " END from CompUser e1"
            + " where e.userid = e1.userid) ORDER BY e.age";
        CriteriaQuery<Tuple> q = cb.createTupleQuery();
        Root<CompUser> e = q.from(CompUser.class);
        q.multiselect(e.get(CompUser_.name), e.get(CompUser_.creditRating));
        q.orderBy(cb.asc(e.get(CompUser_.age)));
        Subquery<Object> sq = q.subquery(Object.class);
        Root<CompUser> e1 = sq.from(CompUser.class);
        sq.where(cb.equal(e.get(CompUser_.userid), e1.get(CompUser_.userid)));

        q.where(cb.equal(e.get(CompUser_.creditRating),
           sq.select(
                cb.selectCase()
                  .when(cb.equal(e1.get(CompUser_.age), 11), CompUser.CreditRating.POOR)
                  .when(cb.equal(e1.get(CompUser_.age), 35), CompUser.CreditRating.GOOD)
                  .otherwise(CompUser.CreditRating.EXCELLENT))));

        q.orderBy(cb.asc(e.get(CompUser_.age)));

        assertEquivalence(q, query);
    }
}