0

Recently, while writing some Spring/JPA code, I stumbled on an issue I tend to face with from time to time: I passed an empty collection to a repository method that generates an IN expression for the WHERE clause causing an SQL query syntax exception (WHERE column IN ()). To work around this issue I just return an empty collection in a DAO object, not letting the syntactially illegal SQL code to execute (if it's justified for a particular case). According to this reference, empty () are illegal as the IN right operand syntax is as follows:

<in predicate value>    ::=   <table subquery> | <left paren> <in value list> <right paren>

<in value list>    ::=   <value expression> { <comma> <value expression> } ...

requiring the value list to have at least one value.

I'm just wondering: would SQL database engines just return empty result sets and would there be backwards compatibility broken if the SQL grammar would allow empty value list for the IN operation?

4
  • Are you are asking what happens if, instead of an explicit list in the in clause, you have a selection returns no results?
    – JimmyJames
    Jul 22, 2016 at 16:03
  • @JimmyJames Not really, I mean what would happen if SQL could allow empty lists in the IN () predicate, and what would such a syntax grammar change affect. Not allowing an empty value list looks somewhat strange to me. Jul 22, 2016 at 16:08
  • 1
    It would mean that your query would return a legitimate result instead of causing an error. Since programs are currently designed to avoid this error, I doubt that your hypothetical change to SQL behavior would have any affect on current programs, unless it covers up an undiscovered bug. Going forward, new programs would no longer have to deliberately avoid the error, so returning to the original SQL behavior would be a breaking change. Jul 22, 2016 at 16:11
  • @RobertHarvey thank you for the comment. I was thinking of these scenarios as well, and I couldn't imagine a scenario that would break the existing code base (assuming no returning back to mandatory IN(val,...) syntax anymore). It's now also interesting why was SQL designed this way. Jul 22, 2016 at 16:19

3 Answers 3

6

It somewhat unlikely but possible that someone has built code that looks for this specific error and handles it in some special way that is different from what it would do if there were no results. Maybe they might return an error to the user. Such an approach is bad-practice IMO but I've seen (much) worse.

I think that it's a mistake that this syntax fails given that a nested select that returns no results inside an in clause doesn't cause an error. That is, it's a parsing issue, not a semantic one.

1

I had a similar issue where I'm using utility functions to generate the right-hand-side of column IN (...) and column NOT IN (...) for any number of values including 0.

IN () can be achieved with IN (null) as this is always false, even for null on the left hand side.

NOT IN () can be achieved with NOT IN (SELECT null WHERE 1=0) which is always true, even for null on the left hand side.

0

This is a strange question.

The reason why WHERE field_name IN () is disallowed (if indeed it is - I've never written such code) is because it more suggests an error in coding than a legitimate intention.

The same as if you wrote WHERE field_name = ;, with the semicolon terminating the statement and there being no field or constant specified on the right hand side of the comparison (which is a syntax error in any language I know).

If there was a desire to make the condition tautologically false and thus return an empty table, you could just write WHERE 1=0.

Obviously, in terms of generated SQL code, it may simplify things slightly to be able to specify zero or more elements in the IN list, but it also wouldn't be so hard to simply generate different code in the zero-length case. For example, to substitute WHERE field_name IN (NULL) in cases where there are no (non-null) values, which I believe is valid syntax.

The behaviour of the IN operator is such that comparison of any value and NULL is always treated as FALSE (even where both values being compared are NULL), so this achieves the apparent purpose.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.