notIn, empty collection procudes SQLGrammarException

[Jonas]

I had this code where I retrieved users in my system. I'm allowing a collection of userId's (long) to be passed to exclude specific users. Previously I used a null value in the collection to indicate "skip none" but the code has changed to an empty collection instead where I started getting this sql exception (stack trace below).

Here's the code:

public List<User> getUsers(final EntityManager em, final boolean includeAdminUser, final Collection<Long> excludedUsers)
{
    final JPQLQuery query = queryFrom(em, qUser);

    // Exclude admin user if set.
    if (!includeAdminUser)
    {
        query.where(qUser.email.ne(ADMIN_EMAIL));
    }

    // Exclude specific users if set.
    if (excludedUsers != null)
    {
        query.where(qUser.userId.notIn(excludedUsers));
    }

    query.orderBy(qUser.userId.asc());

    return query.list(qUser);
}

The resulting SQL:

Hibernate: select user0_.userid as <... snip ...> from "User" user0_ where user0_.userid not in  () order by user0_.userid asc

Now if I also check if !excludedUsers.isEmpty() then it plays along. Is it correct to always check for not null or not empty or shouldn't notIn handle empty collections?

I'm running Querydsl 3.1.1, JPA & Hibernate.

Stack trace:

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:61)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2031)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1832)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
    at org.hibernate.loader.Loader.doQuery(Loader.java:899)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doList(Loader.java:2516)
    at org.hibernate.loader.Loader.doList(Loader.java:2502)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332)
    at org.hibernate.loader.Loader.list(Loader.java:2327)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:490)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1268)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:264)
    ... 70 more
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
  Position: 558
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56)

[Timo Westkämper]

Hi.

It's valid on the JPQL level, so I guess you need to check this on your DAO level.

Br,
Timo

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[Jonas]

Ok, I expected as much. Thanks.