[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=32251#action_32251 ] 

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

I agree with your solution 2).

I'm not sure if it's a feature request, but it's certainly

a) a change request (presumably the assumption is currently, as you state, that the application program will look after this) and 

b) a bug report (as currently invalid SQL is generated, "correct" behavior, if the desired behavior was that the application would look after this, would be to throw an exception, or something.)

But I don't agree that the functionality would really be debatable. I mean imagine if selecting from a table which was empty resulted in an error, or opening an empty text file would be an error from the text editor. I mean having functionality based on the size of something (e.g. if size==0, do X; if size==1 do Y, else do Z) is surely pretty weird: you want functionality which works for any size of the list. I mean if it did something completely weird when the list had 27 elements that would be a bug, right? So why is it not a bug if it does something weird when the list has 0 elements?

True - SQL syntax may not allow it - but Hibernate is an API not just a SQL-generating platform - I don't think one should define ones APIs based on the exact implementation details / bugs of the underlying platform.

For example Oracle doesn't allow more than 1000 items in an IN. Should that also be exposed in the API?

I would be more than happy to fix this myself.

> 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