I have classes called A and B. There is a 1:n relationship from A to B.

Class B has the following in its mapping file:

<many-to-one name="a" class="A" column="fk_a_id" not-null="true" />

If I write the following code:

List<B> foo() {
List<A> aList = ....
Session session = ....
Criteria query = session.createCriteria(B.class);
query.add(Restrictions.in("a", aList));
return query.list();
}

Then all works fine and the results I want are returned. However, if aList is empty (i.e. aList.size()==0) then the query.list function generates and executes invalid SQL for MySQL.

java.sql.SQLException: Syntax error or access violation message from server: "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 ')' at line 1"

I imagine that it is generating SQL like IN () which isn't valid.

I realize it's a bit ridiculous to want to find the rows where a field is IN the empty list. Nothing will ever be returned. However, in my opinion it should still be allowed, and return no rows, just as saying "WHERE 1=2" is allowed, just doesn't return any rows.