[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2776) Restrictons.in(..) generates invalid SQL if list of values is empty

[Adrian Smith (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2776?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=32253#action_32253 ] 

Adrian Smith commented on HHH-2776:
-----------------------------------

I would not be OK with a solution that would only work on MySQL. The IN(NULL) thing was suggested by you, not me. I suggested using "where 1=2" which would work on all databases.

"A user reporting that something they are trying that is not documented to work does not work" - The only thing that is not documented is that if you pass in an empty list it doesn't work. Passing in a non-empty list returns those items which match entries in the list: passing in an empty list should do the same. If not (arbitrary limitation) this should be documented, as you say.

Restrictions.in documentation (this is its entirety!)
  "Apply an "in" constraint to the named property"

This does not mention special cases with the empty list, therefore I would assume it would do the same as with a non-empty list.

No - this is a bug - a) generates invalid SQL (should never happen), and b) even if an exception were thrown, that would still be a bug, as it's not consistent with the documentation, and also doesn't make sense.

> Restrictons.in(..) generates invalid SQL if list of values is empty
> -------------------------------------------------------------------
>
>                 Key: HHH-2776
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2776
>             Project: Hibernate Core
>          Issue Type: New Feature
>          Components: query-criteria, query-hql
>    Affects Versions: 3.2.2
>         Environment: Hibernate 3.2.2.ga, MySQL 4.1.18, Windows XP
>            Reporter: Adrian Smith
>            Assignee: Steve Ebersole
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira