openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/TestSubquery.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.query;

import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.apache.openjpa.jdbc.conf.JDBCConfiguration;
import org.apache.openjpa.jdbc.sql.DBDictionary;
import org.apache.openjpa.jdbc.sql.MariaDBDictionary;
import org.apache.openjpa.jdbc.sql.MySQLDictionary;
import org.apache.openjpa.persistence.query.Customer.CreditRating;
import org.apache.openjpa.persistence.test.SingleEMFTestCase;

/**
 * Test JPQL subquery
 */
public class TestSubquery
    extends SingleEMFTestCase {

    @Override
    public void setUp() {
        setUp(Customer.class, Customer.CustomerKey.class, Order.class,
            OrderItem.class, Magazine.class, Publisher.class, Employee.class,
            Dependent.class, DependentId.class, Account.class, DROP_TABLES);
    }

    static String[]  querys = new String[] {
        "select c from Customer c where EXISTS" +
            " (select o from  in(c.orders)  o)" ,
        "select c from Customer c where EXISTS" +
            " (select o from c.orders o)" ,
        "select c from Customer c where NOT EXISTS" +
            " (select o from in (c.orders) o)" ,
        "select c from Customer c where NOT EXISTS" +
            " (select o from  c.orders o)" ,
        "select o1.oid from Order o1 where o1.oid in " +
            " (select distinct o.oid from OrderItem i, Order o" +
            " where i.quantity > 10 and o.amount > 1000 and i.lid = o.oid)" ,
        "select o.oid from Order o where o.customer.name =" +
            " (select max(o2.customer.name) from Order o2" +
            " where o.customer.cid.id = o2.customer.cid.id)",
        "select o from Order o where o.customer.name =" +
            " (select max(o2.customer.name) from Order o2" +
            " where o.customer.cid.id = o2.customer.cid.id)",
        "select o.oid from Order o where o.amount >" +
            " (select count(i) from o.lineitems i)",
        "select o.oid from Order o where o.amount >" +
            " (select count(o.amount) from Order o)",
        "select o.oid from Order o where o.amount >" +
            " (select count(o.oid) from Order o)",
        "select o.oid from Order o where o.amount >" +
            " (select avg(o.amount) from Order o)",
        "select c.name from Customer c where exists" +
            " (select o from c.orders o where o.oid = 1) or exists" +
            " (select o from c.orders o where o.oid = 2)",
        "select c.name from Customer c, in(c.orders) o where o.amount " +
            "between" +
            " (select max(o.amount) from Order o) and" +
            " (select avg(o.amount) from Order o) ",
        "select o.oid from Order o where o.amount >" +
            " (select sum(o2.amount) from Customer c, in(c.orders) o2) ",
        "select o.oid from Order o where o.amount between" +
            " (select avg(o2.amount) from Customer c, in(c.orders) o2)" +
            " and (select min(o2.amount) from Customer c, in(c.orders) o2)",
        "select o.oid from Customer c, in(c.orders)o where o.amount >" +
            " (select sum(o2.amount) from c.orders o2)",
        "select o1.oid, c.name from Order o1, Customer c where o1.amount = " +
            " any(select o2.amount from in(c.orders) o2)",
        "SELECT p, m "+
            "FROM Publisher p "+
            "LEFT OUTER JOIN p.magazineCollection m "+
            "WHERE m.id = (SELECT MAX(m2.id) "+
            "FROM Magazine m2 "+
            "WHERE m2.idPublisher.id = p.id "+
            "AND m2.datePublished = "+
            "(SELECT MAX(m3.datePublished) "+
            "FROM Magazine m3 "+
            "WHERE m3.idPublisher.id = p.id)) ",
        "select o from Order o where o.amount > " +
            " (select count(o) from Order o)",
        "select o from Order o where o.amount > " +
            "(select count(o2) from Order o2)",
        "select c from Customer c left join c.orders o where not exists"
         + " (select o2 from c.orders o2 where o2 = o)",
    };

    static String[]  querys_jpa20 = new String[] {
        "select o.oid from Order o where o.delivered =" +
            " (select " +
            "   CASE WHEN o2.amount > 10 THEN true" +
            "     WHEN o2.amount = 10 THEN false " +
            "     ELSE false " +
            "     END " +
            " from Order o2" +
            " where o.customer.cid.id = o2.customer.cid.id)",

        "select o1.oid from Order o1 where o1.amount > " +
            " (select o.amount*0.8 from OrderItem i, Order o" +
            " where i.quantity > 10 and o.amount > 1000 and i.lid = o.oid)",

        "select o.oid from Order o where o.customer.name =" +
            " (select substring(o2.customer.name, 3) from Order o2" +
            " where o.customer.cid.id = o2.customer.cid.id)",

        "select o.oid from Order o where o.orderTs >" +
            " (select CURRENT_TIMESTAMP from o.lineitems i)",

        "select o.oid from Order o where o.amount >" +
            " (select SQRT(o.amount) from Order o where o.delivered = true)",

        "select o.oid from Order o where o.customer.name in" +
            " (select CONCAT(o.customer.name, 'XX') from Order o" +
            " where o.amount > 10)",

        "select c from Customer c where c.creditRating =" +
            " (select " +
            "   CASE WHEN o2.amount > 10 THEN " +
            "org.apache.openjpa.persistence.query.Customer$CreditRating.POOR" +
            "     WHEN o2.amount = 10 THEN " +
            "org.apache.openjpa.persistence.query.Customer$CreditRating." +
            "GOOD " +
            "     ELSE " +
            "org.apache.openjpa.persistence.query." +
            "Customer$CreditRating.EXCELLENT " +
            "     END " +
            " from Order o2" +
            " where c.cid.id = o2.customer.cid.id)",

        "select c from Customer c " +
            "where c.creditRating = (select COALESCE (c1.creditRating, " +
            "org.apache.openjpa.persistence.query." +
            "Customer$CreditRating.POOR) " +
            "from Customer c1 where c1.name = 'Famzy') order by c.name DESC",

        "select c from Customer c " +
            "where c.creditRating = (select NULLIF (c1.creditRating, " +
            "org.apache.openjpa.persistence.query." +
            "Customer$CreditRating.POOR) " +
            "from Customer c1 where c1.name = 'Famzy') order by c.name DESC",
    };

    static String[] updates = new String[] {
        "update Order o set o.amount = 1000 where o.customer.name = " +
            " (select max(o2.customer.name) from Order o2 " +
            " where o.customer.cid.id = o2.customer.cid.id)",
    };

    static String[]  querys2 = new String[] {
            // 0
        "select o1.oid, c.name from Order o1, Customer c" +
            " where o1.customer.name = " +
            " any(select o2.customer.name from in(c.orders) o2)",
            // 1
        "select o1.oid, c.name from Order o1, Customer c" +
            " where o1.amount = " +
            " any(select o2.amount from in(c.orders) o2)",
            // 2
        "select DISTINCT c.name FROM Customer c JOIN c.orders o " +
            "WHERE EXISTS (SELECT o FROM o.lineitems l where l.quantity > 2 ) ",
            // 3
        "select DISTINCT c.name FROM Customer c, IN(c.orders) co " +
            "WHERE co.amount > ALL " +
            "(Select o.amount FROM Order o, in(o.lineitems) l WHERE l.quantity > 2)",
            // 4
        "select distinct c.name FROM Customer C, IN(C.orders) co " +
            "WHERE co.amount < ALL " +
            "(Select o.amount FROM Order o, IN(o.lineitems) l WHERE l.quantity > 2)",
            //5
        "select c.name FROM Customer c, IN(c.orders) co " +
            "WHERE co.amount <= ALL " +
            "(Select o.amount FROM Order o, IN(o.lineitems) l WHERE l.quantity > 2)",
            // 6
        "select DISTINCT c.name FROM Customer c, IN(c.orders) co " +
            "WHERE co.amount > ANY " +
            "(Select o.amount FROM Order o, IN(o.lineitems) l WHERE l.quantity = 2)",
            // 7
        "select DISTINCT c.name FROM Customer c " +
            "WHERE EXISTS (SELECT o FROM c.orders o where o.amount " +
            "BETWEEN 1000 AND 1200)",
            // 8
        "select DISTINCT c.name FROM Customer c " +
            "WHERE EXISTS (SELECT o FROM c.orders o where o.amount > 1000 )",
            // 9
        "SELECT o.oid from Order o WHERE " +
            "EXISTS (SELECT c.name From o.customer c WHERE c.name LIKE '%los') ",
            // 10
        "select Distinct c.name FROM Customer c, IN(c.orders) co " +
            "WHERE co.amount >= SOME" +
            "(Select o.amount FROM Order o, IN(o.lineitems) l WHERE l.quantity = 2)",
            // 11
        "select c FROM Customer c WHERE EXISTS" +
            " (SELECT o FROM c.orders o where o.amount > 1000)",
            // 12
        "select c FROM Customer c WHERE EXISTS" +
            " (SELECT o FROM c.orders o)",
            // 13
        "SELECT c FROM Customer c WHERE "
            + "(SELECT COUNT(o) FROM c.orders o) > 10",
            // 14
        "SELECT o FROM Order o JOIN o.customer c WHERE c.name = "
            + "SOME (SELECT a.name FROM c.accounts a)",

        };

    public void testSubquery2() {
        EntityManager em = emf.createEntityManager();
        for (int i = 0; i < querys2.length; i++) {
            String q = querys2[i];
            System.err.println(">>> JPQL JPA2 :[ " + i + "]" +q);
            List rs = em.createQuery(q).getResultList();
            assertEquals(0, rs.size());
        }
        em.close();
    }


    public void testSubquery() {
        JDBCConfiguration conf = (JDBCConfiguration) emf.getConfiguration();
        DBDictionary dict = conf.getDBDictionaryInstance();

        EntityManager em = emf.createEntityManager();
        for (int i = 0; i < querys_jpa20.length; i++) {
            String q = querys_jpa20[i];
            System.err.println(">>> JPQL JPA2 :[ " + i + "]" +q);
            List rs = em.createQuery(q).getResultList();
            assertEquals(0, rs.size());
        }
        for (int i = 0; i < querys.length; i++) {
            String q = querys[i];
            System.err.println(">>> JPQL: [ " + i + "]"+q);
            List rs = em.createQuery(q).getResultList();
            assertEquals(0, rs.size());
        }

        // MySQL throws exception for the jpql in the updates:
        // "You can't specify target table 'xxx' for update in FROM clause". The MySQL manual mentions
        // this at the bottom of the UPDATE documentation(http://dev.mysql.com/doc/refman/5.0/en/update.html):
        // Currently, you cannot update a table and select from the same table in a subquery.

        if (dict instanceof MySQLDictionary || dict instanceof MariaDBDictionary)
            return;

        em.getTransaction().begin();
        for (String update : updates) {
            int updateCount = em.createQuery(update).executeUpdate();
            assertEquals(0, updateCount);
        }

        em.getTransaction().rollback();
        em.close();
    }

    /**
     * Verify a sub query can contain MAX and additional date comparisons
     * without losing the correct alias information. This sort of query
     * originally caused problems for DBDictionaries which used DATABASE
     * syntax.
     */
    public void testSubSelectMaxDateRange() {
        String query =
            "SELECT e,d from Employee e, Dependent d "
                + "WHERE e.empId = :empid "
                + "AND d.id.empid = (SELECT MAX (e2.empId) FROM Employee e2) "
                + "AND d.id.effDate > :minDate "
                + "AND d.id.effDate < :maxDate ";
        EntityManager em = emf.createEntityManager();
        Query q = em.createQuery(query);
        q.setParameter("empid", (long) 101);
        q.setParameter("minDate", new Date(100));
        q.setParameter("maxDate", new Date(100000));
        q.getResultList();
        em.close();
    }

    public void testUpdateWithCorrelatedSubquery() {
        String update = "update Customer c set c.creditRating = ?1 where EXISTS" +
           " (select o from  in(c.orders)  o)";
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        CreditRating creditRating = CreditRating.GOOD;
        int updateCount = em.createQuery(update).
            setParameter(1, creditRating).executeUpdate();
        em.getTransaction().rollback();
        em.close();
    }
}