openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/criteria/multiselect/TestCriteriaMultiselectAliasing.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.
 */
package org.apache.openjpa.persistence.criteria.multiselect;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Tuple;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.Selection;
import javax.persistence.criteria.Subquery;

import org.apache.openjpa.persistence.test.SQLListenerTestCase;

public class TestCriteriaMultiselectAliasing extends SQLListenerTestCase {
    CriteriaQuery<Tuple> critQuery;
    String critQueryString;

    String doCB = System.getProperty("doCB");

    @Override
    public void setUp() throws Exception {
        // Only run on Oracle....the asserts at this time look for SQL specific to the
        // way things are generated for Oracle.
        setSupportedDatabases(
            org.apache.openjpa.jdbc.sql.OracleDictionary.class);
        if (isTestsDisabled()) {
            return;
        }

        super.setUp(DimDay.class, FactWorkAssignment.class
            ,"openjpa.Log","SQL=TRACE,Tests=TRACE", "openjpa.ConnectionFactoryProperties",
            "PrintParameters=true, PrettyPrint=true, PrettyPrintLineLength=72"
            );

        critQuery = createCriteriaBuilder();
        critQueryString = critQuery.toString();
        System.out.println("critQueryString = " + critQueryString);
    }

    public void test (){
        if ("true".equals(doCB)){
            this.ttestCriteriaQuery();
        }
        else{
            this.ttestGeneratedCriteriaQueryString();
        }

    }
    /**
     * This method produce wrong query like (note the extra T_DIM_DAY t3):
     * SELECT t0.empl_cnt FROM
     * T_FACT_WORK_ASGNMT t0, T_DIM_DAY t1, T_DIM_DAY t3
     * WHERE (t0.CLNT_OBJ_ID = ? AND t1.ROLL_13_MNTH_IND = ? AND t0.pers_obj_id IN (
     *     SELECT t2.pers_obj_id FROM T_FACT_WORK_ASGNMT t2 WHERE (t2.CLNT_OBJ_ID = ? AND
     *     t3.MNTH_STRT_DAY_KY >= ?)))
     * [params=(String) dummy1, (int) 1, (String) dummy1, (long) 20150201]
     *
     * The correct query should be:
     * SELECT t0.empl_cnt FROM
     * T_FACT_WORK_ASGNMT t0, T_DIM_DAY t1
     * WHERE (t0.CLNT_OBJ_ID = ? AND t1.ROLL_13_MNTH_IND = ? AND t0.pers_obj_id IN (
     *     SELECT t2.pers_obj_id FROM T_FACT_WORK_ASGNMT t2 WHERE (t2.CLNT_OBJ_ID = ? AND
     *     t3.MNTH_STRT_DAY_KY >= ?)))
     * [params=(String) dummy1, (int) 1, (String) dummy1, (long) 20150201]
     *
     */
    public void ttestCriteriaQuery() {
        EntityManager em = emf.createEntityManager();
        resetSQL();
        em.createQuery(critQuery).getResultList();
        assertNotSQL(".*T_DIM_DAY t3.*");
        assertSQL(".*T_DIM_DAY t1.*");
        em.close();
    }

    /**
     * If we execute just the string generated by Criteria Builder, we
     * do not see an extra alias.  We see:
     * SELECT t0.empl_cnt FROM T_FACT_WORK_ASGNMT t0, T_DIM_DAY t1
     * WHERE (t0.CLNT_OBJ_ID = ? AND t1.ROLL_13_MNTH_IND = ? AND
     *   t0.pers_obj_id IN (SELECT t2.pers_obj_id FROM
     *   T_FACT_WORK_ASGNMT t2 WHERE (t2.CLNT_OBJ_ID = ? AND
     *   t1.MNTH_STRT_DAY_KY = ?)))
     */
    public void ttestGeneratedCriteriaQueryString(){
        if (!"true".equals(doCB)){
        EntityManager em = emf.createEntityManager();
        System.out.println("NOT doing CB");
        resetSQL();
        em.createQuery(critQueryString).getResultList();
        assertNotSQL(".*T_DIM_DAY t3.*");
        assertSQL(".*T_DIM_DAY t1.*");
        em.close();
        }
    }

    public CriteriaQuery<Tuple> createCriteriaBuilder(){
            EntityManager em = emf.createEntityManager();

            List<Predicate> predicates = new ArrayList<>();
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<Tuple> cq = cb.createTupleQuery();
            Root<DimDay> day = cq.from(DimDay.class);

            Root<FactWorkAssignment> wa =  cq.from(FactWorkAssignment.class);

            predicates.add(cb.equal(wa.get(FactWorkAssignment_.orgOID), "dummy1"));
            predicates.add(cb.equal(day.get(DimDay_.roll13MonthInd), 1));

            Subquery<String> subQuery = cq.subquery(String.class);

            Root<FactWorkAssignment> wa1 = subQuery.from(FactWorkAssignment.class);

            subQuery.select(wa1.get(FactWorkAssignment_.personObjId));
            List<Predicate> subQueryPredicates = new ArrayList<>();
            subQueryPredicates.add(cb.equal(wa1.get(FactWorkAssignment_.orgOID), "dummy1"));

            //Removing this seem to "fix" the issue....I think the fact that we use 'day' from
            //the 'outer' query has an affect....is it OK to use 'day' from the outer query??   I'm
            //assuming so since 'testGeneratedCriteriaQueryString' generates the expected SQL.
//            subQueryPredicates.add(cb.greaterThanOrEqualTo(day.get(DimDay_.monthStrtDate), new Long(20150201L)));
            subQueryPredicates.add(cb.equal(day.get(DimDay_.monthStrtDate), 20150201L));

            //Doing this places the 'T_DIM_DAY t3' in the 'inner'/sub query.  Is this the proper solution??  Or just a
            //hacky work around?
            //Root<DimDay> day2 =  subQuery.from(DimDay.class);
            //subQueryPredicates.add(cb.greaterThanOrEqualTo(day2.get(DimDay_.monthStrtDate), new Long(20150201L)));

            subQuery.where(subQueryPredicates.toArray(new Predicate[] {}));

            Predicate predicate = wa.get(FactWorkAssignment_.personObjId).in(subQuery);

            predicates.add(predicate);

            List<Selection<?>> selections = new ArrayList<>();

            Expression<Integer> expHC = wa.get(FactWorkAssignment_.employeeCount);
            selections.add(expHC);

            cq.multiselect(selections).where(predicates.toArray(new Predicate[] {}));

            return cq;
        }
}