.contains on empty list throws exception (+ query with constant "false" not working)

[BjarkeNL]

Hi

I may have found a bug in Jinq (tried versions 1.8.13 and 1.8.19) - or perhaps it is in Hibernate? (I'm using Spring and a JinqSource class as described at http://www.jinq.org/docs/spring.html. The database is MySQL.)

When calling .contains on a list in a lambda-expression, it throws org.hibernate.exception.SQLGrammarException, if the list is empty. It works fine if the list is not empty.

Example to show the problem:

    // This list is always empty in this example - for a real-world situation, imagine that it would sometimes be empty and other times it would be a few elements long.
    List<Integer> ids = new ArrayList<>();

    // Run the query
    List<MyEntity> results = jinq.myEntity()
        .where(me -> ids.contains(me.getId()))
        .toList();

This produces: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet which is caused by com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') limit 10001' at line 1

The reason for the error is that the generated SQL is on this form: (Notice the empty content of "IN ()").

    select myentity0_.id as id1_11_, ...lots of other fields... 
    from myentity myentity0_ 
    where myentity0_.id in () 
    limit ?

Expected result: If the ids-list is empty the where-clause would always evaluate to false, like .contains normally does. (Effectively the same as .where(me -> false) (...but see below)).

(I understand that in SQL and JPQL you can't execute IN of an empty sequence, but with List's contains-method in Java you can, so it would be nice if that special-case was handled)

Update:

When i tried working around it, by building an alternative query if the list was empty, i discovered that .where(me -> false) doesn't seem to work either. I would expect such query to always return an empty result, but it returns all elements instead. (all rows from the table) (.where(me -> true) also returns all rows - i.e. constant true and constant false behaves the same) (boolean b = false and then .where(me -> b) throws an exception: "antlr.NoViableAltException: unexpected AST node") (I managed to work around it in another way, so all is good for now)

If you can't reproduce this, i'll be more than happy to provide more information, of course :) Finally: thank you for JINQ! It is truely awesome!

[my2iu]

It seems like more of a Hibernate issue (or even a MySQL issue). My testing shows that Jinq is generating a correct query, but Hibernate can't handle empty lists (or perhaps that a MySQL problem). It's theoretically possible to put in a workaround in Jinq for this, but it's actually a lot of work because it means that Jinq can't simply cache queries and substitute parameters in. Depending on the values of the parameters, it will have to rerun its analysis and rebuild the query.

Seeing as Hibernate and MySQL have more developer resources, and given that the problem is easy to work around (if your list is empty, then issue a different query), it's probably better to ask them to fix it or to just workaround the problem yourself.

The .where(me -> false) seems to be a bug. I will hunt it down and fix it.