Skip to content
 

SQL strangeness

I’ve been caught several times on the same bug recently : some method takes a collection as argument, and must execute a SQL (actually, a Hibernate query) to find all foos having their ID in the given collection:

    public List<Foo> findByIds(Set<Long> ids) {
        String hql = "select foo from Foo foo where foo.id in (:ids) order by foo.bar";
        Query query = session.createQuery(hql);
        query.setParameterList("ids", ids);
        return query.list();
    }

This generates a SQL query looking like

    select f.ID, f.BAR from FOO f where f.ID in (?, ?, ?) order by f.BAR

Everything goes well, until the method is executed with an empty set as argument. The SQL query becomes invalid and a runtime exception is thrown. Could anyone tell me why the SQL standard doesn’t allow an IN without any parameter in the list? It’s not like the SQL engine couldn’t optimize the query and return nothing instantly.

By the way, Hibernate could also play a role here, and translate an no-parameter IN clause by WHERE 0 = 1. But no, you have to take care of this yourself, and rewrite the method as this:

    public List<Foo> findByIds(Set<Long> ids) {
        if (ids.isEmpty()) {
            return Collections.emptyList();
        }
        String hql = "select foo from Foo foo where foo.id in (:ids) order by foo.bar";
        Query query = session.createQuery(hql);
        query.setParameterList("ids", ids);
        return query.list();
    }

This is easy when the method is as simple as this one. But when the IN clause is embedded inside a subquery, things aren’t always as straightforward. Why an empty set is considered invalid in SQL is an mystery. But at least, SQL doesn’t force me to write

    select f.ID, f.BAR from FOO f where f.ID = ? order by f.BAR

each time the set contains only one element.

4 commentaires

  1. Adeel Ansari dit :

    Criteria API might take care of empty-collection well, I suppose.

  2. jFrenetic dit :

    Yeah, that’s weird.. Another thing that annoys me is that you cannot pass null value in predicate. You have to use this ugly clause: IS NOT NULL.

  3. mahsa dit :

    hello iam a university student i have some problems in java
    would you plz help me?
    i donot know how to create a priority queue with linked lists
    and
    i donot know how to use linked lists for sparse polynomials

    this is my email:sedmoradi92@yahoo.com

    pleaze help me i do need your help
    thanks so much

  4. JB dit :

    See http://www.stackoverflow.com for technical questions.