IN expression in a Criteria Query

Hello,

I am trying to migrate my Spring project from Hibernate to ObjectDB. On ObjectDB page, it is written that ObjectDB is 100% compatible with JPA. However, I have problem with this JPQL query:

SELECT e FROM Employee e WHERE e.present = false AND NOT EXISTS
(SELECT c FROM Computer c WHERE c.employeeEntity = e)
ORDER BY e.name

with Hibernate, there was no problem with this query, but ObjectDB throws exception:

com.objectdb.o.UserException: Unexpected query token 'EXISTS'

Second problem is with Criteria Language. This code:

In<Employee> inExpression = cb.in(root.get(Computer_.employeeEntity));
for (Long id : emplIds) {
    Employee e = new Employee(id);
    inExpression = inExpression.value(e);
}
list.add(inExpression);

was working with Hibernate, with ObjectDB it is throwing:

com.objectdb.o.UserException: Unexpected query token ':l2'

Does anybody have any idea how to solve these problems?

Milan

> On ObjectDB page, it is written that ObjectDB is 100% compatible with JPA.

Where did you see that statement? Actually there are several JPA features that are currently not supported by ObjectDB. They are listed in the issue tracking system, and unfortunately subqueries is one of them.

Regarding the criteria query, it is unclear where exactly the exception is thrown. It may be an ObjectDB issue or limitation, or a non standard JPA Hibernate extension. What happens if you replace Computer_.employeeEntity with "employeeEntity"? Currently you cannot generate Computer_ with ObjectDB, and maybe the class that Hibernate generates doesn't work with ObjectDB.

I couldn't find the statement any more, I probbably saw it on some unreliable page, sorry about that.

I was generating metamodel by myself. I replaced it with "employeeEntity", but the result is the same. Here is complete stack trace:

com.objectdb.o.UserException: Unexpected query token ':l2'
com.objectdb.o.MSG.d(MSG.java:61)
com.objectdb.o.TKN.J(TKN.java:765)
com.objectdb.o.QNF.B(QNF.java:894)
com.objectdb.o.QNF.I(QNF.java:1294)
com.objectdb.o.QNF.k(QNF.java:315)
com.objectdb.o.QNF.H(QNF.java:1270)
com.objectdb.o.QNF.k(QNF.java:210)
com.objectdb.o.QNF.t(QNF.java:611)
com.objectdb.o.QNF.t(QNF.java:605)
com.objectdb.o.QNF.k(QNF.java:218)
com.objectdb.o.QNF.j(QNF.java:135)
com.objectdb.o.QRC.z(QRC.java:321)
com.objectdb.o.QRC.v(QRC.java:212)
com.objectdb.o.QRC.u(QRC.java:166)
com.objectdb.o.QRM.U6(QRM.java:250)
com.objectdb.o.MST.U6(MST.java:933)
com.objectdb.o.WRA.U6(WRA.java:291)
com.objectdb.o.WSM.U6(WSM.java:113)
com.objectdb.o.STC.r(STC.java:449)
com.objectdb.o.SHN.aj(SHN.java:489)
com.objectdb.o.SHN.K(SHN.java:156)
com.objectdb.o.HND.run(HND.java:132)
java.lang.Thread.run(Unknown Source)

I also found another issue with this query:

SELECT a FROM Account a GROUP BY a.code HAVING COUNT(a) < 2

It throws following exception:

com.objectdb.o.UserException: Invalid result expression 'a' for an aggregate query
at com.objectdb.o.MSG.d(MSG.java:61)
at com.objectdb.o.QNF.l(QNF.java:338)
at com.objectdb.o.QNF.n(QNF.java:399)
at com.objectdb.o.QNF.k(QNF.java:170)
at com.objectdb.o.QRC.G(QRC.java:608)
at com.objectdb.o.QRC.F(QRC.java:580)
at com.objectdb.o.QRC.E(QRC.java:529)
at com.objectdb.o.QRC.v(QRC.java:214)
at com.objectdb.o.QRC.u(QRC.java:166)
at com.objectdb.o.QRM.U6(QRM.java:250)
at com.objectdb.o.MST.U6(MST.java:933)
at com.objectdb.o.WRA.U6(WRA.java:291)
at com.objectdb.o.WSM.U6(WSM.java:113)
at com.objectdb.o.STC.r(STC.java:449)
at com.objectdb.o.SHN.aj(SHN.java:489)
at com.objectdb.o.SHN.K(SHN.java:156)
at com.objectdb.o.HND.run(HND.java:132)
at java.lang.Thread.run(Unknown Source)

Regarding the criteria query issue, please provide the complete query string (you should be able to see it in the log, if you enable logging as explained in this forum thread, or by converting the criteria query into a string with toString).

Regarding the GROUP BY query:

SELECT a FROM Account a GROUP BY a.code HAVING COUNT(a) < 2

This is an invalid query. In a grouping query you can only select expressions of the groups, as stated in the manual:

The SELECT clause can only use properties of the groups, which include:

• The properties that are used for grouping (each group has unique value combination).
• Aggregate calculations (count, sum, avg, max, min) that are carried out on all the objects (or the object tuples) in the group.

Bellow is the XML for criteria query from logs:

<token name="QUERY" code="67" postion="0:0">
    <token name="SELECT" code="132" postion="0:6">
        <token name="DISTINCT" code="94" postion="7:15" />
        <token name="COUNT" code="88" postion="16:21">
            <token name="(" code="14" postion="21:22">
                <token name="$1" code="1" postion="22:24" />
                <token name=")" code="15" postion="24:25" />
            </token>
        </token>
    </token>
    <token name="FROM" code="103" postion="26:30">
        <token name="$1" code="1" postion="46:48">
            <token name="Computer" code="1" postion="31:45" />
        </token>
    </token>
    <token name="WHERE" code="150" postion="49:54">
        <token name="IN" code="106" postion="99:101">
            <token name="." code="6" postion="83:84">
                <token name="$1" code="1" postion="81:83" />
                <token name="employeeEntity" code="1" postion="84:98" />
            </token>
            <token name="(" code="14" postion="102:103">
                <token name=":" code="7" postion="103:104">
                    <token name="l2" code="1" postion="104:106" />
                </token>
                <token name=":" code="7" postion="107:108">
                    <token name="l3" code="1" postion="108:110" />
                </token>
                <token name=":" code="7" postion="111:112">
                    <token name="l4" code="1" postion="112:114" />
                </token>
                <token name=")" code="15" postion="114:115" />
            </token>
        </token>
    </token>
</token>

I just found out intereting thing. This query:

SELECT a FROM Account a WHERE a.code IN (:codes) ORDER BY a.code

throws exception Unexpected query token ':l2'. When I remove the brackets:

SELECT a FROM Account a WHERE a.code IN :codes ORDER BY a.code

it works.

I think it should be valid with brackets, Hibernate has no problem with it. Can it be somehow connected to the problem with IN in criteria query above?

Good catch. Both forms (with and without parenthesis) should be supported, and apparently this is a regression in version 2.5.0, as a result of adding support of IN (literal-list) expressions.

Please try build 2.5.0_02 that should fix it, and check whether or not the criteria query problem is solved.

I have just tryed 2.5.0_02, but the problem with criteria query is still there. Log shows that this query:

<queryRequest query="SELECT DISTINCT COUNT($1) FROM Computer $1 WHERE (($1.workplace.id=:l1) AND $1.employeeEntity.id IN (:l2,:l3,:l4))" args="{l1->5,l2->37,l3->5,l4->38}" transactionId="-1" />

throws this exception:

com.objectdb.o.UserException: Unexpected query token ':l3'
at com.objectdb.o.MSG.d(MSG.java:61)
at com.objectdb.o.TKN.J(TKN.java:765)
at com.objectdb.o.QNF.B(QNF.java:904)
at com.objectdb.o.QNF.I(QNF.java:1295)
at com.objectdb.o.QNF.k(QNF.java:315)
at com.objectdb.o.QNF.H(QNF.java:1271)
at com.objectdb.o.QNF.k(QNF.java:210)
at com.objectdb.o.QNF.t(QNF.java:611)
at com.objectdb.o.QNF.t(QNF.java:605)
at com.objectdb.o.QNF.k(QNF.java:218)
at com.objectdb.o.QNF.j(QNF.java:135)
at com.objectdb.o.QRC.z(QRC.java:321)
at com.objectdb.o.QRC.v(QRC.java:212)
at com.objectdb.o.QRC.u(QRC.java:166)
at com.objectdb.o.QRM.U6(QRM.java:250)
at com.objectdb.o.MST.U6(MST.java:933)
at com.objectdb.o.WRA.U6(WRA.java:293)
at com.objectdb.o.WSM.U6(WSM.java:113)
at com.objectdb.o.STC.r(STC.java:450)
at com.objectdb.o.SHN.aj(SHN.java:489)
at com.objectdb.o.SHN.K(SHN.java:156)
at com.objectdb.o.HND.run(HND.java:132)
at java.lang.Thread.run(Unknown Source)

OK. Apparently the problem is clear now, and we are working on a quick fix.

Please try build 2.5.0_04, which seems to fix the criteria query issue.

Hi,

I am seeing the same issue of "IN (:bindVar)" generates a syntax error in version 2.5.3, but "IN :bindVar"  - without the parenthesis works ok. Looks like a regression ... ?

Thanks

A test that was written for the original report, still works with 2.5.3:

package test;

import java.util.*;

import javax.persistence.*;
import javax.persistence.criteria.*;


@Entity
public class T1108 {
   
    public static void main(String[] args) {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory(
                "objectdb:$objectdb/db/test.tmp;drop"
            );
       
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        em.persist(new MyEntity("a"));
        em.persist(new MyEntity("b"));
        em.persist(new MyEntity("c"));
        em.getTransaction().commit();
        em.close();

        em = emf.createEntityManager();
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<MyEntity> q = cb.createQuery(MyEntity.class);
        Root<MyEntity> root = q.from(MyEntity.class);
        Path<String> value = root.get("str");
        Predicate predicate = value.in("a", "c", "e");
        q.where(predicate);
        q.select(root);
        TypedQuery<MyEntity> query = em.createQuery(q);
        List<MyEntity> results = query.getResultList();
        System.out.println(results);
        em.close();
       
        emf.close();
    }
   
    @Entity
    public static class MyEntity {
        String str;
        public MyEntity() {}
        public MyEntity(String str) { this.str = str; }
        @Override
        public String toString() { return str; }
    }
}

Please demonstrate the issue that you have with a test case, or change this example to show it.

Hi,

The test case you have posted uses the CriteriaBuilder API. The problem I was getting, was a syntax error when the "IN (:bindVar)" was used within a JPQL query string, passed to EntityManager.createQuery() in version 2.5.3.

Thanks

The original post was on criteria queries, but it is the same with JPQL, since criteria queries are converted into JPQL query strings. Anyway, here is a revised test with a JPQL query string, that still works:

package test;

import java.util.*;

import javax.persistence.*;
import javax.persistence.criteria.*;


@Entity
public class T1108 {
   
    public static void main(String[] args) {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory(
                "objectdb:$objectdb/db/test.tmp;drop"
            );
       
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        em.persist(new MyEntity("a"));
        em.persist(new MyEntity("b"));
        em.persist(new MyEntity("c"));
        em.getTransaction().commit();
        em.close();

        em = emf.createEntityManager();
        Query query =
            em.createQuery("SELECT e FROM MyEntity e WHERE e.str IN (:bindVar)");
        query.setParameter("bindVar", "a");
        List<MyEntity> results = query.getResultList();
        System.out.println(results);
        em.close();
       
        emf.close();
    }
   
    @Entity
    public static class MyEntity {
        String str;
        public MyEntity() {}
        public MyEntity(String str) { this.str = str; }
        @Override
        public String toString() { return str; }
    }
}