[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=32249#action_32249 ] 

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

I've forgotten the particular situation that made me write this bug, but imagine you have a list of products and the user must select 0 or more products. Then you want to see all purchases for these products. If you select 2 products, you see all the transactions for 2 products. If you select 1 product, you see all the transactions for the 1 product. If you select 0 products, there aren't any transactions matching your list of products.

Independent of the SQL standard, the query "give me all the transactions involving the following list of products" when the list of products is empty should return 0 transactions.

Anything else would be like saying that aMap.get(key) in Java should throw an exception or otherwise not work if the map happens to be empty. Lists and maps that are empty are no different from lists and maps that are not empty, only there are fewer elements.

> 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