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?

  • 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 '16 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. – Lyubomyr Shaydariv Jul 22 '16 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. – Robert Harvey Jul 22 '16 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. – Lyubomyr Shaydariv Jul 22 '16 at 16:19

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.

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.