[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2776) Restrictons.in(..) generates invalid SQL if list of values is empty
Steve Ebersole (JIRA)
noreply at atlassian.com
Mon Feb 2 15:44:38 EST 2009
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2776?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=32252#action_32252 ]
Steve Ebersole commented on HHH-2776:
-------------------------------------
Well neither #1 nor #2 were solutions, they were merely points...
A user reporting that something they are trying that is not documented to work does not work is never ever ever going to be accepted as a bug.
So you are ok with "creating an API-instead-of-SQL-generating-platform" that happens to only work on MySQL because it uses some MySQL extension beyond what standard SQL allows? LOL, comon.
I never said this should not be implemented (perhaps this is why I marked that we should implement this in HQL over a year ago???), but clearly implementing this based on a non-standard SQL syntax that happens to work on your selected database is a bit non-community-friendly wouldnt you say?
Sure feel free to implement it. You just need to finish up the research I started and find out which SQL spec added support for NULLs as explicit in-value-list items and then how many common databases support that spec.
> 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
More information about the hibernate-issues
mailing list