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

[Bernhard Streit (JIRA)]

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

Bernhard Streit commented on HHH-2776:
--------------------------------------

Hibernate creates "select * ... where xxx in ()" in case the list is empty, which in my opinion is correct.

But mySQL only works if you write "where xxx in (NULL)". Doesn't make sense at all to me, since it obviously prevents one from having NULL as a valid element in the list (like "return all items where xxx is null").

So a workaround would be to return "in (NULL)" for empty lists. That would spare us another, unnecessary check if the list is empty.

> 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: Bug
>          Components: query-criteria
>    Affects Versions: 3.2.2
>         Environment: Hibernate 3.2.2.ga, MySQL 4.1.18, Windows XP
>            Reporter: Adrian Smith
>
> 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